Line data Source code
1 : /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 : /*
3 : * This file is part of the LibreOffice project.
4 : *
5 : * This Source Code Form is subject to the terms of the Mozilla Public
6 : * License, v. 2.0. If a copy of the MPL was not distributed with this
7 : * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8 : *
9 : */
10 :
11 : #include <sfx2/dispatch.hxx>
12 : #include <svl/zforlist.hxx>
13 : #include <svl/undo.hxx>
14 :
15 : #include "formulacell.hxx"
16 : #include "rangelst.hxx"
17 : #include "scitems.hxx"
18 : #include "docsh.hxx"
19 : #include "document.hxx"
20 : #include "uiitems.hxx"
21 : #include "reffact.hxx"
22 : #include "strload.hxx"
23 : #include "docfunc.hxx"
24 : #include "StatisticsDialogs.hrc"
25 : #include "TableFillingAndNavigationTools.hxx"
26 :
27 : #include "AnalysisOfVarianceDialog.hxx"
28 :
29 : namespace
30 : {
31 :
32 : struct StatisticCalculation {
33 : sal_Int16 aLabelId;
34 : const char* aFormula;
35 : const char* aResultRangeName;
36 : };
37 :
38 : static StatisticCalculation lclBasicStatistics[] =
39 : {
40 : { STR_ANOVA_LABEL_GROUPS, NULL, NULL },
41 : { STRID_CALC_COUNT, "=COUNT(%RANGE%)", "COUNT_RANGE" },
42 : { STRID_CALC_SUM, "=SUM(%RANGE%)", "SUM_RANGE" },
43 : { STRID_CALC_MEAN, "=AVERAGE(%RANGE%)", "MEAN_RANGE" },
44 : { STRID_CALC_VARIANCE, "=VAR(%RANGE%)", "VAR_RANGE" },
45 : { 0, NULL, NULL }
46 : };
47 :
48 : static sal_Int16 lclAnovaLabels[] =
49 : {
50 : STR_ANOVA_LABEL_SOURCE_OF_VARIATION,
51 : STR_ANOVA_LABEL_SS,
52 : STR_ANOVA_LABEL_DF,
53 : STR_ANOVA_LABEL_MS,
54 : STR_ANOVA_LABEL_F,
55 : STR_ANOVA_LABEL_P_VALUE,
56 : STR_ANOVA_LABEL_F_CRITICAL,
57 : 0
58 : };
59 :
60 76 : static const OUString strWildcardRange("%RANGE%");
61 76 : static const OUString strWildcardNumber("%NUMBER%");
62 :
63 0 : OUString lclCreateMultiParameterFormula(
64 : ScRangeList& aRangeList, const OUString& aFormulaTemplate,
65 : const OUString& aWildcard, ScDocument* pDocument,
66 : ScAddress::Details& aAddressDetails)
67 : {
68 0 : OUString aResult;
69 0 : for (size_t i = 0; i < aRangeList.size(); i++)
70 : {
71 0 : OUString aRangeString(aRangeList[i]->Format(SCR_ABS, pDocument, aAddressDetails));
72 0 : OUString aFormulaString = aFormulaTemplate.replaceAll(aWildcard, aRangeString);
73 0 : aResult += aFormulaString;
74 0 : if(i != aRangeList.size() - 1) // Not Last
75 0 : aResult+= ";";
76 0 : }
77 0 : return aResult;
78 : }
79 :
80 0 : void lclMakeSubRangesList(ScRangeList& rRangeList, ScRange& rInputRange, ScStatisticsInputOutputDialog::GroupedBy aGroupedBy)
81 : {
82 0 : boost::scoped_ptr<DataRangeIterator> pIterator;
83 0 : if (aGroupedBy == ScStatisticsInputOutputDialog::BY_COLUMN)
84 0 : pIterator.reset(new DataRangeByColumnIterator(rInputRange));
85 : else
86 0 : pIterator.reset(new DataRangeByRowIterator(rInputRange));
87 :
88 0 : for( ; pIterator->hasNext(); pIterator->next() )
89 : {
90 0 : ScRange aRange = pIterator->get();
91 0 : rRangeList.Append(aRange);
92 0 : }
93 0 : }
94 :
95 : }
96 :
97 0 : ScAnalysisOfVarianceDialog::ScAnalysisOfVarianceDialog(
98 : SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow,
99 : vcl::Window* pParent, ScViewData* pViewData ) :
100 : ScStatisticsInputOutputDialog(
101 : pSfxBindings, pChildWindow, pParent, pViewData,
102 : "AnalysisOfVarianceDialog", "modules/scalc/ui/analysisofvariancedialog.ui" ),
103 0 : meFactor(SINGLE_FACTOR)
104 : {
105 0 : get(mpAlphaField, "alpha-spin");
106 0 : get(mpSingleFactorRadio, "radio-single-factor");
107 0 : get(mpTwoFactorRadio, "radio-two-factor");
108 0 : get(mpRowsPerSampleField, "rows-per-sample-spin");
109 :
110 0 : mpSingleFactorRadio->SetToggleHdl( LINK( this, ScAnalysisOfVarianceDialog, FactorChanged ) );
111 0 : mpTwoFactorRadio->SetToggleHdl( LINK( this, ScAnalysisOfVarianceDialog, FactorChanged ) );
112 :
113 0 : mpSingleFactorRadio->Check(true);
114 0 : mpTwoFactorRadio->Check(false);
115 :
116 0 : FactorChanged(NULL);
117 0 : }
118 :
119 0 : ScAnalysisOfVarianceDialog::~ScAnalysisOfVarianceDialog()
120 0 : {}
121 :
122 0 : bool ScAnalysisOfVarianceDialog::Close()
123 : {
124 0 : return DoClose( ScAnalysisOfVarianceDialogWrapper::GetChildWindowId() );
125 : }
126 :
127 0 : sal_Int16 ScAnalysisOfVarianceDialog::GetUndoNameId()
128 : {
129 0 : return STR_ANALYSIS_OF_VARIANCE_UNDO_NAME;
130 : }
131 :
132 0 : IMPL_LINK_NOARG( ScAnalysisOfVarianceDialog, FactorChanged )
133 : {
134 0 : if (mpSingleFactorRadio->IsChecked())
135 : {
136 0 : mpGroupByRowsRadio->Enable(true);
137 0 : mpGroupByColumnsRadio->Enable(true);
138 0 : mpRowsPerSampleField->Enable(false);
139 0 : meFactor = SINGLE_FACTOR;
140 : }
141 0 : else if (mpTwoFactorRadio->IsChecked())
142 : {
143 0 : mpGroupByRowsRadio->Enable(false);
144 0 : mpGroupByColumnsRadio->Enable(false);
145 0 : mpRowsPerSampleField->Enable(false); // Rows per sample not yet implemented
146 0 : meFactor = TWO_FACTOR;
147 : }
148 :
149 0 : return 0;
150 : }
151 :
152 0 : void ScAnalysisOfVarianceDialog::RowColumn(ScRangeList& rRangeList, AddressWalkerWriter& aOutput, FormulaTemplate& aTemplate,
153 : OUString& sFormula, GroupedBy aGroupedBy, ScRange* pResultRange)
154 : {
155 0 : if (pResultRange != NULL)
156 0 : pResultRange->aStart = aOutput.current();
157 0 : if (!sFormula.isEmpty())
158 : {
159 0 : for (size_t i = 0; i < rRangeList.size(); i++)
160 : {
161 0 : ScRange* pRange = rRangeList[i];
162 0 : aTemplate.setTemplate(sFormula);
163 0 : aTemplate.applyRange(strWildcardRange, *pRange);
164 0 : aOutput.writeFormula(aTemplate.getTemplate());
165 0 : if (pResultRange != NULL)
166 0 : pResultRange->aEnd = aOutput.current();
167 0 : aOutput.nextRow();
168 : }
169 : }
170 : else
171 : {
172 0 : sal_Int16 aLabelId = (aGroupedBy == BY_COLUMN) ? STR_COLUMN_LABEL_TEMPLATE : STR_ROW_LABEL_TEMPLATE;
173 0 : OUString aLabelTemplate(SC_STRLOAD(RID_STATISTICS_DLGS, aLabelId));
174 :
175 0 : for (size_t i = 0; i < rRangeList.size(); i++)
176 : {
177 0 : aTemplate.setTemplate(aLabelTemplate);
178 0 : aTemplate.applyNumber(strWildcardNumber, i + 1);
179 0 : aOutput.writeString(aTemplate.getTemplate());
180 0 : if (pResultRange != NULL)
181 0 : pResultRange->aEnd = aOutput.current();
182 0 : aOutput.nextRow();
183 0 : }
184 : }
185 0 : }
186 :
187 0 : void ScAnalysisOfVarianceDialog::AnovaSingleFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate)
188 : {
189 0 : output.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_SINGLE_FACTOR_LABEL));
190 0 : output.newLine();
191 :
192 0 : double aAlphaValue = mpAlphaField->GetValue() / 100.0;
193 0 : output.writeString("Alpha");
194 0 : output.nextColumn();
195 0 : output.writeValue(aAlphaValue);
196 0 : aTemplate.autoReplaceAddress("%ALPHA%", output.current());
197 0 : output.newLine();
198 0 : output.newLine();
199 :
200 : // Write labels
201 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
202 : {
203 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclBasicStatistics[i].aLabelId));
204 0 : output.nextColumn();
205 : }
206 0 : output.newLine();
207 :
208 : // Collect aRangeList
209 0 : ScRangeList aRangeList;
210 0 : lclMakeSubRangesList(aRangeList, mInputRange, mGroupedBy);
211 :
212 0 : output.push();
213 :
214 : // Write values
215 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
216 : {
217 0 : output.resetRow();
218 0 : ScRange aResultRange;
219 0 : OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
220 0 : RowColumn(aRangeList, output, aTemplate, sFormula, mGroupedBy, &aResultRange);
221 0 : output.nextColumn();
222 0 : if (lclBasicStatistics[i].aResultRangeName != NULL)
223 : {
224 0 : OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
225 0 : aTemplate.autoReplaceRange("%" + sResultRangeName + "%", aResultRange);
226 : }
227 0 : }
228 :
229 0 : output.nextRow(); // Blank row
230 :
231 : // Write ANOVA labels
232 0 : output.resetColumn();
233 0 : for(sal_Int32 i = 0; lclAnovaLabels[i] != 0; i++)
234 : {
235 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclAnovaLabels[i]));
236 0 : output.nextColumn();
237 : }
238 0 : output.nextRow();
239 :
240 0 : aTemplate.autoReplaceRange("%FIRST_COLUMN%", *aRangeList[0]);
241 :
242 : // Between Groups
243 : {
244 : // Label
245 0 : output.resetColumn();
246 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_BETWEEN_GROUPS));
247 0 : output.nextColumn();
248 :
249 : // Sum of Squares
250 :
251 0 : aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE%;%MEAN_RANGE%)-SUM(%SUM_RANGE%)^2/SUM(%COUNT_RANGE%)");
252 0 : aTemplate.autoReplaceAddress("%BETWEEN_SS%", output.current());
253 0 : output.writeFormula(aTemplate.getTemplate());
254 0 : output.nextColumn();
255 :
256 : // Degree of freedom
257 0 : aTemplate.setTemplate("=COUNT(%SUM_RANGE%)-1");
258 0 : aTemplate.autoReplaceAddress("%BETWEEN_DF%", output.current());
259 0 : output.writeFormula(aTemplate.getTemplate());
260 0 : output.nextColumn();
261 :
262 : // MS
263 0 : aTemplate.setTemplate("=%BETWEEN_SS% / %BETWEEN_DF%");
264 0 : aTemplate.autoReplaceAddress("%BETWEEN_MS%", output.current());
265 0 : output.writeFormula(aTemplate.getTemplate());
266 0 : output.nextColumn();
267 :
268 : // F
269 0 : aTemplate.setTemplate("=%BETWEEN_MS% / %WITHIN_MS%");
270 0 : aTemplate.applyAddress("%WITHIN_MS%", output.current(-1, 1));
271 0 : aTemplate.autoReplaceAddress("%F_VAL%", output.current());
272 0 : output.writeFormula(aTemplate.getTemplate());
273 0 : output.nextColumn();
274 :
275 : // P-value
276 0 : aTemplate.setTemplate("=FDIST(%F_VAL%; %BETWEEN_DF%; %WITHIN_DF%");
277 0 : aTemplate.applyAddress("%WITHIN_DF%", output.current(-3, 1));
278 0 : output.writeFormula(aTemplate.getTemplate());
279 0 : output.nextColumn();
280 :
281 : // F critical
282 0 : aTemplate.setTemplate("=FINV(%ALPHA%; %BETWEEN_DF%; %WITHIN_DF%");
283 0 : aTemplate.applyAddress("%WITHIN_DF%", output.current(-4, 1));
284 0 : output.writeFormula(aTemplate.getTemplate());
285 : }
286 0 : output.nextRow();
287 :
288 : // Within Groups
289 : {
290 : // Label
291 0 : output.resetColumn();
292 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_WITHIN_GROUPS));
293 0 : output.nextColumn();
294 :
295 : // Sum of Squares
296 0 : OUString aSSPart = lclCreateMultiParameterFormula(aRangeList, OUString("DEVSQ(%RANGE%)"), strWildcardRange, mDocument, mAddressDetails);
297 0 : aTemplate.setTemplate("=SUM(%RANGE%)");
298 0 : aTemplate.applyString(strWildcardRange, aSSPart);
299 0 : aTemplate.autoReplaceAddress("%WITHIN_SS%", output.current());
300 0 : output.writeFormula(aTemplate.getTemplate());
301 0 : output.nextColumn();
302 :
303 : // Degree of freedom
304 0 : aTemplate.setTemplate("=SUM(%COUNT_RANGE%)-COUNT(%COUNT_RANGE%)");
305 0 : aTemplate.autoReplaceAddress("%WITHIN_DF%", output.current());
306 0 : output.writeFormula(aTemplate.getTemplate());
307 0 : output.nextColumn();
308 :
309 : // MS
310 0 : aTemplate.setTemplate("=%WITHIN_SS% / %WITHIN_DF%");
311 0 : output.writeFormula(aTemplate.getTemplate());
312 : }
313 0 : output.nextRow();
314 :
315 : // Total
316 : {
317 : // Label
318 0 : output.resetColumn();
319 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_TOTAL));
320 0 : output.nextColumn();
321 :
322 : // Sum of Squares
323 0 : aTemplate.setTemplate("=DEVSQ(%RANGE_LIST%)");
324 0 : aTemplate.applyRangeList("%RANGE_LIST%", aRangeList);
325 0 : output.writeFormula(aTemplate.getTemplate());
326 0 : output.nextColumn();
327 :
328 : // Degree of freedom
329 0 : aTemplate.setTemplate("=SUM(%COUNT_RANGE%) - 1");
330 0 : output.writeFormula(aTemplate.getTemplate());
331 : }
332 0 : output.nextRow();
333 0 : }
334 :
335 0 : void ScAnalysisOfVarianceDialog::AnovaTwoFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate)
336 : {
337 0 : output.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_TWO_FACTOR_LABEL));
338 0 : output.newLine();
339 :
340 0 : double aAlphaValue = mpAlphaField->GetValue() / 100.0;
341 0 : output.writeString("Alpha");
342 0 : output.nextColumn();
343 0 : output.writeValue(aAlphaValue);
344 0 : aTemplate.autoReplaceAddress("%ALPHA%", output.current());
345 0 : output.newLine();
346 0 : output.newLine();
347 :
348 : // Write labels
349 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
350 : {
351 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclBasicStatistics[i].aLabelId));
352 0 : output.nextColumn();
353 : }
354 0 : output.newLine();
355 :
356 0 : ScRangeList aColumnRangeList;
357 0 : ScRangeList aRowRangeList;
358 :
359 0 : lclMakeSubRangesList(aColumnRangeList, mInputRange, BY_COLUMN);
360 0 : lclMakeSubRangesList(aRowRangeList, mInputRange, BY_ROW);
361 :
362 : // Write ColumnX values
363 0 : output.push();
364 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
365 : {
366 0 : output.resetRow();
367 0 : ScRange aResultRange;
368 0 : OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
369 0 : RowColumn(aColumnRangeList, output, aTemplate, sFormula, BY_COLUMN, &aResultRange);
370 0 : if (lclBasicStatistics[i].aResultRangeName != NULL)
371 : {
372 0 : OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
373 0 : aTemplate.autoReplaceRange("%" + sResultRangeName + "_COLUMN%", aResultRange);
374 : }
375 0 : output.nextColumn();
376 0 : }
377 0 : output.newLine();
378 :
379 : // Write RowX values
380 0 : output.push();
381 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
382 : {
383 0 : output.resetRow();
384 0 : ScRange aResultRange;
385 0 : OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
386 0 : RowColumn(aRowRangeList, output, aTemplate, sFormula, BY_ROW, &aResultRange);
387 :
388 0 : if (lclBasicStatistics[i].aResultRangeName != NULL)
389 : {
390 0 : OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
391 0 : aTemplate.autoReplaceRange("%" + sResultRangeName + "_ROW%", aResultRange);
392 : }
393 0 : output.nextColumn();
394 0 : }
395 0 : output.newLine();
396 :
397 : // Write ANOVA labels
398 0 : for(sal_Int32 i = 0; lclAnovaLabels[i] != 0; i++)
399 : {
400 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclAnovaLabels[i]));
401 0 : output.nextColumn();
402 : }
403 0 : output.nextRow();
404 :
405 : // Setup auto-replace strings
406 0 : aTemplate.autoReplaceRange(strWildcardRange, mInputRange);
407 0 : aTemplate.autoReplaceRange("%FIRST_COLUMN%", *aColumnRangeList[0]);
408 0 : aTemplate.autoReplaceRange("%FIRST_ROW%", *aRowRangeList[0]);
409 :
410 : // Rows
411 : {
412 : // Label
413 0 : output.resetColumn();
414 0 : output.writeString("Rows");
415 0 : output.nextColumn();
416 :
417 : // Sum of Squares
418 0 : aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)");
419 0 : aTemplate.autoReplaceAddress("%ROW_SS%", output.current());
420 0 : output.writeFormula(aTemplate.getTemplate());
421 0 : output.nextColumn();
422 :
423 : // Degree of freedom
424 0 : aTemplate.setTemplate("=MAX(%COUNT_RANGE_COLUMN%) - 1");
425 0 : aTemplate.autoReplaceAddress("%ROW_DF%", output.current());
426 0 : output.writeFormula(aTemplate.getTemplate());
427 0 : output.nextColumn();
428 :
429 : // MS
430 0 : aTemplate.setTemplate("=%ROW_SS% / %ROW_DF%");
431 0 : aTemplate.autoReplaceAddress("%MS_ROW%", output.current());
432 0 : output.writeFormula(aTemplate.getTemplate());
433 0 : output.nextColumn();
434 :
435 : // F
436 0 : aTemplate.setTemplate("=%MS_ROW% / %MS_ERROR%");
437 0 : aTemplate.applyAddress("%MS_ERROR%", output.current(-1, 2));
438 0 : aTemplate.autoReplaceAddress("%F_ROW%", output.current());
439 0 : output.writeFormula(aTemplate.getTemplate());
440 0 : output.nextColumn();
441 :
442 : // P-value
443 0 : aTemplate.setTemplate("=FDIST(%F_ROW%; %ROW_DF%; %ERROR_DF%");
444 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-3, 2));
445 0 : output.writeFormula(aTemplate.getTemplate());
446 0 : output.nextColumn();
447 :
448 : // F critical
449 0 : aTemplate.setTemplate("=FINV(%ALPHA%; %ROW_DF%; %ERROR_DF%");
450 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-4, 2));
451 0 : output.writeFormula(aTemplate.getTemplate());
452 0 : output.nextColumn();
453 : }
454 0 : output.nextRow();
455 :
456 : // Columns
457 : {
458 : // Label
459 0 : output.resetColumn();
460 0 : output.writeString("Columns");
461 0 : output.nextColumn();
462 :
463 : // Sum of Squares
464 0 : aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)");
465 0 : aTemplate.autoReplaceAddress("%COLUMN_SS%", output.current());
466 0 : output.writeFormula(aTemplate.getTemplate());
467 0 : output.nextColumn();
468 :
469 : // Degree of freedom
470 0 : aTemplate.setTemplate("=MAX(%COUNT_RANGE_ROW%) - 1");
471 0 : aTemplate.autoReplaceAddress("%COLUMN_DF%", output.current());
472 0 : output.writeFormula(aTemplate.getTemplate());
473 0 : output.nextColumn();
474 :
475 : // MS
476 0 : aTemplate.setTemplate("=%COLUMN_SS% / %COLUMN_DF%");
477 0 : aTemplate.autoReplaceAddress("%MS_COLUMN%", output.current());
478 0 : output.writeFormula(aTemplate.getTemplate());
479 0 : output.nextColumn();
480 :
481 : // F
482 0 : aTemplate.setTemplate("=%MS_COLUMN% / %MS_ERROR%");
483 0 : aTemplate.applyAddress("%MS_ERROR%", output.current(-1, 1));
484 0 : aTemplate.autoReplaceAddress("%F_COLUMN%", output.current());
485 0 : output.writeFormula(aTemplate.getTemplate());
486 0 : output.nextColumn();
487 :
488 : // P-value
489 0 : aTemplate.setTemplate("=FDIST(%F_COLUMN%; %COLUMN_DF%; %ERROR_DF%");
490 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-3, 1));
491 0 : output.writeFormula(aTemplate.getTemplate());
492 0 : output.nextColumn();
493 :
494 : // F critical
495 0 : aTemplate.setTemplate("=FINV(%ALPHA%; %COLUMN_DF%; %ERROR_DF%");
496 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-4, 1));
497 0 : output.writeFormula(aTemplate.getTemplate());
498 0 : output.nextColumn();
499 : }
500 0 : output.nextRow();
501 :
502 : // Error
503 : {
504 : // Label
505 0 : output.resetColumn();
506 0 : output.writeString("Error");
507 0 : output.nextColumn();
508 :
509 : // Sum of Squares
510 0 : aTemplate.setTemplate("=SUMSQ(%RANGE%)+SUM(%RANGE%)^2/COUNT(%RANGE%) - (SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) + SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%))");
511 0 : aTemplate.autoReplaceAddress("%ERROR_SS%", output.current());
512 0 : output.writeFormula(aTemplate.getTemplate());
513 0 : output.nextColumn();
514 :
515 : // Degree of freedom
516 0 : aTemplate.setTemplate("=%TOTAL_DF% - %ROW_DF% - %COLUMN_DF%");
517 0 : aTemplate.applyAddress("%TOTAL_DF%", output.current(0,1,0));
518 0 : aTemplate.autoReplaceAddress("%ERROR_DF%", output.current());
519 0 : output.writeFormula(aTemplate.getTemplate());
520 0 : output.nextColumn();
521 :
522 : // MS
523 0 : aTemplate.setTemplate("=%ERROR_SS% / %ERROR_DF%");
524 0 : output.writeFormula(aTemplate.getTemplate());
525 : }
526 0 : output.nextRow();
527 :
528 : // Total
529 : {
530 : // Label
531 0 : output.resetColumn();
532 0 : output.writeString("Total");
533 0 : output.nextColumn();
534 :
535 : // Sum of Squares
536 0 : aTemplate.setTemplate("=SUM(%ROW_SS%;%COLUMN_SS%;%ERROR_SS%)");
537 0 : output.writeFormula(aTemplate.getTemplate());
538 0 : output.nextColumn();
539 :
540 : // Degree of freedom
541 0 : aTemplate.setTemplate("=COUNT(%RANGE%)-1");
542 0 : output.writeFormula(aTemplate.getTemplate());
543 0 : output.nextColumn();
544 0 : }
545 0 : }
546 :
547 0 : ScRange ScAnalysisOfVarianceDialog::ApplyOutput(ScDocShell* pDocShell)
548 : {
549 : AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument,
550 0 : formula::FormulaGrammar::mergeToGrammar(formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv));
551 0 : FormulaTemplate aTemplate(mDocument);
552 :
553 0 : if (meFactor == SINGLE_FACTOR)
554 : {
555 0 : AnovaSingleFactor(output, aTemplate);
556 : }
557 0 : else if (meFactor == TWO_FACTOR)
558 : {
559 0 : AnovaTwoFactor(output, aTemplate);
560 : }
561 :
562 0 : return ScRange(output.mMinimumAddress, output.mMaximumAddress);
563 228 : }
564 :
565 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
