sc/source/ui/StatisticsDialogs/RegressionDialog.cxx

   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 <sal/config.h>
  12
  13 #include <string_view>
  14
  15 #include <document.hxx>
  16 #include <reffact.hxx>
  17 #include <TableFillingAndNavigationTools.hxx>
  18 #include <RegressionDialog.hxx>
  19 #include <scresid.hxx>
  20 #include <strings.hrc>
  21
  22 /*
  23    Some regression basics
  24    ----------------------
  25
  26    1. Linear regression fits using data, a linear function between the dependent variable and the independent variable(s).
  27       The basic form of this function is :-
  28
  29       y = b + m_1*x_1 + m_2*x_2 + ... + m_k*x_k
  30
  31       where y is the dependent variable
  32             x_1, x_2, ..., x_k are the k independent variables
  33             b is the intercept
  34             m_1, m_2, ..., m_k are the slopes corresponding to the variables x_1, x_2, ..., x_k respectively.
  35
  36
  37      This equation for n observations can be compactly written using matrices as :-
  38
  39      y = X*A
  40
  41      where y is the n dimensional column vector containing dependent variable observations.
  42      where X is matrix of shape n*(k+1) where a row looks like [ 1  x_1  x_2 ...  x_k ]
  43            A is the k+1 dimensional column vector              [ b  m_1  m_2 ...  m_k ]
  44
  45      Calc formula LINEST(Y_array ; X_array) can be used to compute all entries in "A" along with many other statistics.
  46
  47
  48   2. Logarithmic regression is basically used to find a linear function between the dependent variable and
  49      the natural logarithm of the independent variable(s).
  50      So the basic form of this functions is :-
  51
  52      y = b + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k)
  53
  54      This can be again written in a compact matrix form for n observations.
  55
  56      y = ln(X)*A
  57
  58      where y is the n dimensional column vector containing dependent variable observations.
  59      where X is matrix of shape n*(k+1) where a row looks like  [ e  x_1  x_2 ...  x_k ]
  60            A is the k+1 dimensional column vector               [ b  m_1  m_2 ...  m_k ]
  61
  62      To estimate A, we use the formula =LINEST(Y_array ; LN(X_array))
  63
  64
  65   3. Power regression is used to fit the following model :-
  66
  67      y = b * (x_1 ^ m_1) * (x_2 ^ m_2) * ... * (x_k ^ m_k)
  68
  69      To reduce this to a linear function(so that we can still use LINEST()), we take natural logarithm on both sides
  70
  71      ln(y) = c + m_1*ln(x_1) + m_2*ln(x_2) + ... + m_k*ln(x_k) ; where c = ln(b)
  72
  73
  74      This again can be written compactly in matrix form as :-
  75
  76      ln(y) = ln(X)*A
  77
  78      where y is the n dimensional column vector containing dependent variable observations.
  79      where X is matrix of shape n*(k+1) where a row looks like  [ e  x_1  x_2 ...  x_k ]
  80            A is the k+1 dimensional column vector               [ c  m_1  m_2 ...  m_k ]
  81
  82      To estimate A, we use the formula =LINEST(LN(Y_array) ; LN(X_array))
  83
  84      Once we get A, to get back y from x's we use the formula :-
  85
  86      y = exp( ln(X)*A )
  87
  88
  89
  90      Some references for computing confidence interval for the regression coefficients :-
  91
  92      [1] https://en.wikipedia.org/wiki/Student%27s_t-test#Slope_of_a_regression_line
  93      [2] https://en.wikipedia.org/wiki/Simple_linear_regression#Normality_assumption
  94      [3] https://onlinecourses.science.psu.edu/stat414/node/280
  95
  96  */
  97
  98 namespace
  99 {
 100     enum class ScRegType {
 101         LINEAR,
 102         LOGARITHMIC,
 103         POWER
 104     };
 105
 106     const TranslateId constRegressionModel[] =
 107     {
 108         STR_LABEL_LINEAR,
 109         STR_LABEL_LOGARITHMIC,
 110         STR_LABEL_POWER
 111     };
 112
 113     OUString constTemplateLINEST[] =
 114     {
 115         u"=LINEST(%VARIABLE2_RANGE% ; %VARIABLE1_RANGE% ; %CALC_INTERCEPT% ; TRUE)"_ustr,
 116         u"=LINEST(%VARIABLE2_RANGE% ; LN(%VARIABLE1_RANGE%) ; %CALC_INTERCEPT% ; TRUE)"_ustr,
 117         u"=LINEST(LN(%VARIABLE2_RANGE%) ; LN(%VARIABLE1_RANGE%) ; %CALC_INTERCEPT% ; TRUE)"_ustr
 118     };
 119
 120     OUString constRegressionFormula[] =
 121     {
 122         u"=MMULT(%XDATAMATRIX_RANGE% ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%"_ustr,
 123         u"=MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%"_ustr,
 124         u"=EXP(MMULT(LN(%XDATAMATRIX_RANGE%) ; %SLOPES_RANGE%) + %INTERCEPT_ADDR%)"_ustr
 125     };
 126
 127 } // end anonymous namespace
 128
 129 static size_t lcl_GetNumRowsColsInRange(const ScRange& rRange, bool bRows)
 130 {
 131     if (bRows)
 132         return rRange.aEnd.Row() - rRange.aStart.Row() + 1;
 133
 134     return rRange.aEnd.Col() - rRange.aStart.Col() + 1;
 135 }
 136
 137 ScRegressionDialog::ScRegressionDialog(
 138                     SfxBindings* pSfxBindings, SfxChildWindow* pChildWindow,
 139                     weld::Window* pParent, ScViewData& rViewData )
 140     : ScStatisticsTwoVariableDialog(
 141             pSfxBindings, pChildWindow, pParent, rViewData,
 142             u"modules/scalc/ui/regressiondialog.ui"_ustr, u"RegressionDialog"_ustr)
 143     , mbUnivariate(true)
 144     , mnNumIndependentVars(1)
 145     , mnNumObservations(0)
 146     , mbUse3DAddresses(false)
 147     , mbCalcIntercept(true)
 148     , mxWithLabelsCheckBox(m_xBuilder->weld_check_button(u"withlabels-check"_ustr))
 149     , mxLinearRadioButton(m_xBuilder->weld_radio_button(u"linear-radio"_ustr))
 150     , mxLogarithmicRadioButton(m_xBuilder->weld_radio_button(u"logarithmic-radio"_ustr))
 151     , mxErrorMessage(m_xBuilder->weld_label(u"error-message"_ustr))
 152     , mxConfidenceLevelField(m_xBuilder->weld_spin_button(u"confidencelevel-spin"_ustr))
 153     , mxCalcResidualsCheckBox(m_xBuilder->weld_check_button(u"calcresiduals-check"_ustr))
 154     , mxNoInterceptCheckBox(m_xBuilder->weld_check_button(u"nointercept-check"_ustr))
 155 {
 156     mxWithLabelsCheckBox->connect_toggled(LINK(this, ScRegressionDialog, CheckBoxHdl));
 157     mxConfidenceLevelField->connect_value_changed(LINK(this, ScRegressionDialog, NumericFieldHdl));
 158 }
 159
 160 ScRegressionDialog::~ScRegressionDialog()
 161 {
 162 }
 163
 164 void ScRegressionDialog::Close()
 165 {
 166     DoClose(ScRegressionDialogWrapper::GetChildWindowId());
 167 }
 168
 169 TranslateId ScRegressionDialog::GetUndoNameId()
 170 {
 171     return STR_REGRESSION_UNDO_NAME;
 172 }
 173
 174 ScRange ScRegressionDialog::ApplyOutput(ScDocShell* pDocShell)
 175 {
 176     AddressWalkerWriter aOutput(mOutputAddress, pDocShell, mDocument,
 177             formula::FormulaGrammar::mergeToGrammar( formula::FormulaGrammar::GRAM_ENGLISH, mAddressDetails.eConv));
 178     FormulaTemplate aTemplate(&mDocument);
 179     aTemplate.autoReplaceUses3D(mbUse3DAddresses);
 180     mbCalcIntercept = !mxNoInterceptCheckBox->get_active();
 181
 182     // max col of our output should account for
 183     // 1. constant term column,
 184     // 2. mnNumIndependentVars columns
 185     // 3. Actual Y column
 186     // 4. Predicted Y column
 187     // 5. Residual Column
 188     SCCOL nOutputMaxCol = mOutputAddress.Col() + mnNumIndependentVars + 3;
 189
 190     ScRange aXDataRange(GetDataRange(mVariable1Range));
 191     ScRange aYDataRange(GetDataRange(mVariable2Range));
 192
 193     aTemplate.autoReplaceRange(u"%VARIABLE1_RANGE%"_ustr, aXDataRange);
 194     aTemplate.autoReplaceRange(u"%VARIABLE2_RANGE%"_ustr, aYDataRange);
 195     size_t nRegressionIndex = GetRegressionTypeIndex();
 196     ScRegType eRegType = static_cast<ScRegType>(nRegressionIndex);
 197     bool bTakeLogX = eRegType == ScRegType::LOGARITHMIC || eRegType == ScRegType::POWER;
 198
 199     WriteRawRegressionResults(aOutput, aTemplate, nRegressionIndex);
 200     WriteRegressionStatistics(aOutput, aTemplate);
 201     WriteRegressionANOVAResults(aOutput, aTemplate);
 202     WriteRegressionEstimatesWithCI(aOutput, aTemplate, bTakeLogX);
 203     if (mxCalcResidualsCheckBox->get_active())
 204         WritePredictionsWithResiduals(aOutput, aTemplate, nRegressionIndex);
 205
 206     ScAddress aMaxAddress(aOutput.mMaximumAddress);
 207     aMaxAddress.SetCol(std::max(aMaxAddress.Col(), nOutputMaxCol));
 208     return ScRange(aOutput.mMinimumAddress, aMaxAddress);
 209 }
 210
 211 bool ScRegressionDialog::InputRangesValid()
 212 {
 213     if (!mVariable1Range.IsValid())
 214     {
 215         mxErrorMessage->set_label(ScResId(STR_MESSAGE_XINVALID_RANGE));
 216         return false;
 217     }
 218
 219     if (!mVariable2Range.IsValid())
 220     {
 221         mxErrorMessage->set_label(ScResId(STR_MESSAGE_YINVALID_RANGE));
 222         return false;
 223     }
 224
 225     if (!mOutputAddress.IsValid())
 226     {
 227         mxErrorMessage->set_label(ScResId(STR_MESSAGE_INVALID_OUTPUT_ADDR));
 228         return false;
 229     }
 230
 231     {
 232         double fConfidenceLevel = mxConfidenceLevelField->get_value();
 233         if ( fConfidenceLevel <= 0.0 || fConfidenceLevel >= 100.0 )
 234         {
 235             mxErrorMessage->set_label(ScResId(STR_MESSAGE_INVALID_CONFIDENCE_LEVEL));
 236             return false;
 237         }
 238     }
 239
 240     mVariable1Range.PutInOrder();
 241     mVariable2Range.PutInOrder();
 242
 243     bool bGroupedByColumn = mGroupedBy == BY_COLUMN;
 244
 245     bool bYHasSingleDim = (
 246         (bGroupedByColumn &&
 247          mVariable2Range.aStart.Col() == mVariable2Range.aEnd.Col()) ||
 248         (!bGroupedByColumn &&
 249          mVariable2Range.aStart.Row() == mVariable2Range.aEnd.Row()));
 250
 251     if (!bYHasSingleDim)
 252     {
 253         if (bGroupedByColumn)
 254             mxErrorMessage->set_label(ScResId(STR_MESSAGE_YVARIABLE_MULTI_COLUMN));
 255         else
 256             mxErrorMessage->set_label(ScResId(STR_MESSAGE_YVARIABLE_MULTI_ROW));
 257         return false;
 258     }
 259
 260     bool bWithLabels = mxWithLabelsCheckBox->get_active();
 261
 262     size_t nYObs = lcl_GetNumRowsColsInRange(mVariable2Range, bGroupedByColumn);
 263     size_t nNumXVars = lcl_GetNumRowsColsInRange(mVariable1Range, !bGroupedByColumn);
 264     mbUnivariate = nNumXVars == 1;
 265     // Observation count mismatch check
 266     if (lcl_GetNumRowsColsInRange(mVariable1Range, bGroupedByColumn) != nYObs)
 267     {
 268         if (mbUnivariate)
 269             mxErrorMessage->set_label(ScResId(STR_MESSAGE_UNIVARIATE_NUMOBS_MISMATCH));
 270         else
 271             mxErrorMessage->set_label(ScResId(STR_MESSAGE_MULTIVARIATE_NUMOBS_MISMATCH));
 272         return false;
 273     }
 274
 275     mnNumIndependentVars = nNumXVars;
 276     mnNumObservations = bWithLabels ? nYObs - 1 : nYObs;
 277
 278     mbUse3DAddresses = mVariable1Range.aStart.Tab() != mOutputAddress.Tab() ||
 279         mVariable2Range.aStart.Tab() != mOutputAddress.Tab();
 280
 281     mxErrorMessage->set_label(u""_ustr);
 282
 283     return true;
 284 }
 285
 286 size_t ScRegressionDialog::GetRegressionTypeIndex() const
 287 {
 288     if (mxLinearRadioButton->get_active())
 289         return 0;
 290     if (mxLogarithmicRadioButton->get_active())
 291         return 1;
 292     return 2;
 293 }
 294
 295 ScRange ScRegressionDialog::GetDataRange(const ScRange& rRange)
 296 {
 297     if (!mxWithLabelsCheckBox->get_active())
 298         return rRange;
 299
 300     ScRange aDataRange(rRange);
 301     if (mGroupedBy == BY_COLUMN)
 302         aDataRange.aStart.IncRow(1);
 303     else
 304         aDataRange.aStart.IncCol(1);
 305
 306     return aDataRange;
 307 }
 308
 309 OUString ScRegressionDialog::GetVariableNameFormula(bool bXVar, size_t nIndex, bool bWithLog)
 310 {
 311     if (bXVar && nIndex == 0)
 312         return "=\"" + ScResId(STR_LABEL_INTERCEPT) + "\"";
 313
 314     if (mxWithLabelsCheckBox->get_active())
 315     {
 316         ScAddress aAddr(bXVar ? mVariable1Range.aStart : mVariable2Range.aStart);
 317         if (mGroupedBy == BY_COLUMN)
 318             aAddr.IncCol(nIndex - 1);
 319         else
 320             aAddr.IncRow(nIndex - 1);
 321
 322         ScRefFlags eAddrFlag = mbUse3DAddresses ? ScRefFlags::ADDR_ABS_3D : ScRefFlags::ADDR_ABS;
 323         return bWithLog ? OUString("=CONCAT(\"LN(\";" +
 324             aAddr.Format(eAddrFlag, &mDocument, mDocument.GetAddressConvention()) + ";\")\")") :
 325             OUString("=" + aAddr.Format(eAddrFlag, &mDocument, mDocument.GetAddressConvention()));
 326     }
 327
 328     OUString aDefaultVarName;
 329
 330     if (bXVar)
 331         aDefaultVarName = "X" + OUString::number(nIndex);
 332     else
 333         aDefaultVarName = "Y";
 334
 335     return bWithLog ? OUString("=\"LN(" + aDefaultVarName + ")\"") :
 336         OUString("=\"" + aDefaultVarName + "\"");
 337 }
 338
 339 OUString ScRegressionDialog::GetXVariableNameFormula(size_t nIndex, bool bWithLog)
 340 {
 341     assert(nIndex <= mnNumIndependentVars);
 342     return GetVariableNameFormula(true, nIndex, bWithLog);
 343 }
 344
 345 OUString ScRegressionDialog::GetYVariableNameFormula(bool bWithLog)
 346 {
 347     return GetVariableNameFormula(false, 1, bWithLog);
 348 }
 349
 350 void ScRegressionDialog::WriteRawRegressionResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate,
 351                                                    size_t nRegressionIndex)
 352 {
 353     rOutput.writeBoldString(ScResId(STR_REGRESSION));
 354     rOutput.newLine();
 355     // REGRESSION MODEL
 356     rOutput.writeString(ScResId(STR_LABEL_REGRESSION_MODEL));
 357     rOutput.nextColumn();
 358     rOutput.writeString(ScResId(constRegressionModel[nRegressionIndex]));
 359     rOutput.newLine();
 360     rOutput.newLine();
 361
 362     rOutput.writeString(ScResId(STR_LINEST_RAW_OUTPUT_TITLE));
 363     rOutput.newLine();
 364     rOutput.push();
 365
 366     rTemplate.setTemplate(constTemplateLINEST[nRegressionIndex].
 367                           replaceFirst("%CALC_INTERCEPT%",
 368                                        mbCalcIntercept ? std::u16string_view(u"TRUE") : std::u16string_view(u"FALSE")));
 369     rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1 + mnNumIndependentVars, 5);
 370     // Add LINEST result components to template
 371     // 1. Add ranges for coefficients and standard errors for independent vars and the intercept.
 372     // Note that these two are in the reverse order(m_n, m_n-1, ..., m_1, b) w.r.t what we expect.
 373     rTemplate.autoReplaceRange(u"%COEFFICIENTS_REV_RANGE%"_ustr, ScRange(rOutput.current(), rOutput.current(mnNumIndependentVars)));
 374     rTemplate.autoReplaceRange(u"%SERRORSX_REV_RANGE%"_ustr, ScRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars, 1)));
 375
 376     // 2. Add R-squared and standard error for y estimate.
 377     rTemplate.autoReplaceAddress(u"%RSQUARED_ADDR%"_ustr, rOutput.current(0, 2));
 378     rTemplate.autoReplaceAddress(u"%SERRORY_ADDR%"_ustr, rOutput.current(1, 2));
 379
 380     // 3. Add F statistic and degrees of freedom
 381     rTemplate.autoReplaceAddress(u"%FSTATISTIC_ADDR%"_ustr, rOutput.current(0, 3));
 382     rTemplate.autoReplaceAddress(u"%DoFRESID_ADDR%"_ustr, rOutput.current(1, 3));
 383
 384     // 4. Add regression sum of squares and residual sum of squares
 385     rTemplate.autoReplaceAddress(u"%SSREG_ADDR%"_ustr, rOutput.current(0, 4));
 386     rTemplate.autoReplaceAddress(u"%SSRESID_ADDR%"_ustr, rOutput.current(1, 4));
 387
 388     rOutput.push(0, 4);
 389     rOutput.newLine();
 390 }
 391
 392 void ScRegressionDialog::WriteRegressionStatistics(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate)
 393 {
 394     rOutput.newLine();
 395     rOutput.writeString(ScResId(STR_LABEL_REGRESSION_STATISTICS));
 396     rOutput.newLine();
 397
 398     const TranslateId aMeasureNames[] =
 399     {
 400         STR_LABEL_RSQUARED,
 401         STRID_CALC_STD_ERROR,
 402         STR_LABEL_XVARIABLES_COUNT,
 403         STR_OBSERVATIONS_LABEL,
 404         STR_LABEL_ADJUSTED_RSQUARED
 405     };
 406
 407     OUString aMeasureFormulas[] =
 408     {
 409         u"=%RSQUARED_ADDR%"_ustr,
 410         u"=%SERRORY_ADDR%"_ustr,
 411         "=" + OUString::number(mnNumIndependentVars),
 412         "=" + OUString::number(mnNumObservations),
 413         OUString::Concat(
 414                 "=1 - (1 - %RSQUARED_ADDR%)*(%NUMOBS_ADDR% - 1)/(%NUMOBS_ADDR% - %NUMXVARS_ADDR%") +
 415             (mbCalcIntercept ? std::u16string_view(u" - 1)") : std::u16string_view(u")"))
 416     };
 417
 418     rTemplate.autoReplaceAddress(u"%NUMXVARS_ADDR%"_ustr, rOutput.current(1, 2));
 419     rTemplate.autoReplaceAddress(u"%NUMOBS_ADDR%"_ustr, rOutput.current(1, 3));
 420
 421     for (size_t nIdx = 0; nIdx < SAL_N_ELEMENTS(aMeasureNames); ++nIdx)
 422     {
 423         rOutput.writeString(ScResId(aMeasureNames[nIdx]));
 424         rOutput.nextColumn();
 425         rTemplate.setTemplate(aMeasureFormulas[nIdx]);
 426         rOutput.writeFormula(rTemplate.getTemplate());
 427         rOutput.newLine();
 428     }
 429 }
 430
 431 void ScRegressionDialog::WriteRegressionANOVAResults(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate)
 432 {
 433     rOutput.newLine();
 434     rOutput.writeString(ScResId(STR_LABEL_ANOVA));
 435     rOutput.newLine();
 436
 437     const size_t nColsInTable = 6;
 438     const size_t nRowsInTable = 4;
 439     OUString aTable[nRowsInTable][nColsInTable] =
 440     {
 441         {
 442             u""_ustr,
 443             ScResId(STR_ANOVA_LABEL_DF),
 444             ScResId(STR_ANOVA_LABEL_SS),
 445             ScResId(STR_ANOVA_LABEL_MS),
 446             ScResId(STR_ANOVA_LABEL_F),
 447             ScResId(STR_ANOVA_LABEL_SIGNIFICANCE_F)
 448         },
 449         {
 450             ScResId(STR_REGRESSION),
 451             u"=%NUMXVARS_ADDR%"_ustr,
 452             u"=%SSREG_ADDR%"_ustr,
 453             u"=%SSREG_ADDR% / %DoFREG_ADDR%"_ustr,
 454             u"=%FSTATISTIC_ADDR%"_ustr,
 455             u"=FDIST(%FSTATISTIC_ADDR% ; %DoFREG_ADDR% ; %DoFRESID_ADDR%)"_ustr
 456         },
 457         {
 458             ScResId(STR_LABEL_RESIDUAL),
 459             u"=%DoFRESID_ADDR%"_ustr,
 460             u"=%SSRESID_ADDR%"_ustr,
 461             u"=%SSRESID_ADDR% / %DoFRESID_ADDR%"_ustr,
 462             u""_ustr,
 463             u""_ustr
 464         },
 465         {
 466             ScResId(STR_ANOVA_LABEL_TOTAL),
 467             u"=%DoFREG_ADDR% + %DoFRESID_ADDR%"_ustr,
 468             u"=%SSREG_ADDR% + %SSRESID_ADDR%"_ustr,
 469             u""_ustr,
 470             u""_ustr,
 471             u""_ustr
 472         }
 473     };
 474
 475     rTemplate.autoReplaceAddress(u"%DoFREG_ADDR%"_ustr, rOutput.current(1, 1));
 476
 477     // Cell getter lambda
 478     std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString&
 479     {
 480         return aTable[nRowIdx][nColIdx];
 481     };
 482
 483     // Cell writer lambda
 484     std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate]
 485         (const OUString& rContent, size_t /*nRowIdx*/, size_t /*nColIdx*/)
 486     {
 487         if (!rContent.isEmpty())
 488         {
 489             if (rContent.startsWith("="))
 490             {
 491                 rTemplate.setTemplate(rContent);
 492                 rOutput.writeFormula(rTemplate.getTemplate());
 493             }
 494             else
 495                 rOutput.writeString(rContent);
 496         }
 497     };
 498
 499     WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc);
 500
 501     // User given confidence level
 502     rOutput.newLine();
 503     rOutput.writeString(ScResId(STR_LABEL_CONFIDENCE_LEVEL));
 504     rOutput.nextColumn();
 505     rOutput.writeValue(mxConfidenceLevelField->get_value() / 100.0);
 506     rTemplate.autoReplaceAddress(u"%CONFIDENCE_LEVEL_ADDR%"_ustr, rOutput.current());
 507     rOutput.newLine();
 508 }
 509
 510 // Write slopes, intercept, their standard errors, t-statistics, p-value, confidence intervals
 511 void ScRegressionDialog::WriteRegressionEstimatesWithCI(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate,
 512                                                         bool bTakeLogX)
 513 {
 514     rOutput.newLine();
 515     ScAddress aEnd( rOutput.current(0, 1 + mnNumIndependentVars));
 516     ScRefFlags eAddrFlag = mbUse3DAddresses ? ScRefFlags::ADDR_ABS_3D : ScRefFlags::ADDR_ABS;
 517     aEnd.IncCol();
 518     const OUString aCoeffAddr( aEnd.Format( eAddrFlag, &mDocument, mDocument.GetAddressConvention()));
 519     aEnd.IncCol();
 520     const OUString aStErrAddr( aEnd.Format( eAddrFlag, &mDocument, mDocument.GetAddressConvention()));
 521
 522     // Coefficients & Std.Errors ranges (column vectors) in this table (yet to populate).
 523     rTemplate.autoReplaceRange(u"%COEFFICIENTS_RANGE%"_ustr,
 524                                ScRange(rOutput.current(1, 1),
 525                                        rOutput.current(1, 1 + mnNumIndependentVars)));
 526     rTemplate.autoReplaceRange(u"%SLOPES_RANGE%"_ustr,  // Excludes the intercept
 527                                ScRange(rOutput.current(1, 2),
 528                                        rOutput.current(1, 1 + mnNumIndependentVars)));
 529     rTemplate.autoReplaceAddress(u"%INTERCEPT_ADDR%"_ustr, rOutput.current(1, 1));
 530     rTemplate.autoReplaceRange(u"%SERRORSX_RANGE%"_ustr,
 531                                ScRange(rOutput.current(2, 1),
 532                                        rOutput.current(2, 1 + mnNumIndependentVars)));
 533     // t-Statistics range in this table (yet to populate)
 534     rTemplate.autoReplaceRange(u"%TSTAT_RANGE%"_ustr,
 535                                ScRange(rOutput.current(3, 1),
 536                                        rOutput.current(3, 1 + mnNumIndependentVars)));
 537
 538     const size_t nColsInTable = 7;
 539     const size_t nRowsInTable = 2;
 540     OUString aTable[nRowsInTable][nColsInTable] =
 541     {
 542         {
 543             u""_ustr,
 544             ScResId(STR_LABEL_COEFFICIENTS),
 545             ScResId(STRID_CALC_STD_ERROR),
 546             ScResId(STR_LABEL_TSTATISTIC),
 547             ScResId(STR_P_VALUE_LABEL),
 548
 549             "=CONCAT(\"" + ScResId(STR_LABEL_LOWER) +
 550             " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")",
 551
 552             "=CONCAT(\"" + ScResId(STR_LABEL_UPPER) +
 553             " \" ; INT(%CONFIDENCE_LEVEL_ADDR% * 100) ; \"%\")",
 554         },
 555
 556         // Following are matrix formulas of size numcols = 1, numrows = (mnNumIndependentVars + 1)
 557         {
 558             u""_ustr,
 559             // This puts the coefficients in the reverse order compared to that in LINEST output.
 560             "=INDEX(%COEFFICIENTS_REV_RANGE%; 1 ; ROW(" + aCoeffAddr + ")+1 - ROW())",
 561             // This puts the standard errors in the reverse order compared to that in LINEST output.
 562             "=INDEX(%SERRORSX_REV_RANGE%; 1 ; ROW(" + aStErrAddr + ")+1 - ROW())",
 563             // t-Statistic
 564             u"=%COEFFICIENTS_RANGE% / %SERRORSX_RANGE%"_ustr,
 565             // p-Value
 566             u"=TDIST(ABS(%TSTAT_RANGE%) ; %DoFRESID_ADDR% ; 2 )"_ustr,
 567             // Lower limit of confidence interval
 568             u"=%COEFFICIENTS_RANGE% - %SERRORSX_RANGE% * "
 569             "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)"_ustr,
 570             // Upper limit of confidence interval
 571             u"=%COEFFICIENTS_RANGE% + %SERRORSX_RANGE% * "
 572             "TINV(1 - %CONFIDENCE_LEVEL_ADDR% ; %DoFRESID_ADDR%)"_ustr
 573         }
 574     };
 575
 576     // Cell getter lambda
 577     std::function<CellValueGetter> aCellGetterFunc = [&aTable](size_t nRowIdx, size_t nColIdx) -> const OUString&
 578     {
 579         return aTable[nRowIdx][nColIdx];
 580     };
 581
 582     // Cell writer lambda
 583     size_t nNumIndependentVars = mnNumIndependentVars;
 584     std::function<CellWriter> aCellWriterFunc = [&rOutput, &rTemplate, nNumIndependentVars]
 585         (const OUString& rContent, size_t nRowIdx, size_t /*nColIdx*/)
 586     {
 587         if (!rContent.isEmpty())
 588         {
 589             if (rContent.startsWith("="))
 590             {
 591                 rTemplate.setTemplate(rContent);
 592                 if (nRowIdx == 0)
 593                     rOutput.writeFormula(rTemplate.getTemplate());
 594                 else
 595                     rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, 1 + nNumIndependentVars);
 596             }
 597             else
 598                 rOutput.writeString(rContent);
 599         }
 600     };
 601
 602     WriteTable(aCellGetterFunc, nRowsInTable, nColsInTable, rOutput, aCellWriterFunc);
 603
 604     // Go back to the second row and first column of the table to
 605     // fill the names of variables + intercept
 606     rOutput.push(0, -1);
 607
 608     for (size_t nXvarIdx = 0; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx)
 609     {
 610         rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, bTakeLogX));
 611         rOutput.newLine();
 612     }
 613
 614 }
 615
 616 // Re-write all observations in group-by column mode with predictions and residuals
 617 void ScRegressionDialog::WritePredictionsWithResiduals(AddressWalkerWriter& rOutput, FormulaTemplate& rTemplate,
 618                                                        size_t nRegressionIndex)
 619 {
 620     bool bGroupedByColumn = mGroupedBy == BY_COLUMN;
 621     rOutput.newLine();
 622     rOutput.push();
 623
 624     // Range of X variables with rows as observations and columns as variables.
 625     ScRange aDataMatrixRange(rOutput.current(0, 1), rOutput.current(mnNumIndependentVars - 1, mnNumObservations));
 626     rTemplate.autoReplaceRange(u"%XDATAMATRIX_RANGE%"_ustr, aDataMatrixRange);
 627
 628     // Write X variable names
 629     for (size_t nXvarIdx = 1; nXvarIdx <= mnNumIndependentVars; ++nXvarIdx)
 630     {
 631         // Here we write the X variables without any transformation(LN)
 632         rOutput.writeFormula(GetXVariableNameFormula(nXvarIdx, false));
 633         rOutput.nextColumn();
 634     }
 635     rOutput.reset();
 636
 637     // Write the X data matrix
 638     rOutput.nextRow();
 639     OUString aDataMatrixFormula = bGroupedByColumn ? u"=%VARIABLE1_RANGE%"_ustr : u"=TRANSPOSE(%VARIABLE1_RANGE%)"_ustr;
 640     rTemplate.setTemplate(aDataMatrixFormula);
 641     rOutput.writeMatrixFormula(rTemplate.getTemplate(), mnNumIndependentVars, mnNumObservations);
 642
 643     // Write predicted values
 644     rOutput.push(mnNumIndependentVars, -1);
 645     rOutput.writeString(ScResId(STR_LABEL_PREDICTEDY));
 646     rOutput.nextRow();
 647     rTemplate.setTemplate(constRegressionFormula[nRegressionIndex]);
 648     rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations);
 649     rTemplate.autoReplaceRange(u"%PREDICTEDY_RANGE%"_ustr, ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1)));
 650
 651     // Write actual Y
 652     rOutput.push(1, -1);
 653     rOutput.writeFormula(GetYVariableNameFormula(false));
 654     rOutput.nextRow();
 655     OUString aYVectorFormula = bGroupedByColumn ? u"=%VARIABLE2_RANGE%"_ustr : u"=TRANSPOSE(%VARIABLE2_RANGE%)"_ustr;
 656     rTemplate.setTemplate(aYVectorFormula);
 657     rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations);
 658     rTemplate.autoReplaceRange(u"%ACTUALY_RANGE%"_ustr, ScRange(rOutput.current(), rOutput.current(0, mnNumObservations - 1)));
 659
 660     // Write residual
 661     rOutput.push(1, -1);
 662     rOutput.writeString(ScResId(STR_LABEL_RESIDUAL));
 663     rOutput.nextRow();
 664     rTemplate.setTemplate("=%ACTUALY_RANGE% - %PREDICTEDY_RANGE%");
 665     rOutput.writeMatrixFormula(rTemplate.getTemplate(), 1, mnNumObservations);
 666 }
 667
 668 // Generic table writer
 669 void ScRegressionDialog::WriteTable(const std::function<CellValueGetter>& rCellGetter,
 670                                     size_t nRowsInTable, size_t nColsInTable,
 671                                     AddressWalkerWriter& rOutput,
 672                                     const std::function<CellWriter>& rFunc)
 673 {
 674     for (size_t nRowIdx = 0; nRowIdx < nRowsInTable; ++nRowIdx)
 675     {
 676         for (size_t nColIdx = 0; nColIdx < nColsInTable; ++nColIdx)
 677         {
 678             rFunc(rCellGetter(nRowIdx, nColIdx), nRowIdx, nColIdx);
 679             rOutput.nextColumn();
 680         }
 681         rOutput.newLine();
 682     }
 683 }
 684
 685 IMPL_LINK_NOARG(ScRegressionDialog, CheckBoxHdl, weld::Toggleable&, void)
 686 {
 687     ValidateDialogInput();
 688 }
 689
 690 IMPL_LINK_NOARG(ScRegressionDialog, NumericFieldHdl, weld::SpinButton&, void)
 691 {
 692     ValidateDialogInput();
 693 }
 694
 695 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */