sc: t-test - refactor to row oriented output & externalise strings

Change-Id: I70b6d9ecc02aabf691b3606487e719885e6430fd

3 files changed, 132 insertions, 49 deletions

diff --git a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc
index df0162af7f32..b22f3dce188e 100644
--- a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc
+++ b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.hrc
@@ -75,6 +75,8 @@
 #define STR_CORRELATION_LABEL                       80
 #define STR_COVARIANCE_LABEL                        81
 #define STR_LABEL_ALPHA                             82
+#define STR_VARIABLE_1_LABEL                        83
+#define STR_VARIABLE_2_LABEL                        84
 
 #define STR_FTEST_OBSERVATIONS_LABEL                90
 #define STR_FTEST_P_RIGHT_TAIL                      91
@@ -84,4 +86,15 @@
 #define STR_FTEST_P_TWO_TAIL                        95
 #define STR_FTEST_F_CRITICAL_TWO_TAIL               96
 
+#define STR_TTEST_PEARSON_CORRELATION               100
+#define STR_TTEST_HYPOTHESIZED_MEAN_DIFFERENCE      101
+#define STR_TTEST_OBSERVED_MEAN_DIFFERENCE          102
+#define STR_TTEST_VARIANCE_OF_THE_DIFFERENCES       103
+#define STR_TTEST_T_STAT                            104
+#define STR_TTEST_P_ONE_TAIL                        105
+#define STR_TTEST_T_CRITICAL_ONE_TAIL               106
+#define STR_TTEST_P_TWO_TAIL                        107
+#define STR_TTEST_T_CRITICAL_TWO_TAIL               108
+
+
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src
index df73d6cae2fb..2d9f946f636a 100644
--- a/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src
+++ b/sc/source/ui/StatisticsDialogs/StatisticsDialogs.src
@@ -286,6 +286,15 @@ Resource RID_STATISTICS_DLGS
     {
         Text [ en-US ] = "Alpha";
     };
+    String STR_VARIABLE_1_LABEL
+    {
+        Text [ en-US ] = "Variable 1";
+    };
+    String STR_VARIABLE_2_LABEL
+    {
+        Text [ en-US ] = "Variable 2";
+    };
+
 
     /*F Test */
     String STR_FTEST_OBSERVATIONS_LABEL
@@ -316,6 +325,44 @@ Resource RID_STATISTICS_DLGS
     {
         Text [ en-US ] = "F Critical two-tail";
     };
+
+    /*t Test*/
+    String STR_TTEST_PEARSON_CORRELATION
+    {
+        Text [ en-US ] = "Pearson Correlation";
+    };
+    String STR_TTEST_HYPOTHESIZED_MEAN_DIFFERENCE
+    {
+        Text [ en-US ] = "Hypothesized Mean Difference";
+    };
+    String STR_TTEST_OBSERVED_MEAN_DIFFERENCE
+    {
+        Text [ en-US ] = "Observed Mean Difference";
+    };
+    String STR_TTEST_VARIANCE_OF_THE_DIFFERENCES
+    {
+        Text [ en-US ] = "Variance of the Differences";
+    };
+    String STR_TTEST_T_STAT
+    {
+        Text [ en-US ] = "t Stat";
+    };
+    String STR_TTEST_P_ONE_TAIL
+    {
+        Text [ en-US ] = "P (T<=t) one-tail";
+    };
+    String STR_TTEST_T_CRITICAL_ONE_TAIL
+    {
+        Text [ en-US ] = "t Critical one-tail";
+    };
+    String STR_TTEST_P_TWO_TAIL
+    {
+        Text [ en-US ] = "P (T<=t) two-tail";
+    };
+    String STR_TTEST_T_CRITICAL_TWO_TAIL
+    {
+        Text [ en-US ] = "t Critical two-tail";
+    };
 };
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/StatisticsDialogs/TTestDialog.cxx b/sc/source/ui/StatisticsDialogs/TTestDialog.cxx
index 8a06d946c5ef..e2fb17c51e8d 100644
--- a/sc/source/ui/StatisticsDialogs/TTestDialog.cxx
+++ b/sc/source/ui/StatisticsDialogs/TTestDialog.cxx
@@ -79,115 +79,138 @@ ScRange ScTTestDialog::ApplyOutput(ScDocShell* pDocShell)
     aTemplate.autoReplaceRange(strWildcardVariable1Range, pVariable1Iterator->get());
     aTemplate.autoReplaceRange(strWildcardVariable2Range, pVariable2Iterator->get());
 
+
+    aOutput.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_UNDO_NAME));
+    aOutput.resetColumn();
     aOutput.nextRow();
-    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STRID_CALC_MEAN));
-    aOutput.nextRow();
-    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STRID_CALC_VARIANCE));
-    aOutput.nextRow();
-    aOutput.writeString("Observations");
-    aOutput.nextRow();
-    aOutput.writeString("Pearson Correlation");
-    aOutput.nextRow();
-    aOutput.writeString("Hypothesized Mean Difference");
-    aOutput.nextRow();
-    aOutput.writeString("Observed Mean Difference");
-    aOutput.nextRow();
-    aOutput.writeString("Variance of the Differences");
-    aOutput.nextRow();
-    aOutput.writeString("df");
-    aOutput.nextRow();
-    aOutput.writeString("t Stat");
-    aOutput.nextRow();
-    aOutput.writeString("P (T<=t) one-tail");
-    aOutput.nextRow();
-    aOutput.writeString("t Critical one-tail");
-    aOutput.nextRow();
-    aOutput.writeString("P (T<=t) two-tail");
-    aOutput.nextRow();
-    aOutput.writeString("t Critical two-tail");
-    aOutput.resetRow();
 
+    aOutput.writeString("Alpha");
     aOutput.nextColumn();
+    aOutput.writeValue(0.05);
+    aTemplate.autoReplaceAddress("%ALPHA%", aOutput.current());
+    aOutput.resetColumn();
+    aOutput.nextRow();
 
-    aOutput.writeString("Variable 1");
+    aOutput.nextColumn();
+    aOutput.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_VARIABLE_1_LABEL));
+    aOutput.nextColumn();
+    aOutput.writeBoldString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_VARIABLE_2_LABEL));
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STRID_CALC_MEAN));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=AVERAGE(%VAR1_RANGE%)");
     aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.nextColumn();
+    aTemplate.setTemplate("=AVERAGE(%VAR2_RANGE%)");
+    aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STRID_CALC_VARIANCE));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=VAR(%VAR1_RANGE%)");
     aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.nextColumn();
+    aTemplate.setTemplate("=VAR(%VAR2_RANGE%)");
+    aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // Observations
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_FTEST_OBSERVATIONS_LABEL));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=COUNT(%VAR1_RANGE%)");
     aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.nextColumn();
+    aTemplate.setTemplate("=COUNT(%VAR2_RANGE%)");
+    aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // Pearson Correlation
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_PEARSON_CORRELATION));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=CORREL(%VAR1_RANGE%;%VAR2_RANGE%)");
     aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // Hypothesized mean difference
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_HYPOTHESIZED_MEAN_DIFFERENCE));
+    aOutput.nextColumn();
     aOutput.writeValue(2);
     aTemplate.autoReplaceAddress("%HYPOTHESIZED_MEAN_DIFFERENCE%", aOutput.current());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // Observed mean difference
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_OBSERVED_MEAN_DIFFERENCE));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=AVERAGE(IF(ISODD(IF(ISNUMBER(%VAR1_RANGE%); 1; 0) * IF(ISNUMBER(%VAR2_RANGE%); 1; 0)); %VAR1_RANGE% - %VAR2_RANGE%; \"NA\"))");
     aOutput.writeMatrixFormula(aTemplate.getTemplate());
     aTemplate.autoReplaceAddress("%OBSERVED_MEAN_DIFFERENCE%", aOutput.current());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // Variance of the Differences
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_VARIANCE_OF_THE_DIFFERENCES));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=VAR(IF(ISODD(IF(ISNUMBER(%VAR1_RANGE%); 1; 0) * IF(ISNUMBER(%VAR2_RANGE%); 1; 0)); %VAR1_RANGE% - %VAR2_RANGE%; \"NA\"))");
     aOutput.writeMatrixFormula(aTemplate.getTemplate());
     aTemplate.autoReplaceAddress("%VARIANCE_OF_DIFFERENCES%", aOutput.current());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // df
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_ANOVA_LABEL_DF));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=SUM(IF(ISNUMBER(%VAR1_RANGE%); 1; 0) * IF(ISNUMBER(%VAR2_RANGE%); 1; 0)) - 1");
     aOutput.writeMatrixFormula(aTemplate.getTemplate());
     aTemplate.autoReplaceAddress("%DEGREE_OF_FREEDOM%", aOutput.current());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // t stat
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_T_STAT));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=(%OBSERVED_MEAN_DIFFERENCE% - %HYPOTHESIZED_MEAN_DIFFERENCE%) / (%VARIANCE_OF_DIFFERENCES% / ( %DEGREE_OF_FREEDOM% + 1)) ^ 0.5");
     aOutput.writeFormula(aTemplate.getTemplate());
     aTemplate.autoReplaceAddress("%T_STAT%", aOutput.current());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // P one-tail
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_P_ONE_TAIL));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=TDIST(ABS(%T_STAT%); %DEGREE_OF_FREEDOM%; 1)");
     aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // T critical one-tail
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_T_CRITICAL_ONE_TAIL));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=TINV(2*0.05; %DEGREE_OF_FREEDOM%)");
     aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
+    // P two-tail
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_P_TWO_TAIL));
+    aOutput.nextColumn();
     aTemplate.setTemplate("=TDIST(ABS(%T_STAT%); %DEGREE_OF_FREEDOM%; 2)");
     aOutput.writeFormula(aTemplate.getTemplate());
+    aOutput.resetColumn();
     aOutput.nextRow();
 
-    aTemplate.setTemplate("=TINV(0.05; %DEGREE_OF_FREEDOM%)");
-    aOutput.writeFormula(aTemplate.getTemplate());
-    aOutput.nextRow();
-
-    aOutput.resetRow();
-
+    // T critical two-tail
+    aOutput.writeString(SC_STRLOAD(RID_STATISTICS_DLGS, STR_TTEST_T_CRITICAL_TWO_TAIL));
     aOutput.nextColumn();
-
-    aOutput.writeString("Variable 2");
-    aOutput.nextRow();
-
-    aTemplate.setTemplate("=AVERAGE(%VAR2_RANGE%)");
-    aOutput.writeFormula(aTemplate.getTemplate());
-    aOutput.nextRow();
-
-    aTemplate.setTemplate("=VAR(%VAR2_RANGE%)");
-    aOutput.writeFormula(aTemplate.getTemplate());
-    aOutput.nextRow();
-
-    aTemplate.setTemplate("=COUNT(%VAR2_RANGE%)");
+    aTemplate.setTemplate("=TINV(0.05; %DEGREE_OF_FREEDOM%)");
     aOutput.writeFormula(aTemplate.getTemplate());
-    aOutput.nextRow();
-
-    aOutput.resetRow();
+    aOutput.resetColumn();
 
     return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress);
 }