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 : static const char strWildcardRange[] = "%RANGE%";
61 : static const char 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 : {
121 0 : disposeOnce();
122 0 : }
123 :
124 0 : void ScAnalysisOfVarianceDialog::dispose()
125 : {
126 0 : mpAlphaField.clear();
127 0 : mpSingleFactorRadio.clear();
128 0 : mpTwoFactorRadio.clear();
129 0 : mpRowsPerSampleField.clear();
130 0 : ScStatisticsInputOutputDialog::dispose();
131 0 : }
132 :
133 0 : bool ScAnalysisOfVarianceDialog::Close()
134 : {
135 0 : return DoClose( ScAnalysisOfVarianceDialogWrapper::GetChildWindowId() );
136 : }
137 :
138 0 : sal_Int16 ScAnalysisOfVarianceDialog::GetUndoNameId()
139 : {
140 0 : return STR_ANALYSIS_OF_VARIANCE_UNDO_NAME;
141 : }
142 :
143 0 : IMPL_LINK_NOARG( ScAnalysisOfVarianceDialog, FactorChanged )
144 : {
145 0 : if (mpSingleFactorRadio->IsChecked())
146 : {
147 0 : mpGroupByRowsRadio->Enable(true);
148 0 : mpGroupByColumnsRadio->Enable(true);
149 0 : mpRowsPerSampleField->Enable(false);
150 0 : meFactor = SINGLE_FACTOR;
151 : }
152 0 : else if (mpTwoFactorRadio->IsChecked())
153 : {
154 0 : mpGroupByRowsRadio->Enable(false);
155 0 : mpGroupByColumnsRadio->Enable(false);
156 0 : mpRowsPerSampleField->Enable(false); // Rows per sample not yet implemented
157 0 : meFactor = TWO_FACTOR;
158 : }
159 :
160 0 : return 0;
161 : }
162 :
163 0 : void ScAnalysisOfVarianceDialog::RowColumn(ScRangeList& rRangeList, AddressWalkerWriter& aOutput, FormulaTemplate& aTemplate,
164 : OUString& sFormula, GroupedBy aGroupedBy, ScRange* pResultRange)
165 : {
166 0 : if (pResultRange != NULL)
167 0 : pResultRange->aStart = aOutput.current();
168 0 : if (!sFormula.isEmpty())
169 : {
170 0 : for (size_t i = 0; i < rRangeList.size(); i++)
171 : {
172 0 : ScRange* pRange = rRangeList[i];
173 0 : aTemplate.setTemplate(sFormula);
174 0 : aTemplate.applyRange(strWildcardRange, *pRange);
175 0 : aOutput.writeFormula(aTemplate.getTemplate());
176 0 : if (pResultRange != NULL)
177 0 : pResultRange->aEnd = aOutput.current();
178 0 : aOutput.nextRow();
179 : }
180 : }
181 : else
182 : {
183 0 : sal_Int16 aLabelId = (aGroupedBy == BY_COLUMN) ? STR_COLUMN_LABEL_TEMPLATE : STR_ROW_LABEL_TEMPLATE;
184 0 : OUString aLabelTemplate(SC_STRLOAD(RID_STATISTICS_DLGS, aLabelId));
185 :
186 0 : for (size_t i = 0; i < rRangeList.size(); i++)
187 : {
188 0 : aTemplate.setTemplate(aLabelTemplate);
189 0 : aTemplate.applyNumber(strWildcardNumber, i + 1);
190 0 : aOutput.writeString(aTemplate.getTemplate());
191 0 : if (pResultRange != NULL)
192 0 : pResultRange->aEnd = aOutput.current();
193 0 : aOutput.nextRow();
194 0 : }
195 : }
196 0 : }
197 :
198 0 : void ScAnalysisOfVarianceDialog::AnovaSingleFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate)
199 : {
200 0 : output.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_SINGLE_FACTOR_LABEL));
201 0 : output.newLine();
202 :
203 0 : double aAlphaValue = mpAlphaField->GetValue() / 100.0;
204 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_LABEL_ALPHA));
205 0 : output.nextColumn();
206 0 : output.writeValue(aAlphaValue);
207 0 : aTemplate.autoReplaceAddress("%ALPHA%", output.current());
208 0 : output.newLine();
209 0 : output.newLine();
210 :
211 : // Write labels
212 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
213 : {
214 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclBasicStatistics[i].aLabelId));
215 0 : output.nextColumn();
216 : }
217 0 : output.newLine();
218 :
219 : // Collect aRangeList
220 0 : ScRangeList aRangeList;
221 0 : lclMakeSubRangesList(aRangeList, mInputRange, mGroupedBy);
222 :
223 0 : output.push();
224 :
225 : // Write values
226 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
227 : {
228 0 : output.resetRow();
229 0 : ScRange aResultRange;
230 0 : OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
231 0 : RowColumn(aRangeList, output, aTemplate, sFormula, mGroupedBy, &aResultRange);
232 0 : output.nextColumn();
233 0 : if (lclBasicStatistics[i].aResultRangeName != NULL)
234 : {
235 0 : OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
236 0 : aTemplate.autoReplaceRange("%" + sResultRangeName + "%", aResultRange);
237 : }
238 0 : }
239 :
240 0 : output.nextRow(); // Blank row
241 :
242 : // Write ANOVA labels
243 0 : output.resetColumn();
244 0 : for(sal_Int32 i = 0; lclAnovaLabels[i] != 0; i++)
245 : {
246 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclAnovaLabels[i]));
247 0 : output.nextColumn();
248 : }
249 0 : output.nextRow();
250 :
251 0 : aTemplate.autoReplaceRange("%FIRST_COLUMN%", *aRangeList[0]);
252 :
253 : // Between Groups
254 : {
255 : // Label
256 0 : output.resetColumn();
257 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_BETWEEN_GROUPS));
258 0 : output.nextColumn();
259 :
260 : // Sum of Squares
261 :
262 0 : aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE%;%MEAN_RANGE%)-SUM(%SUM_RANGE%)^2/SUM(%COUNT_RANGE%)");
263 0 : aTemplate.autoReplaceAddress("%BETWEEN_SS%", output.current());
264 0 : output.writeFormula(aTemplate.getTemplate());
265 0 : output.nextColumn();
266 :
267 : // Degree of freedom
268 0 : aTemplate.setTemplate("=COUNT(%SUM_RANGE%)-1");
269 0 : aTemplate.autoReplaceAddress("%BETWEEN_DF%", output.current());
270 0 : output.writeFormula(aTemplate.getTemplate());
271 0 : output.nextColumn();
272 :
273 : // MS
274 0 : aTemplate.setTemplate("=%BETWEEN_SS% / %BETWEEN_DF%");
275 0 : aTemplate.autoReplaceAddress("%BETWEEN_MS%", output.current());
276 0 : output.writeFormula(aTemplate.getTemplate());
277 0 : output.nextColumn();
278 :
279 : // F
280 0 : aTemplate.setTemplate("=%BETWEEN_MS% / %WITHIN_MS%");
281 0 : aTemplate.applyAddress("%WITHIN_MS%", output.current(-1, 1));
282 0 : aTemplate.autoReplaceAddress("%F_VAL%", output.current());
283 0 : output.writeFormula(aTemplate.getTemplate());
284 0 : output.nextColumn();
285 :
286 : // P-value
287 0 : aTemplate.setTemplate("=FDIST(%F_VAL%; %BETWEEN_DF%; %WITHIN_DF%");
288 0 : aTemplate.applyAddress("%WITHIN_DF%", output.current(-3, 1));
289 0 : output.writeFormula(aTemplate.getTemplate());
290 0 : output.nextColumn();
291 :
292 : // F critical
293 0 : aTemplate.setTemplate("=FINV(%ALPHA%; %BETWEEN_DF%; %WITHIN_DF%");
294 0 : aTemplate.applyAddress("%WITHIN_DF%", output.current(-4, 1));
295 0 : output.writeFormula(aTemplate.getTemplate());
296 : }
297 0 : output.nextRow();
298 :
299 : // Within Groups
300 : {
301 : // Label
302 0 : output.resetColumn();
303 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_WITHIN_GROUPS));
304 0 : output.nextColumn();
305 :
306 : // Sum of Squares
307 0 : OUString aSSPart = lclCreateMultiParameterFormula(aRangeList, OUString("DEVSQ(%RANGE%)"), strWildcardRange, mDocument, mAddressDetails);
308 0 : aTemplate.setTemplate("=SUM(%RANGE%)");
309 0 : aTemplate.applyString(strWildcardRange, aSSPart);
310 0 : aTemplate.autoReplaceAddress("%WITHIN_SS%", output.current());
311 0 : output.writeFormula(aTemplate.getTemplate());
312 0 : output.nextColumn();
313 :
314 : // Degree of freedom
315 0 : aTemplate.setTemplate("=SUM(%COUNT_RANGE%)-COUNT(%COUNT_RANGE%)");
316 0 : aTemplate.autoReplaceAddress("%WITHIN_DF%", output.current());
317 0 : output.writeFormula(aTemplate.getTemplate());
318 0 : output.nextColumn();
319 :
320 : // MS
321 0 : aTemplate.setTemplate("=%WITHIN_SS% / %WITHIN_DF%");
322 0 : output.writeFormula(aTemplate.getTemplate());
323 : }
324 0 : output.nextRow();
325 :
326 : // Total
327 : {
328 : // Label
329 0 : output.resetColumn();
330 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_TOTAL));
331 0 : output.nextColumn();
332 :
333 : // Sum of Squares
334 0 : aTemplate.setTemplate("=DEVSQ(%RANGE_LIST%)");
335 0 : aTemplate.applyRangeList("%RANGE_LIST%", aRangeList);
336 0 : output.writeFormula(aTemplate.getTemplate());
337 0 : output.nextColumn();
338 :
339 : // Degree of freedom
340 0 : aTemplate.setTemplate("=SUM(%COUNT_RANGE%) - 1");
341 0 : output.writeFormula(aTemplate.getTemplate());
342 : }
343 0 : output.nextRow();
344 0 : }
345 :
346 0 : void ScAnalysisOfVarianceDialog::AnovaTwoFactor(AddressWalkerWriter& output, FormulaTemplate& aTemplate)
347 : {
348 0 : output.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_TWO_FACTOR_LABEL));
349 0 : output.newLine();
350 :
351 0 : double aAlphaValue = mpAlphaField->GetValue() / 100.0;
352 0 : output.writeString("Alpha");
353 0 : output.nextColumn();
354 0 : output.writeValue(aAlphaValue);
355 0 : aTemplate.autoReplaceAddress("%ALPHA%", output.current());
356 0 : output.newLine();
357 0 : output.newLine();
358 :
359 : // Write labels
360 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
361 : {
362 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclBasicStatistics[i].aLabelId));
363 0 : output.nextColumn();
364 : }
365 0 : output.newLine();
366 :
367 0 : ScRangeList aColumnRangeList;
368 0 : ScRangeList aRowRangeList;
369 :
370 0 : lclMakeSubRangesList(aColumnRangeList, mInputRange, BY_COLUMN);
371 0 : lclMakeSubRangesList(aRowRangeList, mInputRange, BY_ROW);
372 :
373 : // Write ColumnX values
374 0 : output.push();
375 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
376 : {
377 0 : output.resetRow();
378 0 : ScRange aResultRange;
379 0 : OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
380 0 : RowColumn(aColumnRangeList, output, aTemplate, sFormula, BY_COLUMN, &aResultRange);
381 0 : if (lclBasicStatistics[i].aResultRangeName != NULL)
382 : {
383 0 : OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
384 0 : aTemplate.autoReplaceRange("%" + sResultRangeName + "_COLUMN%", aResultRange);
385 : }
386 0 : output.nextColumn();
387 0 : }
388 0 : output.newLine();
389 :
390 : // Write RowX values
391 0 : output.push();
392 0 : for(sal_Int32 i = 0; lclBasicStatistics[i].aLabelId != 0; i++)
393 : {
394 0 : output.resetRow();
395 0 : ScRange aResultRange;
396 0 : OUString sFormula = OUString::createFromAscii(lclBasicStatistics[i].aFormula);
397 0 : RowColumn(aRowRangeList, output, aTemplate, sFormula, BY_ROW, &aResultRange);
398 :
399 0 : if (lclBasicStatistics[i].aResultRangeName != NULL)
400 : {
401 0 : OUString sResultRangeName = OUString::createFromAscii(lclBasicStatistics[i].aResultRangeName);
402 0 : aTemplate.autoReplaceRange("%" + sResultRangeName + "_ROW%", aResultRange);
403 : }
404 0 : output.nextColumn();
405 0 : }
406 0 : output.newLine();
407 :
408 : // Write ANOVA labels
409 0 : for(sal_Int32 i = 0; lclAnovaLabels[i] != 0; i++)
410 : {
411 0 : output.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, lclAnovaLabels[i]));
412 0 : output.nextColumn();
413 : }
414 0 : output.nextRow();
415 :
416 : // Setup auto-replace strings
417 0 : aTemplate.autoReplaceRange(strWildcardRange, mInputRange);
418 0 : aTemplate.autoReplaceRange("%FIRST_COLUMN%", *aColumnRangeList[0]);
419 0 : aTemplate.autoReplaceRange("%FIRST_ROW%", *aRowRangeList[0]);
420 :
421 : // Rows
422 : {
423 : // Label
424 0 : output.resetColumn();
425 0 : output.writeString("Rows");
426 0 : output.nextColumn();
427 :
428 : // Sum of Squares
429 0 : aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)");
430 0 : aTemplate.autoReplaceAddress("%ROW_SS%", output.current());
431 0 : output.writeFormula(aTemplate.getTemplate());
432 0 : output.nextColumn();
433 :
434 : // Degree of freedom
435 0 : aTemplate.setTemplate("=MAX(%COUNT_RANGE_COLUMN%) - 1");
436 0 : aTemplate.autoReplaceAddress("%ROW_DF%", output.current());
437 0 : output.writeFormula(aTemplate.getTemplate());
438 0 : output.nextColumn();
439 :
440 : // MS
441 0 : aTemplate.setTemplate("=%ROW_SS% / %ROW_DF%");
442 0 : aTemplate.autoReplaceAddress("%MS_ROW%", output.current());
443 0 : output.writeFormula(aTemplate.getTemplate());
444 0 : output.nextColumn();
445 :
446 : // F
447 0 : aTemplate.setTemplate("=%MS_ROW% / %MS_ERROR%");
448 0 : aTemplate.applyAddress("%MS_ERROR%", output.current(-1, 2));
449 0 : aTemplate.autoReplaceAddress("%F_ROW%", output.current());
450 0 : output.writeFormula(aTemplate.getTemplate());
451 0 : output.nextColumn();
452 :
453 : // P-value
454 0 : aTemplate.setTemplate("=FDIST(%F_ROW%; %ROW_DF%; %ERROR_DF%");
455 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-3, 2));
456 0 : output.writeFormula(aTemplate.getTemplate());
457 0 : output.nextColumn();
458 :
459 : // F critical
460 0 : aTemplate.setTemplate("=FINV(%ALPHA%; %ROW_DF%; %ERROR_DF%");
461 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-4, 2));
462 0 : output.writeFormula(aTemplate.getTemplate());
463 0 : output.nextColumn();
464 : }
465 0 : output.nextRow();
466 :
467 : // Columns
468 : {
469 : // Label
470 0 : output.resetColumn();
471 0 : output.writeString("Columns");
472 0 : output.nextColumn();
473 :
474 : // Sum of Squares
475 0 : aTemplate.setTemplate("=SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%) - SUM(%RANGE%)^2 / COUNT(%RANGE%)");
476 0 : aTemplate.autoReplaceAddress("%COLUMN_SS%", output.current());
477 0 : output.writeFormula(aTemplate.getTemplate());
478 0 : output.nextColumn();
479 :
480 : // Degree of freedom
481 0 : aTemplate.setTemplate("=MAX(%COUNT_RANGE_ROW%) - 1");
482 0 : aTemplate.autoReplaceAddress("%COLUMN_DF%", output.current());
483 0 : output.writeFormula(aTemplate.getTemplate());
484 0 : output.nextColumn();
485 :
486 : // MS
487 0 : aTemplate.setTemplate("=%COLUMN_SS% / %COLUMN_DF%");
488 0 : aTemplate.autoReplaceAddress("%MS_COLUMN%", output.current());
489 0 : output.writeFormula(aTemplate.getTemplate());
490 0 : output.nextColumn();
491 :
492 : // F
493 0 : aTemplate.setTemplate("=%MS_COLUMN% / %MS_ERROR%");
494 0 : aTemplate.applyAddress("%MS_ERROR%", output.current(-1, 1));
495 0 : aTemplate.autoReplaceAddress("%F_COLUMN%", output.current());
496 0 : output.writeFormula(aTemplate.getTemplate());
497 0 : output.nextColumn();
498 :
499 : // P-value
500 0 : aTemplate.setTemplate("=FDIST(%F_COLUMN%; %COLUMN_DF%; %ERROR_DF%");
501 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-3, 1));
502 0 : output.writeFormula(aTemplate.getTemplate());
503 0 : output.nextColumn();
504 :
505 : // F critical
506 0 : aTemplate.setTemplate("=FINV(%ALPHA%; %COLUMN_DF%; %ERROR_DF%");
507 0 : aTemplate.applyAddress("%ERROR_DF%", output.current(-4, 1));
508 0 : output.writeFormula(aTemplate.getTemplate());
509 0 : output.nextColumn();
510 : }
511 0 : output.nextRow();
512 :
513 : // Error
514 : {
515 : // Label
516 0 : output.resetColumn();
517 0 : output.writeString("Error");
518 0 : output.nextColumn();
519 :
520 : // Sum of Squares
521 0 : aTemplate.setTemplate("=SUMSQ(%RANGE%)+SUM(%RANGE%)^2/COUNT(%RANGE%) - (SUMPRODUCT(%SUM_RANGE_ROW%;%MEAN_RANGE_ROW%) + SUMPRODUCT(%SUM_RANGE_COLUMN%;%MEAN_RANGE_COLUMN%))");
522 0 : aTemplate.autoReplaceAddress("%ERROR_SS%", output.current());
523 0 : output.writeFormula(aTemplate.getTemplate());
524 0 : output.nextColumn();
525 :
526 : // Degree of freedom
527 0 : aTemplate.setTemplate("=%TOTAL_DF% - %ROW_DF% - %COLUMN_DF%");
528 0 : aTemplate.applyAddress("%TOTAL_DF%", output.current(0,1,0));
529 0 : aTemplate.autoReplaceAddress("%ERROR_DF%", output.current());
530 0 : output.writeFormula(aTemplate.getTemplate());
531 0 : output.nextColumn();
532 :
533 : // MS
534 0 : aTemplate.setTemplate("=%ERROR_SS% / %ERROR_DF%");
535 0 : output.writeFormula(aTemplate.getTemplate());
536 : }
537 0 : output.nextRow();
538 :
539 : // Total
540 : {
541 : // Label
542 0 : output.resetColumn();
543 0 : output.writeString("Total");
544 0 : output.nextColumn();
545 :
546 : // Sum of Squares
547 0 : aTemplate.setTemplate("=SUM(%ROW_SS%;%COLUMN_SS%;%ERROR_SS%)");
548 0 : output.writeFormula(aTemplate.getTemplate());
549 0 : output.nextColumn();
550 :
551 : // Degree of freedom
552 0 : aTemplate.setTemplate("=COUNT(%RANGE%)-1");
553 0 : output.writeFormula(aTemplate.getTemplate());
554 0 : output.nextColumn();
555 0 : }
556 0 : }
557 :
558 0 : ScRange ScAnalysisOfVarianceDialog::ApplyOutput(ScDocShell* pDocShell)
559 : {
560 : AddressWalkerWriter output(mOutputAddress, pDocShell, mDocument,
561 0 : formula::FormulaGrammar::mergeToGrammar(formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv));
562 0 : FormulaTemplate aTemplate(mDocument);
563 :
564 0 : if (meFactor == SINGLE_FACTOR)
565 : {
566 0 : AnovaSingleFactor(output, aTemplate);
567 : }
568 0 : else if (meFactor == TWO_FACTOR)
569 : {
570 0 : AnovaTwoFactor(output, aTemplate);
571 : }
572 :
573 0 : return ScRange(output.mMinimumAddress, output.mMaximumAddress);
574 156 : }
575 :
576 : /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
|
