[Libreoffice-commits] core.git: sc/inc sc/qa sc/source sc/uiconfig
Dennis Francis
dennis.francis at collabora.co.uk
Mon Jul 2 21:20:04 UTC 2018
sc/inc/strings.hrc | 23
sc/qa/uitest/statistics/data/regression.ods |binary
sc/qa/uitest/statistics/regression.py | 314 ----
sc/source/ui/StatisticsDialogs/RegressionDialog.cxx | 707 ++++++++--
sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx | 28
sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx | 6
sc/source/ui/inc/RegressionDialog.hxx | 49
sc/source/ui/inc/StatisticsTwoVariableDialog.hxx | 2
sc/source/ui/inc/TableFillingAndNavigationTools.hxx | 2
sc/uiconfig/scalc/ui/regressiondialog.ui | 156 +-
10 files changed, 882 insertions(+), 405 deletions(-)
New commits:
commit b7a02f2bb66b990289eb7f4dc80069d1545179a4
Author: Dennis Francis <dennis.francis at collabora.co.uk>
Date: Mon Jun 25 23:42:26 2018 +0530
tdf#109042 : Add support for multivariate regression...
to regression tool. This means we now support more than
one X variable(independent variable). One caveat is that
all X variable observations needs to be present adjacent
to each other in the same table. For example if data is
grouped by columns, a valid organization of X variables
look like :-
X Variables ---->
A B C ...
XVar1 XVar2 XVar3 ... XVarN |
0.1 0.45 0.32 ... Observations
0.34 0.23 0.54 ... |
0.23 0.56 0.90 ... |
0.32 0.11 0.78 ... V
This patch also makes our regression tool output to have
similar structure to what Excel and Gnumeric does. This
means more statistical measures are added including
confidence intervals for all parmeter estimates.
We already have support for Logarithmic and Power regression
in addition to plain Linear regression. This patch's
multivariate support extends to all of these types of
regressions.
Earlier all regression statistics were computed separately
from scratch, which mostly compute the same regression
multiple times. This would slow things down if the
data-set being analysed is big. This is not true anymore
as we use LINEST() formula. LINEST() formula provides all
the necessary statistics needed in regression analysis, so
here it is called just once and its output components are
referenced to compute other statistics(derived).
Following are the UI changes for the regression dialog box :-
1. Changed the regression-type selectors from check-boxes
to radio-buttons. So only one type of regression can
be done at a time. This is because the output of a single
regression type itself shows a lot of information and
if do all types of regression, it is hard to read and
interpret especially for bigger data-sets with lots of
X variables.
2. Allow the variable's ranges to have label in them, via
a checkbox. If labels are provided, they are used to
annotate the variable specific statistics and the user
can easily identify the stats corresponding to each
variable.
3. More robust input validity checks, with error messages
at the bottom of the dialog to let the user know which
of their entry is invalid.
4. User can enter the confidence level (default = 95%)
for computing the confidence intervals of each estimate.
5. Make residual computations optional via a check-box,
as this involves writing a table with all X's and Y
with predicted Y and residual for each observation.
If the data-set is big, or the user just care about
the estimates and confidence intervals, they can
avoid this.
Finally the patch includes a uitest that tests all
3 types of regressions with a small dataset. The ground
truths for the tests were obtained by running
regression tool in Gnumeric.
Change-Id: I9762b716eae14b9fbd16e2c7228edf9e1930dc93
Reviewed-on: https://gerrit.libreoffice.org/56809
Tested-by: Jenkins
Reviewed-by: Michael Meeks <michael.meeks at collabora.com>
Reviewed-by: Tomaž Vajngerl <quikee at gmail.com>
diff --git a/sc/inc/strings.hrc b/sc/inc/strings.hrc
index 470f06161690..a91f295e1591 100644
--- a/sc/inc/strings.hrc
+++ b/sc/inc/strings.hrc
@@ -218,6 +218,7 @@
#define STR_EXPONENTIAL_SMOOTHING_UNDO_NAME NC_("STR_EXPONENTIAL_SMOOTHING_UNDO_NAME", "Exponential Smoothing")
/* AnalysisOfVarianceDialog */
#define STR_ANALYSIS_OF_VARIANCE_UNDO_NAME NC_("STR_ANALYSIS_OF_VARIANCE_UNDO_NAME", "Analysis of Variance")
+#define STR_LABEL_ANOVA NC_("STR_LABEL_ANOVA", "Analysis of Variance (ANOVA)")
#define STR_ANOVA_SINGLE_FACTOR_LABEL NC_("STR_ANOVA_SINGLE_FACTOR_LABEL", "ANOVA - Single Factor")
#define STR_ANOVA_TWO_FACTOR_LABEL NC_("STR_ANOVA_TWO_FACTOR_LABEL", "ANOVA - Two Factor")
#define STR_ANOVA_LABEL_GROUPS NC_("STR_ANOVA_LABEL_GROUPS", "Groups")
@@ -228,6 +229,7 @@
#define STR_ANOVA_LABEL_DF NC_("STR_ANOVA_LABEL_DF", "df")
#define STR_ANOVA_LABEL_MS NC_("STR_ANOVA_LABEL_MS", "MS")
#define STR_ANOVA_LABEL_F NC_("STR_ANOVA_LABEL_F", "F")
+#define STR_ANOVA_LABEL_SIGNIFICANCE_F NC_("STR_ANOVA_LABEL_SIGNIFICANCE_F", "Significance F")
#define STR_ANOVA_LABEL_P_VALUE NC_("STR_ANOVA_LABEL_P_VALUE", "P-value")
#define STR_ANOVA_LABEL_F_CRITICAL NC_("STR_ANOVA_LABEL_F_CRITICAL", "F critical")
#define STR_ANOVA_LABEL_TOTAL NC_("STR_ANOVA_LABEL_TOTAL", "Total")
@@ -295,18 +297,37 @@
#define STR_HYPOTHESIZED_MEAN_DIFFERENCE_LABEL NC_("STR_HYPOTHESIZED_MEAN_DIFFERENCE_LABEL", "Hypothesized Mean Difference")
#define STR_OBSERVATIONS_LABEL NC_("STR_OBSERVATIONS_LABEL", "Observations")
#define STR_OBSERVED_MEAN_DIFFERENCE_LABEL NC_("STR_OBSERVED_MEAN_DIFFERENCE_LABEL", "Observed Mean Difference")
+#define STR_LABEL_RSQUARED NC_("STR_LABEL_RSQUARED", "R^2")
+#define STR_LABEL_ADJUSTED_RSQUARED NC_("STR_LABEL_ADJUSTED_RSQUARED", "Adjusted R^2")
+#define STR_LABEL_XVARIABLES_COUNT NC_("STR_LABEL_XVARIABLES_COUNT", "Count of X variables")
#define STR_DEGREES_OF_FREEDOM_LABEL NC_("STR_DEGREES_OF_FREEDOM_LABEL", "df")
#define STR_P_VALUE_LABEL NC_("STR_P_VALUE_LABEL", "P-value")
#define STR_CRITICAL_VALUE_LABEL NC_("STR_CRITICAL_VALUE_LABEL", "Critical Value")
#define STR_TEST_STATISTIC_LABEL NC_("STR_TEST_STATISTIC_LABEL", "Test Statistic")
+#define STR_LABEL_LOWER NC_("STR_LABEL_LOWER", "Lower")
+#define STR_LABEL_UPPER NC_("STR_LABEL_Upper", "Upper")
/* RegressionDialog */
#define STR_LABEL_LINEAR NC_("STR_LABEL_LINEAR", "Linear")
#define STR_LABEL_LOGARITHMIC NC_("STR_LABEL_LOGARITHMIC", "Logarithmic")
#define STR_LABEL_POWER NC_("STR_LABEL_POWER", "Power")
+#define STR_MESSAGE_XINVALID_RANGE NC_("STR_MESSAGE_XINVALID_RANGE", "Independent variable(s) range is not valid.")
+#define STR_MESSAGE_YINVALID_RANGE NC_("STR_MESSAGE_YINVALID_RANGE", "Dependent variable(s) range is not valid.")
+#define STR_MESSAGE_INVALID_OUTPUT_ADDR NC_("STR_MESSAGE_INVALID_OUTPUT_ADDR", "Output range is not valid.")
+#define STR_MESSAGE_INVALID_CONFIDENCE_LEVEL NC_("STR_MESSAGE_INVALID_CONFIDENCE_LEVEL", "Confidence level must be in the interval (0, 1).")
+#define STR_MESSAGE_YVARIABLE_MULTI_COLUMN NC_("STR_MESSAGE_YVARIABLE_MULTI_COLUMN", "Y variable range cannot have more than 1 column.")
+#define STR_MESSAGE_YVARIABLE_MULTI_ROW NC_("STR_MESSAGE_YVARIABLE_MULTI_ROW", "Y variable range cannot have more than 1 row.")
+#define STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH NC_("STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH", "Univariate regression : The observation count in X and Y must match.")
+#define STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH NC_("STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH", "Multivariate regression : The observation count in X and Y must match.")
#define STR_LABEL_REGRESSION_MODEL NC_("STR_LABEL_REGRESSION_MODEL", "Regression Model")
-#define STR_LABEL_RSQUARED NC_("STR_LABEL_RSQUARED", "R^2")
+#define STR_LABEL_REGRESSION_STATISTICS NC_("STR_LABEL_REGRESSION_STATISTICS", "Regression Statistics")
+#define STR_LABEL_RESIDUAL NC_("STR_LABEL_RESIDUAL", "Residual")
+#define STR_LABEL_CONFIDENCE_LEVEL NC_("STR_LABEL_CONFIDENCE_LEVEL", "Confidence level")
+#define STR_LABEL_COEFFICIENTS NC_("STR_LABEL_COEFFICIENTS", "Coefficients")
+#define STR_LABEL_TSTATISTIC NC_("STR_LABEL_TSTATISTIC", "t-Statistic")
#define STR_LABEL_SLOPE NC_("STR_LABEL_SLOPE", "Slope")
#define STR_LABEL_INTERCEPT NC_("STR_LABEL_INTERCEPT", "Intercept")
+#define STR_LABEL_PREDICTEDY NC_("STR_LABEL_PREDICTEDY", "Predicted Y")
+#define STR_LINEST_RAW_OUTPUT_TITLE NC_("STR_LINEST_RAW_OUTPUT_TITLE", "LINEST raw output")
/*F Test */
#define STR_FTEST_P_RIGHT_TAIL NC_("STR_FTEST_P_RIGHT_TAIL", "P (F<=f) right-tail")
#define STR_FTEST_F_CRITICAL_RIGHT_TAIL NC_("STR_FTEST_F_CRITICAL_RIGHT_TAIL", "F Critical right-tail")
diff --git a/sc/qa/uitest/statistics/data/regression.ods b/sc/qa/uitest/statistics/data/regression.ods
new file mode 100644
index 000000000000..e38d0a7ab26b
Binary files /dev/null and b/sc/qa/uitest/statistics/data/regression.ods differ
diff --git a/sc/qa/uitest/statistics/regression.py b/sc/qa/uitest/statistics/regression.py
index b586d7af7c49..ccf1258a776e 100644
--- a/sc/qa/uitest/statistics/regression.py
+++ b/sc/qa/uitest/statistics/regression.py
@@ -5,285 +5,99 @@
# file, You can obtain one at http://mozilla.org/MPL/2.0/.
#
from uitest.framework import UITestCase
+from uitest.path import get_srcdir_url
from uitest.uihelper.common import get_state_as_dict
from uitest.uihelper.common import select_pos
from uitest.uihelper.calc import enter_text_to_cell
from libreoffice.calc.document import get_sheet_from_doc
-from libreoffice.calc.conditional_format import get_conditional_format_from_sheet
from uitest.debug import sleep
from libreoffice.calc.document import get_cell_by_position
from libreoffice.uno.propertyvalue import mkPropertyValues
+def get_url_for_data_file(file_name):
+ return get_srcdir_url() + "/sc/qa/uitest/statistics/data/" + file_name
+
class regression(UITestCase):
+ def test_regression_row(self):
+ self._regression_check(data_groupedby_column = False)
+
def test_regression_column(self):
- calc_doc = self.ui_test.create_doc_in_start_center("calc")
+ self._regression_check(data_groupedby_column = True)
+
+ def _regression_check(self, data_groupedby_column = True):
+ calc_doc = self.ui_test.load_file(get_url_for_data_file("regression.ods"))
xCalcDoc = self.xUITest.getTopFocusWindow()
gridwin = xCalcDoc.getChild("grid_window")
document = self.ui_test.get_component()
- #fill data
- enter_text_to_cell(gridwin, "A1", "Time")
- enter_text_to_cell(gridwin, "A2", "1")
- enter_text_to_cell(gridwin, "A3", "2")
- enter_text_to_cell(gridwin, "A4", "3")
- enter_text_to_cell(gridwin, "A5", "4")
- enter_text_to_cell(gridwin, "A6", "5")
- enter_text_to_cell(gridwin, "A7", "6")
- enter_text_to_cell(gridwin, "A8", "7")
- enter_text_to_cell(gridwin, "A9", "8")
- enter_text_to_cell(gridwin, "A10", "9")
- enter_text_to_cell(gridwin, "A11", "10")
- enter_text_to_cell(gridwin, "B1", "Measurement")
- enter_text_to_cell(gridwin, "B2", "2.7")
- enter_text_to_cell(gridwin, "B3", "4")
- enter_text_to_cell(gridwin, "B4", "4.4")
- enter_text_to_cell(gridwin, "B5", "7.1")
- enter_text_to_cell(gridwin, "B6", "4.9")
- enter_text_to_cell(gridwin, "B7", "3.6")
- enter_text_to_cell(gridwin, "B8", "4")
- enter_text_to_cell(gridwin, "B9", "0.6")
- enter_text_to_cell(gridwin, "B10", "1")
- enter_text_to_cell(gridwin, "B11", "4.3")
+ # Initially the final check status is "FALSE" (failed).
+ self.assertEqual(get_cell_by_position(document, 11, 1, 5).getString(), "FALSE",
+ "Check status must be FALSE before the test")
+ self._do_regression(regression_type = "LINEAR", data_groupedby_column = data_groupedby_column)
+ self._do_regression(regression_type = "LOG", data_groupedby_column = data_groupedby_column)
+ self._do_regression(regression_type = "POWER", data_groupedby_column = data_groupedby_column)
+ self.assertEqual(get_cell_by_position(document, 11, 1, 5).getString(), "TRUE",
+ "One of more of the checks failed for data_groupedby_column = {}, manually try with the document".
+ format(data_groupedby_column))
+ self.ui_test.close_doc()
- gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "A2:B11"}))
+ def _do_regression(self, regression_type, data_groupedby_column = True):
+ assert(regression_type == "LINEAR" or regression_type == "LOG" or regression_type == "POWER")
self.ui_test.execute_modeless_dialog_through_command(".uno:RegressionDialog")
xDialog = self.xUITest.getTopFocusWindow()
xvariable1rangeedit = xDialog.getChild("variable1-range-edit")
xvariable2rangeedit = xDialog.getChild("variable2-range-edit")
xoutputrangeedit = xDialog.getChild("output-range-edit")
+ xwithlabelscheck = xDialog.getChild("withlabels-check")
xgroupedbyrowsradio = xDialog.getChild("groupedby-rows-radio")
xgroupedbycolumnsradio = xDialog.getChild("groupedby-columns-radio")
- xlinearcheck = xDialog.getChild("linear-check")
- xlogarithmiccheck = xDialog.getChild("logarithmic-check")
- xpowercheck = xDialog.getChild("power-check")
+ xlinearradio = xDialog.getChild("linear-radio")
+ xlogarithmicradio = xDialog.getChild("logarithmic-radio")
+ xpowerradio = xDialog.getChild("power-radio")
+ ## Set the X, Y and output ranges
+ xvariable1rangeedit.executeAction("FOCUS", tuple()) # Without this the range parser does not kick in somehow
xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"}))
xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"}))
- xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$A$2:$A$11"}))
+ if data_groupedby_column:
+ xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInColumns.$A$1:$C$11"}))
+ else:
+ xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInRows.$A$1:$K$3"}))
+
+ xvariable2rangeedit.executeAction("FOCUS", tuple()) # Without this the range parser does not kick in somehow
xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"}))
xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"}))
- xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$B$2:$B$11"}))
+ if data_groupedby_column:
+ xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInColumns.$D$1:$D$11"}))
+ else:
+ xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$DataInRows.$A$4:$K$4"}))
+ # The data ranges have labels in them
+ if (get_state_as_dict(xwithlabelscheck)["Selected"]) == "false":
+ xwithlabelscheck.executeAction("CLICK", tuple())
+
+ xoutputrangeedit.executeAction("FOCUS", tuple()) # Without this the range parser does not kick in somehow
xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"}))
xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"}))
- xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"F1"}))
- xgroupedbycolumnsradio.executeAction("CLICK", tuple())
- if (get_state_as_dict(xlinearcheck)["Selected"]) == "false":
- xlinearcheck.executeAction("CLICK", tuple())
- if (get_state_as_dict(xlogarithmiccheck)["Selected"]) == "false":
- xlogarithmiccheck.executeAction("CLICK", tuple())
- if (get_state_as_dict(xpowercheck)["Selected"]) == "false":
- xpowercheck.executeAction("CLICK", tuple())
+ if regression_type == "LINEAR":
+ xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$ActualLinear.$A$1"}))
+ elif regression_type == "LOG":
+ xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$ActualLog.$A$1"}))
+ else:
+ xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$ActualPower.$A$1"}))
+
+ if data_groupedby_column:
+ xgroupedbycolumnsradio.executeAction("CLICK", tuple())
+ else:
+ xgroupedbyrowsradio.executeAction("CLICK", tuple())
+
+ if regression_type == "LINEAR":
+ xlinearradio.executeAction("CLICK", tuple())
+ elif regression_type == "LOG":
+ xlogarithmicradio.executeAction("CLICK", tuple())
+ else:
+ xpowerradio.executeAction("CLICK", tuple())
+
xOKBtn = xDialog.getChild("ok")
self.ui_test.close_dialog_through_button(xOKBtn)
- #Verify
- self.assertEqual(get_cell_by_position(document, 0, 5, 0).getString(), "Regression")
- self.assertEqual(get_cell_by_position(document, 0, 5, 2).getString(), "Regression Model")
- self.assertEqual(get_cell_by_position(document, 0, 5, 3).getString(), "R^2")
- self.assertEqual(get_cell_by_position(document, 0, 5, 4).getString(), "Standard Error")
- self.assertEqual(get_cell_by_position(document, 0, 5, 6).getString(), "Slope")
- self.assertEqual(get_cell_by_position(document, 0, 5, 7).getString(), "Intercept")
- self.assertEqual(get_cell_by_position(document, 0, 5, 9).getValue(), 1)
- self.assertEqual(get_cell_by_position(document, 0, 5, 10).getValue(), 2)
- self.assertEqual(get_cell_by_position(document, 0, 5, 11).getValue(), 3)
- self.assertEqual(get_cell_by_position(document, 0, 5, 12).getValue(), 4)
- self.assertEqual(get_cell_by_position(document, 0, 5, 13).getValue(), 5)
- self.assertEqual(get_cell_by_position(document, 0, 5, 14).getValue(), 6)
- self.assertEqual(get_cell_by_position(document, 0, 5, 15).getValue(), 7)
- self.assertEqual(get_cell_by_position(document, 0, 5, 16).getValue(), 8)
- self.assertEqual(get_cell_by_position(document, 0, 5, 17).getValue(), 9)
- self.assertEqual(get_cell_by_position(document, 0, 5, 18).getValue(), 10)
-
- self.assertEqual(get_cell_by_position(document, 0, 6, 2).getString(), "Linear")
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 3).getValue(),13), 0.1243901235130)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 4).getValue(),9), 1.869256861)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 6).getValue(),8), -0.21939394)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 7).getValue(),8), 4.86666667)
-
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 9).getValue(),12), 4.647272727273)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 10).getValue(),11), 4.42787878788)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 11).getValue(),11), 4.20848484848)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 12).getValue(),11), 3.98909090909)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 13).getValue(),12), 3.769696969697)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 14).getValue(),11), 3.55030303030)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 15).getValue(),11), 3.33090909091)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 16).getValue(),10), 3.1115151515)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 17).getValue(),11), 2.89212121212)
- self.assertEqual(round(get_cell_by_position(document, 0, 6, 18).getValue(),12), 2.672727272727)
-
- self.assertEqual(get_cell_by_position(document, 0, 7, 2).getString(), "Logarithmic")
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 3).getValue(),13), 0.0362835060138)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 4).getValue(),9), 1.961048360)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 6).getValue(),8), -0.48941120)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 7).getValue(),8), 4.39922687)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 9).getValue(),12), 4.399226869524)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 10).getValue(),11), 4.05999287553)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 11).getValue(),11), 3.86155371008)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 12).getValue(),11), 3.72075888154)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 13).getValue(),12), 3.611549928136)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 14).getValue(),11), 3.52231971609)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 15).getValue(),11), 3.44687664676)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 16).getValue(),10), 3.3815248876)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 17).getValue(),11), 3.32388055063)
- self.assertEqual(round(get_cell_by_position(document, 0, 7, 18).getValue(),12), 3.272315934145)
-
- self.assertEqual(get_cell_by_position(document, 0, 8, 2).getString(), "Power")
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 3).getValue(),13), 0.0884254697227)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 4).getValue(),9), 0.774632105)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 6).getValue(),8), -0.31030853)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 7).getValue(),8), 4.81267293)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 9).getValue(),12), 4.812672931007)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 10).getValue(),12), 3.881272835552)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 11).getValue(),11), 3.42240619237)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 12).getValue(),11), 3.13012727853)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 13).getValue(),11), 2.92072046513)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 14).getValue(),12), 2.760065430808)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 15).getValue(),10), 2.6311476385)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 16).getValue(),11), 2.52435146791)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 17).getValue(),10), 2.4337544465)
- self.assertEqual(round(get_cell_by_position(document, 0, 8, 18).getValue(),11), 2.35547130753)
-
- #undo
- self.xUITest.executeCommand(".uno:Undo")
- self.assertEqual(get_cell_by_position(document, 0, 5, 0).getString(), "")
-
- self.ui_test.close_doc()
-
-#doesn't work in test
-# def test_regression_row(self):
-# calc_doc = self.ui_test.create_doc_in_start_center("calc")
-# xCalcDoc = self.xUITest.getTopFocusWindow()
-# gridwin = xCalcDoc.getChild("grid_window")
-# document = self.ui_test.get_component()
-# #fill data
-# enter_text_to_cell(gridwin, "A1", "Time")
-# enter_text_to_cell(gridwin, "A2", "Measurement")
-# enter_text_to_cell(gridwin, "B1", "1")
-# enter_text_to_cell(gridwin, "B2", "2.7")
-# enter_text_to_cell(gridwin, "C1", "2")
-# enter_text_to_cell(gridwin, "C2", "4")
-# enter_text_to_cell(gridwin, "D1", "3")
-# enter_text_to_cell(gridwin, "D2", "4.4")
-# enter_text_to_cell(gridwin, "E1", "4")
-# enter_text_to_cell(gridwin, "E2", "7.1")
-# enter_text_to_cell(gridwin, "F1", "5")
-# enter_text_to_cell(gridwin, "F2", "4.9")
-# enter_text_to_cell(gridwin, "G1", "6")
-# enter_text_to_cell(gridwin, "G2", "3.6")
-# enter_text_to_cell(gridwin, "H1", "7")
-# enter_text_to_cell(gridwin, "H2", "4")
-# enter_text_to_cell(gridwin, "I1", "8")
-# enter_text_to_cell(gridwin, "I2", "0.6")
-# enter_text_to_cell(gridwin, "J1", "9")
-# enter_text_to_cell(gridwin, "J2", "1")
-# enter_text_to_cell(gridwin, "K1", "10")
-# enter_text_to_cell(gridwin, "K2", "4.3")
-
-## gridwin.executeAction("SELECT", mkPropertyValues({"RANGE": "B1:K1"}))
-# sleep(5)
-# self.ui_test.execute_modeless_dialog_through_command(".uno:RegressionDialog")
-# xDialog = self.xUITest.getTopFocusWindow()
-# xvariable1rangeedit = xDialog.getChild("variable1-range-edit")
-# xvariable2rangeedit = xDialog.getChild("variable2-range-edit")
-# xoutputrangeedit = xDialog.getChild("output-range-edit")
-# xgroupedbyrowsradio = xDialog.getChild("groupedby-rows-radio")
-# xgroupedbycolumnsradio = xDialog.getChild("groupedby-columns-radio")
-# xlinearcheck = xDialog.getChild("linear-check")
-# xlogarithmiccheck = xDialog.getChild("logarithmic-check")
-# xpowercheck = xDialog.getChild("power-check")
-# sleep(4)
-
-# xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"}))
-# xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"}))
-# xvariable1rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$B$1:$K$1"}))
-# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"}))
-# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"}))
-# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"TAB"}))
-# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"}))
-# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"}))
-# xvariable2rangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$Sheet1.$B$2:$K$2"}))
-# xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"CTRL+A"}))
-# xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"KEYCODE":"BACKSPACE"}))
-# xoutputrangeedit.executeAction("TYPE", mkPropertyValues({"TEXT":"$M$1"}))
-# xgroupedbyrowsradio.executeAction("CLICK", tuple())
-# if (get_state_as_dict(xlinearcheck)["Selected"]) == "false":
-# xlinearcheck.executeAction("CLICK", tuple())
-# if (get_state_as_dict(xlogarithmiccheck)["Selected"]) == "false":
-# xlogarithmiccheck.executeAction("CLICK", tuple())
-# if (get_state_as_dict(xpowercheck)["Selected"]) == "false":
-# xpowercheck.executeAction("CLICK", tuple())
-# xOKBtn = xDialog.getChild("ok")
-# sleep(5)
-# self.ui_test.close_dialog_through_button(xOKBtn)
-# sleep(6)
-# #Verify
-# self.assertEqual(get_cell_by_position(document, 0, 12, 0).getString(), "Regression")
-# self.assertEqual(get_cell_by_position(document, 0, 12, 2).getString(), "Regression Model")
-# self.assertEqual(get_cell_by_position(document, 0, 12, 3).getString(), "R^2")
-# self.assertEqual(get_cell_by_position(document, 0, 12, 4).getString(), "Standard Error")
-# self.assertEqual(get_cell_by_position(document, 0, 12, 6).getString(), "Slope")
-# self.assertEqual(get_cell_by_position(document, 0, 12, 7).getString(), "Intercept")
-# self.assertEqual(get_cell_by_position(document, 0, 12, 9).getValue(), 1)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 10).getValue(), 2)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 11).getValue(), 3)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 12).getValue(), 4)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 13).getValue(), 5)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 14).getValue(), 6)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 15).getValue(), 7)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 16).getValue(), 8)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 17).getValue(), 9)
-# self.assertEqual(get_cell_by_position(document, 0, 12, 18).getValue(), 10)
-
-# self.assertEqual(get_cell_by_position(document, 0, 13, 2).getString(), "Linear")
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 3).getValue(),13), 0.1243901235130)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 4).getValue(),9), 1.869256861)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 6).getValue(),8), -0.21939394)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 7).getValue(),8), 4.86666667)
-
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 9).getValue(),12), 4.647272727273)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 10).getValue(),11), 4.42787878788)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 11).getValue(),11), 4.20848484848)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 12).getValue(),11), 3.98909090909)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 13).getValue(),12), 3.769696969697)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 14).getValue(),11), 3.55030303030)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 15).getValue(),11), 3.33090909091)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 16).getValue(),10), 3.1115151515)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 17).getValue(),11), 2.89212121212)
-# self.assertEqual(round(get_cell_by_position(document, 0, 13, 18).getValue(),12), 2.672727272727)
-
-# self.assertEqual(get_cell_by_position(document, 0, 14, 2).getString(), "Logarithmic")
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 3).getValue(),13), 0.0362835060138)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 4).getValue(),9), 1.961048360)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 6).getValue(),8), -0.48941120)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 7).getValue(),8), 4.39922687)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 9).getValue(),12), 4.399226869524)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 10).getValue(),11), 4.05999287553)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 11).getValue(),11), 3.86155371008)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 12).getValue(),11), 3.72075888154)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 13).getValue(),12), 3.611549928136)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 14).getValue(),11), 3.52231971609)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 15).getValue(),11), 3.44687664676)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 16).getValue(),10), 3.3815248876)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 17).getValue(),11), 3.32388055063)
-# self.assertEqual(round(get_cell_by_position(document, 0, 14, 18).getValue(),12), 3.272315934145)
-
-# self.assertEqual(get_cell_by_position(document, 0, 15, 2).getString(), "Power")
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 3).getValue(),13), 0.0884254697227)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 4).getValue(),9), 0.774632105)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 6).getValue(),8), -0.31030853)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 7).getValue(),8), 4.81267293)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 9).getValue(),12), 4.812672931007)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 10).getValue(),12), 3.881272835552)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 11).getValue(),11), 3.42240619237)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 12).getValue(),11), 3.13012727853)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 13).getValue(),11), 2.92072046513)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 14).getValue(),12), 2.760065430808)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 15).getValue(),10), 2.6311476385)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 16).getValue(),11), 2.52435146791)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 17).getValue(),10), 2.4337544465)
-# self.assertEqual(round(get_cell_by_position(document, 0, 15, 18).getValue(),11), 2.35547130753)
-# #undo
-# self.xUITest.executeCommand(".uno:Undo")
-# self.assertEqual(get_cell_by_position(document, 0, 5, 0).getString(), "")
-# self.ui_test.close_doc()
-# vim: set shiftwidth=4 softtabstop=4 expandtab:
\ No newline at end of file
+# vim: set shiftwidth=4 softtabstop=4 expandtab:
diff --git a/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx
index a07e94286c58..f7bdbd3271ba 100644
--- a/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx
+++ b/sc/source/ui/StatisticsDialogs/RegressionDialog.cxx
@@ -8,13 +8,10 @@
*
*/
-#include <memory>
#include <sfx2/dispatch.hxx>
#include <svl/zforlist.hxx>
#include <svl/undo.hxx>
-#include <formulacell.hxx>
-#include <rangelst.hxx>
#include <scitems.hxx>
#include <docsh.hxx>
#include <document.hxx>
@@ -26,8 +23,90 @@
#include <scresid.hxx>
#include <strings.hrc>
+/*
+ Some regression basics
+ ----------------------
+
+ 1. Linear regression fits using data, a linear function between the dependent variable and the independent variable(s).
+ The basic form of this function is :-
+
+ y = b + m_1*x_1 + m_2*x_2 + ... + m_k*x_k
+
+ where y is the dependent variable
+ x_1, x_2, ..., x_k are the k independent variables
+ b is the intercept
+ m_1, m_2, ..., m_k are the slopes corresponding to the variables x_1, x_2, ..., x_k respectively.
+
+
+ This equation for n observations can be compactly written using matrices as :-
+
+ y = X*A
+
+ where y is the n dimensional column vector containing dependent variable observations.
+ where X is matrix of shape n*(k+1) where a row looks like [ 1 x_1 x_2 ... x_k ]
+ A is the k+1 dimensional column vector [ b m_1 m_2 ... m_k ]
+
+ Calc formula LINEST(Y_array ; X_array) can be used to compute all entries in "A" along with many other statistics.
+
+
+ 2. Logarithmic regression is basically used to find a linear function between the dependent variable and
+ the natural logarithm of the independent variable(s).
+ So the basic form of this functions is :-
+
+ y = b + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k)
+
+ This can be again written in a compact matrix form for n observations.
+
+ y = ln(X)*A
+
+ where y is the n dimensional column vector containing dependent variable observations.
+ where X is matrix of shape n*(k+1) where a row looks like [ e x_1 x_2 ... x_k ]
+ A is the k+1 dimensional column vector [ b m_1 m_2 ... m_k ]
+
+ To estimate A, we use the formula =LINEST(Y_array ; LN(X_array))
+
+
+ 3. Power regression is used to fit the following model :-
+
+ y = b * (x_1 ^ m_1) * (x_2 ^ m_2) * ... * (x_k ^ m_k)
+
+ To reduce this to a linear function(so that we can still use LINEST()), we take natural logarithm on both sides
+
+ ln(y) = c + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k) ; where c = ln(b)
+
+
+ This again can be written compactly in matrix form as :-
+
+ ln(y) = ln(X)*A
+
+ where y is the n dimensional column vector containing dependent variable observations.
+ where X is matrix of shape n*(k+1) where a row looks like [ e x_1 x_2 ... x_k ]
+ A is the k+1 dimensional column vector [ c m_1 m_2 ... m_k ]
+
+ To estimate A, we use the formula =LINEST(LN(Y_array) ; LN(X_array))
+
+ Once we get A, to get back y from x's we use the formula :-
+
+ y = exp( ln(X)*A )
+
+
+
+ Some references for computing confidence interval for the regression coefficients :-
+
+ [1] https://en.wikipedia.org/wiki/Student%27s_t-test#Slope_of_a_regression_line
+ [2] https://en.wikipedia.org/wiki/Simple_linear_regression#Normality_assumption
+ [3] https://onlinecourses.science.psu.edu/stat414/node/280
+
+ */
+
namespace
{
+ enum class ScRegType {
+ LINEAR,
+ LOGARITHMIC,
+ POWER
+ };
+
const char* constRegressionModel[] =
{
STR_LABEL_LINEAR,
@@ -35,53 +114,51 @@ namespace
STR_LABEL_POWER
};
- OUString constTemplateRSQUARED[] =
- {
- "=RSQ(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
- "=RSQ(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
- "=RSQ(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))"
- };
-
- OUString constTemplatesSTDERR[] =
- {
- "=STEYX(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
- "=STEYX(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
- "=STEYX(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))"
- };
-
- OUString constTemplatesSLOPE[] =
- {
- "=SLOPE(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
- "=SLOPE(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
- "=SLOPE(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%))"
- };
-
- OUString constTemplatesINTERCEPT[] =
+ OUString constTemplateLINEST[] =
{
- "=INTERCEPT(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE%)",
- "=INTERCEPT(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%))",
- "=EXP(INTERCEPT(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%)))"
+ "=LINEST(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE% ; TRUE ; TRUE)",
+ "=LINEST(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%) ; TRUE ; TRUE)",
+ "=LINEST(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%) ; TRUE ; TRUE)"
};
OUString constRegressionFormula[] =
{
- "=%A% * %ADDRESS% + %B%",
- "=%A% * LN(%ADDRESS%) + %B%",
- "=%B% * %ADDRESS% ^ %A%"
+ "=MMULT(%XDATAMATRIX_RANGE% ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%",
+ "=MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%",
+ "=EXP(MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%)"
};
} // end anonymous namespace
+static size_t lcl_GetNumRowsColsInRange(const ScRange& rRange, bool bRows)
+{
+ if (bRows)
+ return rRange.aEnd.Row() - rRange.aStart.Row() + 1;
+
+ return rRange.aEnd.Col() - rRange.aStart.Col() + 1;
+}
+
ScRegressionDialog::ScRegressionDialog(
SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow,
vcl::Window* pParent, ScViewData* pViewData ) :
ScStatisticsTwoVariableDialog(
pSfxBindings, pChildWindow, pParent, pViewData,
- "RegressionDialog", "modules/scalc/ui/regressiondialog.ui" )
+ "RegressionDialog", "modules/scalc/ui/regressiondialog.ui" ),
+ mbUnivariate(true),
+ mnNumIndependentVars(1),
+ mnNumObservations(0),
+ mbUse3DAddresses(false)
{
- get(mpLinearCheckBox, "linear-check");
- get(mpLogarithmicCheckBox, "logarithmic-check");
- get(mpPowerCheckBox, "power-check");
+ get(mpWithLabelsCheckBox, "withlabels-check");
+ get(mpLinearRadioButton, "linear-radio");
+ get(mpLogarithmicRadioButton, "logarithmic-radio");
+ get(mpPowerRadioButton, "power-radio");
+ get(mpConfidenceLevelField, "confidencelevel-spin");
+ get(mpCalcResidualsCheckBox, "calcresiduals-check");
+ get(mpErrorMessage, "error-message");
+ mpWithLabelsCheckBox->SetToggleHdl(LINK(this, ScRegressionDialog, CheckBoxHdl));
+ mpConfidenceLevelField->SetModifyHdl(LINK(this, ScRegressionDialog, NumericFieldHdl));
+ mpCalcResidualsCheckBox->SetToggleHdl(LINK(this, ScRegressionDialog, CheckBoxHdl));
}
ScRegressionDialog::~ScRegressionDialog()
@@ -96,9 +173,13 @@ bool ScRegressionDialog::Close()
void ScRegressionDialog::dispose()
{
- mpLinearCheckBox.disposeAndClear();
- mpLogarithmicCheckBox.disposeAndClear();
- mpPowerCheckBox.disposeAndClear();
+ mpWithLabelsCheckBox.disposeAndClear();
+ mpLinearRadioButton.disposeAndClear();
+ mpLogarithmicRadioButton.disposeAndClear();
+ mpPowerRadioButton.disposeAndClear();
+ mpConfidenceLevelField.disposeAndClear();
+ mpCalcResidualsCheckBox.disposeAndClear();
+ mpErrorMessage.disposeAndClear();
ScStatisticsTwoVariableDialog::dispose();
}
@@ -112,131 +193,509 @@ ScRange ScRegressionDialog::ApplyOutput(ScDocShell* pDocShell)
AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument,
formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv));
FormulaTemplate aTemplate(mDocument);
- aTemplate.autoReplaceUses3D(false);
+ aTemplate.autoReplaceUses3D(mbUse3DAddresses);
+
+ // max col of our output should account for
+ // 1. constant term column,
+ // 2. mnNumIndependentVars columns
+ // 3. Actual Y column
+ // 4. Predicted Y column
+ // 5. Residual Column
+ SCCOL nOutputMaxCol = mOutputAddress.Col() + mnNumIndependentVars + 3;
+
+ ScRange aXDataRange(GetDataRange(mVariable1Range));
+ ScRange aYDataRange(GetDataRange(mVariable2Range));
+
+ aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", aXDataRange);
+ aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", aYDataRange);
+ size_t nRegressionIndex = GetRegressionTypeIndex();
+ ScRegType eRegType = static_cast<ScRegType>(nRegressionIndex);
+ bool bTakeLogX = eRegType == ScRegType::LOGARITHMIC || eRegType == ScRegType::POWER;
+
+ WriteRawRegressionResults(aOutput, aTemplate, nRegressionIndex);
+ WriteRegressionStatistics(aOutput, aTemplate);
+ WriteRegressionANOVAResults(aOutput, aTemplate);
+ WriteRegressionEstimatesWithCI(aOutput, aTemplate, bTakeLogX);
+ if (mpCalcResidualsCheckBox->IsChecked())
+ WritePredictionsWithResiduals(aOutput, aTemplate, nRegressionIndex);
+
+ ScAddress aMaxAddress(aOutput.mMaximumAddress);
+ aMaxAddress.SetCol(std::max(aMaxAddress.Col(), nOutputMaxCol));
+ return ScRange(aOutput.mMinimumAddress, aMaxAddress);
+}
- std::unique_ptr<DataRangeIterator> pVariable1Iterator;
- if (mGroupedBy == BY_COLUMN)
- pVariable1Iterator.reset(new DataRangeByColumnIterator(mVariable1Range));
- else
- pVariable1Iterator.reset(new DataRangeByRowIterator(mVariable1Range));
+bool ScRegressionDialog::InputRangesValid()
+{
+ if (!mVariable1Range.IsValid())
+ {
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_XINVALID_RANGE));
+ return false;
+ }
- std::unique_ptr<DataRangeIterator> pVariable2Iterator;
- if (mGroupedBy == BY_COLUMN)
- pVariable2Iterator.reset(new DataRangeByColumnIterator(mVariable2Range));
- else
- pVariable2Iterator.reset(new DataRangeByRowIterator(mVariable2Range));
+ if (!mVariable2Range.IsValid())
+ {
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_YINVALID_RANGE));
+ return false;
+ }
- aTemplate.autoReplaceRange("%VARIABLE1_RANGE%", pVariable1Iterator->get());
- aTemplate.autoReplaceRange("%VARIABLE2_RANGE%", pVariable2Iterator->get());
+ if (!mOutputAddress.IsValid())
+ {
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_INVALID_OUTPUT_ADDR));
+ return false;
+ }
- aOutput.writeBoldString(ScResId(STR_REGRESSION));
- aOutput.newLine();
- aOutput.newLine();
- aOutput.push();
+ {
+ double fConfidenceLevel = mpConfidenceLevelField->GetValue();
+ if ( fConfidenceLevel <= 0.0 || fConfidenceLevel >= 100.0 )
+ {
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_INVALID_CONFIDENCE_LEVEL));
+ return false;
+ }
+ }
- // REGRESSION MODEL
- aOutput.writeString(ScResId(STR_LABEL_REGRESSION_MODEL));
- aOutput.nextRow();
+ mVariable1Range.PutInOrder();
+ mVariable2Range.PutInOrder();
- // RSQUARED
- aOutput.writeString(ScResId(STR_LABEL_RSQUARED));
- aOutput.nextRow();
+ bool bGroupedByColumn = mGroupedBy == BY_COLUMN;
- // Standard Error
- aOutput.writeString(ScResId(STRID_CALC_STD_ERROR));
- aOutput.nextRow();
+ bool bYHasSingleDim = (
+ (bGroupedByColumn &&
+ mVariable2Range.aStart.Col() == mVariable2Range.aEnd.Col()) ||
+ (!bGroupedByColumn &&
+ mVariable2Range.aStart.Row() == mVariable2Range.aEnd.Row()));
- aOutput.nextRow();
+ if (!bYHasSingleDim)
+ {
+ if (bGroupedByColumn)
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_YVARIABLE_MULTI_COLUMN));
+ else
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_YVARIABLE_MULTI_ROW));
+ return false;
+ }
- // Slope
- aOutput.writeString(ScResId(STR_LABEL_SLOPE));
- aOutput.nextRow();
+ bool bWithLabels = mpWithLabelsCheckBox->IsChecked();
- // Intercept
- aOutput.writeString(ScResId(STR_LABEL_INTERCEPT));
- aOutput.nextRow();
+ size_t nYObs = lcl_GetNumRowsColsInRange(mVariable2Range, bGroupedByColumn);
+ size_t nNumXVars = lcl_GetNumRowsColsInRange(mVariable1Range, !bGroupedByColumn);
+ mbUnivariate = nNumXVars == 1;
+ // Observation count mismatch check
+ if (lcl_GetNumRowsColsInRange(mVariable1Range, bGroupedByColumn) != nYObs)
+ {
+ if (mbUnivariate)
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH));
+ else
+ mpErrorMessage->SetText(ScResId(STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH));
+ return false;
+ }
+
+ mnNumIndependentVars = nNumXVars;
+ mnNumObservations = bWithLabels ? nYObs - 1 : nYObs;
+
+ mbUse3DAddresses = mVariable1Range.aStart.Tab() != mOutputAddress.Tab() ||
+ mVariable2Range.aStart.Tab() != mOutputAddress.Tab();
+
+ mpErrorMessage->SetText("");
- aOutput.nextRow();
+ return true;
+}
+
+size_t ScRegressionDialog::GetRegressionTypeIndex()
+{
+ if (mpLinearRadioButton->IsChecked())
+ return 0;
+ if (mpLogarithmicRadioButton->IsChecked())
+ return 1;
+ return 2;
+}
- size_t nVariable1Size = pVariable1Iterator->size();
+ScRange ScRegressionDialog::GetDataRange(const ScRange& rRange)
+{
+ if (!mpWithLabelsCheckBox->IsChecked())
+ return rRange;
- OUString sFormula;
+ ScRange aDataRange(rRange);
if (mGroupedBy == BY_COLUMN)
- sFormula = "=INDEX(%VARIABLE1_RANGE%; %VAR1_CELL_INDEX%; 1)";
+ aDataRange.aStart.IncRow(1);
else
- sFormula = "=INDEX(%VARIABLE1_RANGE%; 1; %VAR1_CELL_INDEX%)";
+ aDataRange.aStart.IncCol(1);
+
+ return aDataRange;
+}
+
+OUString ScRegressionDialog::GetVariableNameFormula(bool bXVar, size_t nIndex, bool bWithLog)
+{
+ if (bXVar && nIndex == 0)
+ return "=\"" + ScResId(STR_LABEL_INTERCEPT) + "\"";
- for (size_t i = 0; i < nVariable1Size; i++)
+ if (mpWithLabelsCheckBox->IsChecked())
{
- aTemplate.setTemplate(sFormula);
- aTemplate.applyNumber("%VAR1_CELL_INDEX%", i + 1);
- aOutput.writeFormula(aTemplate.getTemplate());
- aOutput.nextRow();
+ ScAddress aAddr(bXVar ? mVariable1Range.aStart : mVariable2Range.aStart);
+ if (mGroupedBy == BY_COLUMN)
+ aAddr.IncCol(nIndex - 1);
+ else
+ aAddr.IncRow(nIndex - 1);
+
+ ScRefFlags eAddrFlag = mbUse3DAddresses ? ScRefFlags::ADDR_ABS_3D : ScRefFlags::ADDR_ABS;
+ return bWithLog ? OUString("=CONCAT(\"LN(\";" +
+ aAddr.Format(eAddrFlag, mDocument, mDocument->GetAddressConvention()) + ";\")\")") :
+ OUString("=" + aAddr.Format(eAddrFlag, mDocument, mDocument->GetAddressConvention()));
}
- aOutput.reset();
+ OUString aDefaultVarName;
+
+ if (bXVar)
+ aDefaultVarName = "X" + OUString::number(nIndex);
+ else
+ aDefaultVarName = "Y";
+
+ return bWithLog ? OUString("=\"LN(" + aDefaultVarName + ")\"") :
+ OUString("=\"" + aDefaultVarName + "\"");
+}
+
+OUString ScRegressionDialog::GetXVariableNameFormula(size_t nIndex, bool bWithLog)
+{
+ assert(nIndex <= mnNumIndependentVars);
+ return GetVariableNameFormula(true, nIndex, bWithLog);
+}
- bool aEnabledRegressionTypes[3];
+OUString ScRegressionDialog::GetYVariableNameFormula(bool bWithLog)
+{
+ return GetVariableNameFormula(false, 1, bWithLog);
+}
- aEnabledRegressionTypes[0] = mpLinearCheckBox->IsChecked();
- aEnabledRegressionTypes[1] = mpLogarithmicCheckBox->IsChecked();
- aEnabledRegressionTypes[2] = mpPowerCheckBox->IsChecked();
+void ScRegressionDialog::WriteRawRegressionResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate,
+ size_t nRegressionIndex)
+{
+ rOutput.writeBoldString(ScResId(STR_REGRESSION));
+ rOutput.newLine();
+ // REGRESSION MODEL
+ rOutput.writeString(ScResId(STR_LABEL_REGRESSION_MODEL));
+ rOutput.nextColumn();
+ rOutput.writeString(ScResId(constRegressionModel[nRegressionIndex]));
+ rOutput.newLine();
+ rOutput.newLine();
+
+ rOutput.writeString(ScResId(STR_LINEST_RAW_OUTPUT_TITLE));
+ rOutput.newLine();
+ rOutput.push();
+ rTemplate.setTemplate(constTemplateLINEST[nRegressionIndex]);
+ rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1 + mnNumIndependentVars, 5);
+ // Add LINEST result components to template
+ // 1. Add ranges for coeffients and standard errors for indep. vars and the intercept.
+ // Note that these two are in the reverse order(m_n, m_n-1, ..., m_1, b) w.r.t what we expect.
+ rTemplate.autoReplaceRange("%COEFFICIENTS_REV_RANGE%", ScRange(rOutput.current(), rOutput.current(mnNumIndependentVars)));
+ rTemplate.autoReplaceRange("%SERRORSX_REV_RANGE%", ScRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars, 1)));
+
+ // 2. Add R-squared and standard error for y estimate.
+ rTemplate.autoReplaceAddress("%RSQUARED_ADDR%", rOutput.current(0, 2));
+ rTemplate.autoReplaceAddress("%SERRORY_ADDR%", rOutput.current(1, 2));
+
+ // 3. Add F statistic and degrees of freedom
+ rTemplate.autoReplaceAddress("%FSTATISTIC_ADDR%", rOutput.current(0, 3));
+ rTemplate.autoReplaceAddress("%DoFRESID_ADDR%", rOutput.current(1, 3));
+
+ // 4. Add regression sum of squares and residual sum of squares
+ rTemplate.autoReplaceAddress("%SSREG_ADDR%", rOutput.current(0, 4));
+ rTemplate.autoReplaceAddress("%SSRESID_ADDR%", rOutput.current(1, 4));
+
+ rOutput.push(0, 4);
+ rOutput.newLine();
+}
- sal_Int16 nColumn = 0;
+void ScRegressionDialog::WriteRegressionStatistics(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate)
+{
+ rOutput.newLine();
+ rOutput.writeString(ScResId(STR_LABEL_REGRESSION_STATISTICS));
+ rOutput.newLine();
- for (size_t nRegressionIndex = 0; nRegressionIndex < SAL_N_ELEMENTS(aEnabledRegressionTypes); ++nRegressionIndex)
+ const char* aMeasureNames[] =
{
- if (!aEnabledRegressionTypes[nRegressionIndex])
- continue;
+ STR_LABEL_RSQUARED,
+ STRID_CALC_STD_ERROR,
+ STR_LABEL_XVARIABLES_COUNT,
+ STR_OBSERVATIONS_LABEL,
+ STR_LABEL_ADJUSTED_RSQUARED
+ };
- aOutput.nextColumn();
- nColumn += 1;
+ OUString aMeasureFormulas[] =
+ {
+ "=%RSQUARED_ADDR%",
+ "=%SERRORY_ADDR%",
+ "=" + OUString::number(mnNumIndependentVars),
+ "=" + OUString::number(mnNumObservations),
+ "=1 - (1 - %RSQUARED_ADDR%)*(%NUMOBS_ADDR% - 1)/(%NUMOBS_ADDR% - %NUMXVARS_ADDR% - 1)"
+ };
+
+ rTemplate.autoReplaceAddress("%NUMXVARS_ADDR%", rOutput.current(1, 2));
+ rTemplate.autoReplaceAddress("%NUMOBS_ADDR%", rOutput.current(1, 3));
+
+ for (size_t nIdx = 0; nIdx < SAL_N_ELEMENTS(aMeasureNames); ++nIdx)
+ {
+ rOutput.writeString(ScResId(aMeasureNames[nIdx]));
+ rOutput.nextColumn();
+ rTemplate.setTemplate(aMeasureFormulas[nIdx]);
+ rOutput.writeFormula(rTemplate.getTemplate());
+ rOutput.newLine();
+ }
+}
+
+void ScRegressionDialog::WriteRegressionANOVAResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate)
+{
+ rOutput.newLine();
+ rOutput.writeString(ScResId(STR_LABEL_ANOVA));
+ rOutput.newLine();
- // REGRESSION MODEL
- aOutput.writeString(ScResId(constRegressionModel[nRegressionIndex]));
- aOutput.nextRow();
+ const size_t nColsInTable = 6;
+ const size_t nRowsInTable = 4;
+ OUString aTable[nRowsInTable][nColsInTable] =
+ {
+ {
+ "",
+ ScResId(STR_ANOVA_LABEL_DF),
+ ScResId(STR_ANOVA_LABEL_SS),
+ ScResId(STR_ANOVA_LABEL_MS),
+ ScResId(STR_ANOVA_LABEL_F),
+ ScResId(STR_ANOVA_LABEL_SIGNIFICANCE_F)
+ },
+ {
+ ScResId(STR_REGRESSION),
+ "=%NUMXVARS_ADDR%",
+ "=%SSREG_ADDR%",
+ "=%SSREG_ADDR% / %DoFREG_ADDR%",
+ "=%FSTATISTIC_ADDR%",
+ "=FDIST(%FSTATISTIC_ADDR% ; %DoFREG_ADDR% ; %DoFRESID_ADDR%)"
+ },
+ {
+ ScResId(STR_LABEL_RESIDUAL),
+ "=%DoFRESID_ADDR%",
+ "=%SSRESID_ADDR%",
+ "=%SSRESID_ADDR% / %DoFRESID_ADDR%",
+ "",
+ ""
+ },
+ {
+ ScResId(STR_ANOVA_LABEL_TOTAL),
+ "=%DoFREG_ADDR% + %DoFRESID_ADDR%",
+ "=%SSREG_ADDR% + %SSRESID_ADDR%",
+ "",
+ "",
+ ""
+ }
+ };
+
+ rTemplate.autoReplaceAddress("%DoFREG_ADDR%", rOutput.current(1, 1));
+
+ // Cell getter lambda
+ std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString&
+ {
+ return aTable[nRowIdx][nColIdx];
+ };
- // RSQUARED
- aTemplate.setTemplate(constTemplateRSQUARED[nRegressionIndex]);
- aOutput.writeMatrixFormula(aTemplate.getTemplate());
- aTemplate.autoReplaceAddress("%RSQUARED%", aOutput.current());
- aOutput.nextRow();
+ // Cell writer lambda
+ std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate]
+ (const OUString& rContent, size_t /*nRowIdx*/, size_t /*nColIdx*/)
+ {
+ if (!rContent.isEmpty())
+ {
+ if (rContent.startsWith("="))
+ {
+ rTemplate.setTemplate(rContent);
+ rOutput.writeFormula(rTemplate.getTemplate());
+ }
+ else
+ rOutput.writeString(rContent);
+ }
+ };
- // Standard Error
- aTemplate.setTemplate(constTemplatesSTDERR[nRegressionIndex]);
- aOutput.writeMatrixFormula(aTemplate.getTemplate());
- aTemplate.autoReplaceAddress("%STD_ERROR%", aOutput.current());
- aOutput.nextRow();
+ WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc);
- aOutput.nextRow();
+ // User given confidence level
+ rOutput.newLine();
+ rOutput.writeString(ScResId(STR_LABEL_CONFIDENCE_LEVEL));
+ rOutput.nextColumn();
+ rOutput.writeString(OUString::number(mpConfidenceLevelField->GetValue() / 100.0));
+ rTemplate.autoReplaceAddress("%CONFIDENCE_LEVEL_ADDR%", rOutput.current());
+ rOutput.newLine();
+}
- // Slope
- aTemplate.setTemplate(constTemplatesSLOPE[nRegressionIndex]);
- aOutput.writeMatrixFormula(aTemplate.getTemplate());
- aTemplate.autoReplaceAddress("%A%", aOutput.current());
- aOutput.nextRow();
+// Write slopes, intercept, their standard errors, t-statistics, p-value, confidence intervals
+void ScRegressionDialog::WriteRegressionEstimatesWithCI(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate,
+ bool bTakeLogX)
+{
+ rOutput.newLine();
+ SCROW nLastRow = rOutput.current(0, 1 + mnNumIndependentVars).Row();
+
+ // Coefficients & Std.Errors ranges (column vectors) in this table (yet to populate).
+ rTemplate.autoReplaceRange("%COEFFICIENTS_RANGE%",
+ ScRange(rOutput.current(1, 1),
+ rOutput.current(1, 1 + mnNumIndependentVars)));
+ rTemplate.autoReplaceRange("%SLOPES_RANGE%", // Excludes the intercept
+ ScRange(rOutput.current(1, 2),
+ rOutput.current(1, 1 + mnNumIndependentVars)));
+ rTemplate.autoReplaceAddress("%INTERCEPT_ADDR%", rOutput.current(1, 1));
+ rTemplate.autoReplaceRange("%SERRORSX_RANGE%",
+ ScRange(rOutput.current(2, 1),
+ rOutput.current(2, 1 + mnNumIndependentVars)));
+ // t-Statistics range in this table (yet to populate)
+ rTemplate.autoReplaceRange("%TSTAT_RANGE%",
+ ScRange(rOutput.current(3, 1),
+ rOutput.current(3, 1 + mnNumIndependentVars)));
+
+ const size_t nColsInTable = 7;
+ const size_t nRowsInTable = 2;
+ OUString aTable[nRowsInTable][nColsInTable] =
+ {
+ {
+ "",
+ ScResId(STR_LABEL_COEFFICIENTS),
+ ScResId(STRID_CALC_STD_ERROR),
+ ScResId(STR_LABEL_TSTATISTIC),
+ ScResId(STR_P_VALUE_LABEL),
- // Intercept
- aTemplate.setTemplate(constTemplatesINTERCEPT[nRegressionIndex]);
- aOutput.writeMatrixFormula(aTemplate.getTemplate());
- aTemplate.autoReplaceAddress("%B%", aOutput.current());
- aOutput.nextRow();
+ "=CONCAT(\"" + ScResId(STR_LABEL_LOWER) +
+ " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")",
- aOutput.nextRow();
+ "=CONCAT(\"" + ScResId(STR_LABEL_UPPER) +
+ " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")",
+ },
- for (size_t i = 0; i < nVariable1Size; i++)
+ // Following are matrix formulas of size numcols = 1, numrows = (mnNumIndependentVars + 1)
{
- aTemplate.setTemplate(constRegressionFormula[nRegressionIndex]);
- aTemplate.applyAddress("%ADDRESS%", aOutput.current(-nColumn), false);
- aOutput.writeFormula(aTemplate.getTemplate());
+ "",
+ // This puts the coefficients in the reverse order compared to that in LINEST output.
+ "=INDEX(%COEFFICIENTS_REV_RANGE%; 1 ; " + OUString::number(nLastRow + 2) + " - ROW())",
+ // This puts the standard errors in the reverse order compared to that in LINEST output.
+ "=INDEX(%SERRORSX_REV_RANGE%; 1 ; " + OUString::number(nLastRow + 2) + " - ROW())",
+ // t-Statistic
+ "=%COEFFICIENTS_RANGE% / %SERRORSX_RANGE%",
+ // p-Value
+ "=TDIST(ABS(%TSTAT_RANGE%) ; %DoFRESID_ADDR% ; 2 )",
+ // Lower limit of confidence interval
+ "=%COEFFICIENTS_RANGE% - %SERRORSX_RANGE% * "
+ "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)",
+ // Upper limit of confidence interval
+ "=%COEFFICIENTS_RANGE% + %SERRORSX_RANGE% * "
+ "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)"
+ }
+ };
- aOutput.nextRow();
+ // Cell getter lambda
+ std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString&
+ {
+ return aTable[nRowIdx][nColIdx];
+ };
+
+ // Cell writer lambda
+ size_t nNumIndependentVars = mnNumIndependentVars;
+ std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate, nNumIndependentVars]
+ (const OUString& rContent, size_t nRowIdx, size_t /*nColIdx*/)
+ {
+ if (!rContent.isEmpty())
+ {
+ if (rContent.startsWith("="))
+ {
+ rTemplate.setTemplate(rContent);
+ if (nRowIdx == 0)
+ rOutput.writeFormula(rTemplate.getTemplate());
+ else
+ rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, 1 + nNumIndependentVars);
+ }
+ else
+ rOutput.writeString(rContent);
}
+ };
+
+ WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc);
+
+ // Go back to the second row and first column of the table to
+ // fill the names of variables + intercept
+ rOutput.push(0, -1);
- aOutput.resetRow();
+ for (size_t nXvarIdx = 0; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx)
+ {
+ rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, bTakeLogX));
+ rOutput.newLine();
}
- return ScRange(aOutput.mMinimumAddress, aOutput.mMaximumAddress);
+}
+
+// Re-write all observations in group-by column mode with predictions and residuals
+void ScRegressionDialog::WritePredictionsWithResiduals(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate,
+ size_t nRegressionIndex)
+{
+ bool bGroupedByColumn = mGroupedBy == BY_COLUMN;
+ rOutput.newLine();
+ rOutput.push();
+
+ // Range of X variables with rows as observations and columns as variables.
+ ScRange aDataMatrixRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars - 1, mnNumObservations));
+ rTemplate.autoReplaceRange("%XDATAMATRIX_RANGE%", aDataMatrixRange);
+
+ // Write X variable names
+ for (size_t nXvarIdx = 1; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx)
+ {
+ // Here we write the X variables without any transformation(LN)
+ rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, false));
+ rOutput.nextColumn();
+ }
+ rOutput.reset();
+
+ // Write the X data matrix
+ rOutput.nextRow();
+ OUString aDataMatrixFormula = bGroupedByColumn ? OUString("=%VARIABLE1_RANGE%") : OUString("=TRANSPOSE(%VARIABLE1_RANGE%)");
+ rTemplate.setTemplate(aDataMatrixFormula);
+ rOutput.writeMatrixFormula(rTemplate.getTemplate(), mnNumIndependentVars, mnNumObservations);
+
+ // Write predicted values
+ rOutput.push(mnNumIndependentVars, -1);
+ rOutput.writeString(ScResId(STR_LABEL_PREDICTEDY));
+ rOutput.nextRow();
+ rTemplate.setTemplate(constRegressionFormula[nRegressionIndex]);
+ rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations);
+ rTemplate.autoReplaceRange("%PREDICTEDY_RANGE%", ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1)));
+
+ // Write actual Y
+ rOutput.push(1, -1);
+ rOutput.writeFormula(GetYVariableNameFormula(false));
+ rOutput.nextRow();
+ OUString aYVectorFormula = bGroupedByColumn ? OUString("=%VARIABLE2_RANGE%") : OUString("=TRANSPOSE(%VARIABLE2_RANGE%)");
+ rTemplate.setTemplate(aYVectorFormula);
+ rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations);
+ rTemplate.autoReplaceRange("%ACTUALY_RANGE%", ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1)));
+
+ // Write residual
+ rOutput.push(1, -1);
+ rOutput.writeString(ScResId(STR_LABEL_RESIDUAL));
+ rOutput.nextRow();
+ rTemplate.setTemplate("=%ACTUALY_RANGE% - %PREDICTEDY_RANGE%");
+ rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations);
+}
+
+// Generic table writer
+void ScRegressionDialog::WriteTable(std::function<CellValueGetter>& rCellGetter,
+ size_t nRowsInTable, size_t nColsInTable,
+ AddressWalkerWriter& rOutput,
+ std::function<CellWriter>& rFunc)
+{
+ for (size_t nRowIdx = 0; nRowIdx < nRowsInTable; ++nRowIdx)
+ {
+ for (size_t nColIdx = 0; nColIdx < nColsInTable; ++nColIdx)
+ {
+ rFunc(rCellGetter(nRowIdx, nColIdx), nRowIdx, nColIdx);
+ rOutput.nextColumn();
+ }
+ rOutput.newLine();
+ }
+}
+
+IMPL_LINK_NOARG(ScRegressionDialog, CheckBoxHdl, CheckBox&, void)
+{
+ ValidateDialogInput();
+}
+
+IMPL_LINK_NOARG(ScRegressionDialog, NumericFieldHdl, Edit&, void)
+{
+ ValidateDialogInput();
}
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx b/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx
index 0fa8c96f0333..85fff5b57ad2 100644
--- a/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx
+++ b/sc/source/ui/StatisticsDialogs/StatisticsTwoVariableDialog.cxx
@@ -199,11 +199,7 @@ void ScStatisticsTwoVariableDialog::SetReference( const ScRange& rReferenceRange
}
}
- // Enable OK if all ranges are set.
- if (mVariable1Range.IsValid() && mVariable2Range.IsValid() && mOutputAddress.IsValid())
- mpButtonOk->Enable();
- else
- mpButtonOk->Disable();
+ ValidateDialogInput();
}
IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, OkClicked, Button*, void )
@@ -246,6 +242,8 @@ IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, GroupByChanged, RadioButton&, vo
mGroupedBy = BY_COLUMN;
else if (mpGroupByRowsRadio->IsChecked())
mGroupedBy = BY_ROW;
+
+ ValidateDialogInput();
}
IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, RefInputModifyHandler, Edit&, void )
@@ -313,11 +311,7 @@ IMPL_LINK_NOARG( ScStatisticsTwoVariableDialog, RefInputModifyHandler, Edit&, vo
}
}
- // Enable OK if all ranges are set.
- if (mVariable1Range.IsValid() && mVariable2Range.IsValid() && mOutputAddress.IsValid())
- mpButtonOk->Enable();
- else
- mpButtonOk->Disable();
+ ValidateDialogInput();
}
void ScStatisticsTwoVariableDialog::CalculateInputAndWriteToOutput()
@@ -333,4 +327,18 @@ void ScStatisticsTwoVariableDialog::CalculateInputAndWriteToOutput()
pDocShell->PostPaint( aOutputRange, PaintPartFlags::Grid );
}
+bool ScStatisticsTwoVariableDialog::InputRangesValid()
+{
+ return mVariable1Range.IsValid() && mVariable2Range.IsValid() && mOutputAddress.IsValid();
+}
+
+void ScStatisticsTwoVariableDialog::ValidateDialogInput()
+{
+ // Enable OK button if all inputs are ok.
+ if (InputRangesValid())
+ mpButtonOk->Enable();
+ else
+ mpButtonOk->Disable();
+}
+
/* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx b/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx
index 5200e541a8f5..16fc8f3c84f7 100644
--- a/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx
+++ b/sc/source/ui/StatisticsDialogs/TableFillingAndNavigationTools.cxx
@@ -162,11 +162,15 @@ void AddressWalkerWriter::writeFormula(const OUString& aFormula)
new ScFormulaCell(mpDocument, mCurrentAddress, aFormula, meGrammar), true);
}
-void AddressWalkerWriter::writeMatrixFormula(const OUString& aFormula)
+void AddressWalkerWriter::writeMatrixFormula(const OUString& aFormula, SCCOL nCols, SCROW nRows)
{
ScRange aRange;
aRange.aStart = mCurrentAddress;
aRange.aEnd = mCurrentAddress;
+ if (nCols > 1)
+ aRange.aEnd.IncCol(nCols - 1);
+ if (nRows > 1)
+ aRange.aEnd.IncRow(nRows - 1);
mpDocShell->GetDocFunc().EnterMatrix(aRange, nullptr, nullptr, aFormula, false, false, OUString(), meGrammar );
}
diff --git a/sc/source/ui/inc/RegressionDialog.hxx b/sc/source/ui/inc/RegressionDialog.hxx
index 254562e93dbc..722fece34279 100644
--- a/sc/source/ui/inc/RegressionDialog.hxx
+++ b/sc/source/ui/inc/RegressionDialog.hxx
@@ -15,9 +15,18 @@
class ScRegressionDialog : public ScStatisticsTwoVariableDialog
{
- VclPtr<CheckBox> mpLinearCheckBox;
- VclPtr<CheckBox> mpLogarithmicCheckBox;
- VclPtr<CheckBox> mpPowerCheckBox;
+ VclPtr<CheckBox> mpWithLabelsCheckBox;
+ VclPtr<RadioButton> mpLinearRadioButton;
+ VclPtr<RadioButton> mpLogarithmicRadioButton;
+ VclPtr<RadioButton> mpPowerRadioButton;
+ VclPtr<FixedText> mpErrorMessage;
+ VclPtr<NumericField> mpConfidenceLevelField;
+ VclPtr<CheckBox> mpCalcResidualsCheckBox;
+
+ bool mbUnivariate;
+ size_t mnNumIndependentVars;
+ size_t mnNumObservations;
+ bool mbUse3DAddresses;
public:
ScRegressionDialog(
@@ -32,6 +41,40 @@ protected:
void dispose() override;
virtual const char* GetUndoNameId() override;
virtual ScRange ApplyOutput(ScDocShell* pDocShell) override;
+ virtual bool InputRangesValid() override;
+
+private:
+
+ using CellValueGetter = const OUString&(size_t, size_t);
+ using CellWriter = void(const OUString&, size_t, size_t);
+
+ size_t GetRegressionTypeIndex();
+ ScRange GetDataRange(const ScRange& rRange);
+ OUString GetVariableNameFormula(bool bXVar, size_t nIndex, bool bWithLog);
+ OUString GetXVariableNameFormula(size_t nIndex, bool bWithLog);
+ OUString GetYVariableNameFormula(bool bWithLog);
+
+ // Helper methods for writing different parts of regression results.
+ void WriteRawRegressionResults(AddressWalkerWriter& rOutput,
+ FormulaTemplate& rTemplate,
+ size_t nRegressionIndex);
+ void WriteRegressionStatistics(AddressWalkerWriter& rOutput,
+ FormulaTemplate& rTemplate);
+ void WriteRegressionANOVAResults(AddressWalkerWriter& rOutput,
+ FormulaTemplate& rTemplate);
+ void WriteRegressionEstimatesWithCI(AddressWalkerWriter& rOutput,
+ FormulaTemplate& rTemplate,
+ bool bTakeLogX);
+ void WritePredictionsWithResiduals(AddressWalkerWriter& rOutput,
+ FormulaTemplate& rTemplate,
+ size_t nRegressionIndex);
+ // Generic table writer
+ void WriteTable(std::function<CellValueGetter>& rCellGetter, size_t nRowsInTable,
+ size_t nColsInTable, AddressWalkerWriter& rOutput,
+ std::function<CellWriter>& rFunc);
+
+ DECL_LINK( CheckBoxHdl, CheckBox&, void );
+ DECL_LINK( NumericFieldHdl, Edit&, void );
};
diff --git a/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx b/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx
index bcfdcb319940..ceb14e4f4e79 100644
--- a/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx
+++ b/sc/source/ui/inc/StatisticsTwoVariableDialog.hxx
@@ -43,6 +43,8 @@ protected:
virtual ScRange ApplyOutput(ScDocShell* pDocShell) = 0;
virtual const char* GetUndoNameId() = 0;
+ virtual bool InputRangesValid();
+ virtual void ValidateDialogInput();
// Widgets
VclPtr<FixedText> mpVariable1RangeLabel;
diff --git a/sc/source/ui/inc/TableFillingAndNavigationTools.hxx b/sc/source/ui/inc/TableFillingAndNavigationTools.hxx
index 9c00bf31e351..073c89d98b0d 100644
--- a/sc/source/ui/inc/TableFillingAndNavigationTools.hxx
+++ b/sc/source/ui/inc/TableFillingAndNavigationTools.hxx
@@ -85,7 +85,7 @@ public:
formula::FormulaGrammar::Grammar eGrammar );
void writeFormula(const OUString& aFormula);
- void writeMatrixFormula(const OUString& aFormula);
+ void writeMatrixFormula(const OUString& aFormula, SCCOL nCols = 1, SCROW nRows = 1);
void writeString(const OUString& aString);
void writeString(const char* aCharArray);
void writeBoldString(const OUString& aString);
diff --git a/sc/uiconfig/scalc/ui/regressiondialog.ui b/sc/uiconfig/scalc/ui/regressiondialog.ui
index 9db73773cf24..4ef37adfafdd 100644
--- a/sc/uiconfig/scalc/ui/regressiondialog.ui
+++ b/sc/uiconfig/scalc/ui/regressiondialog.ui
@@ -1,5 +1,5 @@
<?xml version="1.0" encoding="UTF-8"?>
-<!-- Generated with glade 3.18.3 -->
+<!-- Generated with glade 3.22.1 -->
<interface domain="sc">
<requires lib="gtk+" version="3.18"/>
<requires lib="LibreOffice" version="1.0"/>
@@ -8,6 +8,9 @@
<property name="border_width">6</property>
<property name="title" translatable="yes" context="regressiondialog|RegressionDialog">Regression</property>
<property name="type_hint">dialog</property>
+ <child>
+ <placeholder/>
+ </child>
<child internal-child="vbox">
<object class="GtkBox" id="dialog-vbox1">
<property name="can_focus">False</property>
@@ -96,7 +99,7 @@
<object class="GtkLabel" id="variable1-range-label">
<property name="visible">True</property>
<property name="can_focus">False</property>
- <property name="label" translatable="yes" context="regressiondialog|variable1-range-label">Variable 1 range:</property>
+ <property name="label" translatable="yes" context="regressiondialog|variable1-range-label">Independent variable(s) (X) range:</property>
<property name="use_underline">True</property>
<property name="mnemonic_widget">variable1-range-edit</property>
<property name="xalign">0</property>
@@ -134,7 +137,7 @@
<object class="GtkLabel" id="variable2-range-label">
<property name="visible">True</property>
<property name="can_focus">False</property>
- <property name="label" translatable="yes" context="regressiondialog|variable2-range-label">Variable 2 range:</property>
+ <property name="label" translatable="yes" context="regressiondialog|variable2-range-label">Dependent variable (Y) range:</property>
<property name="use_underline">True</property>
<property name="mnemonic_widget">variable2-range-edit</property>
<property name="xalign">0</property>
@@ -169,6 +172,20 @@
</packing>
</child>
<child>
+ <object class="GtkCheckButton" id="withlabels-check">
+ <property name="label" translatable="yes" context="regressiondialog|withlabels-check">Both X and Y ranges have labels</property>
+ <property name="visible">True</property>
+ <property name="can_focus">True</property>
+ <property name="receives_default">False</property>
+ <property name="halign">start</property>
+ <property name="draw_indicator">True</property>
+ </object>
+ <packing>
+ <property name="left_attach">0</property>
+ <property name="top_attach">2</property>
+ </packing>
+ </child>
+ <child>
<object class="GtkLabel" id="output-range-label">
<property name="visible">True</property>
<property name="can_focus">False</property>
@@ -179,7 +196,7 @@
</object>
<packing>
<property name="left_attach">0</property>
- <property name="top_attach">2</property>
+ <property name="top_attach">3</property>
</packing>
</child>
<child>
@@ -192,7 +209,7 @@
</object>
<packing>
<property name="left_attach">1</property>
- <property name="top_attach">2</property>
+ <property name="top_attach">3</property>
</packing>
</child>
<child>
@@ -203,9 +220,15 @@
</object>
<packing>
<property name="left_attach">2</property>
- <property name="top_attach">2</property>
+ <property name="top_attach">3</property>
</packing>
</child>
+ <child>
+ <placeholder/>
+ </child>
+ <child>
+ <placeholder/>
+ </child>
</object>
</child>
</object>
@@ -321,13 +344,14 @@
<property name="row_spacing">6</property>
<property name="column_spacing">12</property>
<child>
- <object class="GtkCheckButton" id="linear-check">
- <property name="label" translatable="yes" context="regressiondialog|linear-check">Linear Regression</property>
+ <object class="GtkRadioButton" id="linear-radio">
+ <property name="label" translatable="yes" context="regressiondialog|linear-radio">Linear Regression</property>
<property name="visible">True</property>
<property name="can_focus">True</property>
<property name="receives_default">False</property>
+ <property name="halign">start</property>
+ <property name="valign">center</property>
<property name="use_underline">True</property>
- <property name="xalign">0</property>
<property name="active">True</property>
<property name="draw_indicator">True</property>
</object>
@@ -337,14 +361,16 @@
</packing>
</child>
<child>
- <object class="GtkCheckButton" id="logarithmic-check">
- <property name="label" translatable="yes" context="regressiondialog|logarithmic-check">Logarithmic Regression</property>
+ <object class="GtkRadioButton" id="logarithmic-radio">
+ <property name="label" translatable="yes" context="regressiondialog|logarithmic-radio">Logarithmic Regression</property>
<property name="visible">True</property>
<property name="can_focus">True</property>
<property name="receives_default">False</property>
+ <property name="halign">start</property>
+ <property name="valign">center</property>
<property name="use_underline">True</property>
- <property name="xalign">0</property>
<property name="draw_indicator">True</property>
+ <property name="group">linear-radio</property>
</object>
<packing>
<property name="left_attach">0</property>
@@ -352,14 +378,16 @@
</packing>
</child>
<child>
- <object class="GtkCheckButton" id="power-check">
- <property name="label" translatable="yes" context="regressiondialog|power-check">Power Regression</property>
+ <object class="GtkRadioButton" id="power-radio">
+ <property name="label" translatable="yes" context="regressiondialog|power-radio">Power Regression</property>
<property name="visible">True</property>
<property name="can_focus">True</property>
<property name="receives_default">False</property>
+ <property name="halign">start</property>
+ <property name="valign">center</property>
<property name="use_underline">True</property>
- <property name="xalign">0</property>
<property name="draw_indicator">True</property>
+ <property name="group">linear-radio</property>
</object>
<packing>
<property name="left_attach">0</property>
@@ -387,6 +415,104 @@
<property name="position">2</property>
</packing>
</child>
+ <child>
+ <object class="GtkFrame" id="frame-options">
+ <property name="visible">True</property>
+ <property name="can_focus">False</property>
+ <property name="label_xalign">0</property>
+ <property name="shadow_type">none</property>
+ <child>
+ <object class="GtkAlignment" id="alignment6">
+ <property name="visible">True</property>
+ <property name="can_focus">False</property>
+ <property name="left_padding">12</property>
+ <child>
+ <object class="GtkGrid" id="grid5">
+ <property name="visible">True</property>
+ <property name="can_focus">False</property>
+ <property name="row_spacing">6</property>
+ <property name="column_spacing">12</property>
+ <child>
+ <object class="GtkLabel" id="label5">
+ <property name="visible">True</property>
+ <property name="can_focus">False</property>
+ <property name="halign">start</property>
+ <property name="valign">center</property>
+ <property name="label" translatable="yes" context="regressiondialog|label5">Confidence level</property>
+ <property name="mnemonic_widget">confidencelevel-spin</property>
+ </object>
+ <packing>
+ <property name="left_attach">0</property>
+ <property name="top_attach">0</property>
+ </packing>
+ </child>
+ <child>
+ <object class="GtkCheckButton" id="calcresiduals-check">
+ <property name="label" translatable="yes" context="regressiondialog|calcresiduals-check">Calculate residuals</property>
+ <property name="visible">True</property>
+ <property name="can_focus">True</property>
+ <property name="receives_default">False</property>
+ <property name="active">True</property>
+ <property name="draw_indicator">True</property>
+ </object>
+ <packing>
+ <property name="left_attach">0</property>
+ <property name="top_attach">1</property>
+ </packing>
+ </child>
+ <child>
+ <object class="GtkSpinButton" id="confidencelevel-spin">
+ <property name="visible">True</property>
+ <property name="can_focus">True</property>
+ <property name="halign">start</property>
+ <property name="valign">center</property>
+ <property name="text" translatable="yes" context="regressiondialog|confidencelevel-spin">0.95</property>
+ <property name="digits">2</property>
+ <property name="numeric">True</property>
+ <property name="value">0.94999999999999996</property>
+ </object>
+ <packing>
+ <property name="left_attach">1</property>
+ <property name="top_attach">0</property>
+ </packing>
+ </child>
+ <child>
+ <placeholder/>
+ </child>
+ </object>
+ </child>
+ </object>
+ </child>
+ <child type="label">
+ <object class="GtkLabel" id="label4">
+ <property name="visible">True</property>
+ <property name="can_focus">False</property>
+ <property name="label" translatable="yes" context="regressiondialog|label4">Options</property>
+ <attributes>
+ <attribute name="weight" value="bold"/>
+ </attributes>
+ </object>
+ </child>
+ </object>
+ <packing>
+ <property name="expand">False</property>
+ <property name="fill">True</property>
+ <property name="position">3</property>
+ </packing>
+ </child>
+ <child>
+ <object class="GtkLabel" id="error-message">
+ <property name="visible">True</property>
+ <property name="can_focus">False</property>
+ <property name="wrap">True</property>
+ <property name="track_visited_links">False</property>
+ </object>
+ <packing>
+ <property name="expand">True</property>
+ <property name="fill">True</property>
+ <property name="position">4</property>
+ </packing>
+ </child>
</object>
</child>
<action-widgets>
More information about the Libreoffice-commits
mailing list