LibreOffice Module sc (master)  1
interpr8.cxx
Go to the documentation of this file.
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 <interpre.hxx>
12 #include <cellvalue.hxx>
13 #include <scmatrix.hxx>
14 #include <comphelper/random.hxx>
15 #include <formula/token.hxx>
16 #include <sal/log.hxx>
17 #include <svl/numformat.hxx>
18 
19 #include <cmath>
20 #include <memory>
21 #include <vector>
22 
23 using namespace formula;
24 
25 namespace {
26 
27 struct DataPoint
28 {
29  double X, Y;
30 
31  DataPoint( double rX, double rY ) : X( rX ), Y( rY ) {};
32 };
33 
34 }
35 
36 static bool lcl_SortByX( const DataPoint &lhs, const DataPoint &rhs ) { return lhs.X < rhs.X; }
37 
38 /*
39  * ScETSForecastCalculation
40  *
41  * Class is set up to be used with Calc's FORECAST.ETS
42  * functions and with chart extrapolations (not yet implemented).
43  *
44  * Triple Exponential Smoothing (Holt-Winters method)
45  *
46  * Forecasting of a linear change in data over time (y=a+b*x) with
47  * superimposed absolute or relative seasonal deviations, using additive
48  * respectively multiplicative Holt-Winters method.
49  *
50  * Initialisation and forecasting calculations are based on
51  * Engineering Statistics Handbook, 6.4.3.5 Triple Exponential Smoothing
52  * see "http://www.itl.nist.gov/div898/handbook/pmc/section4/pmc435.htm"
53  * Further to the above is that initial calculation of Seasonal effect
54  * is corrected for trend.
55  *
56  * Prediction Interval calculations are based on
57  * Yar & Chatfield, Prediction Intervals for the Holt-Winters forecasting
58  * procedure, International Journal of Forecasting, 1990, Vol.6, pp127-137
59  * The calculation here is a simplified numerical approximation of the above,
60  * using random distributions.
61  *
62  * Double Exponential Smoothing (Holt-Winters method)
63  *
64  * Forecasting of a linear change in data over time (y=a+b*x), using
65  * the Holt-Winters method.
66  *
67  * Initialisation and forecasting calculations are based on
68  * Engineering Statistics Handbook, 6.4.3.3 Double Exponential Smoothing
69  * see "http://www.itl.nist.gov/div898/handbook/pmc/section4/pmc433.htm"
70  *
71  * Prediction Interval calculations are based on
72  * Statistical Methods for Forecasting, Bovas & Ledolter, 2009, 3.8 Prediction
73  * Intervals for Future Values
74  *
75  */
76 
77 namespace {
78 
79 class ScETSForecastCalculation
80 {
81 private:
82  SvNumberFormatter* mpFormatter;
83  std::vector< DataPoint > maRange; // data (X, Y)
84  std::unique_ptr<double[]> mpBase; // calculated base value array
85  std::unique_ptr<double[]> mpTrend; // calculated trend factor array
86  std::unique_ptr<double[]> mpPerIdx; // calculated periodical deviation array, not used with eds
87  std::unique_ptr<double[]> mpForecast; // forecasted value array
88  SCSIZE mnSmplInPrd; // samples per period
89  double mfStepSize; // increment of X in maRange
90  double mfAlpha, mfBeta, mfGamma; // constants to minimize the RMSE in the ES-equations
91  SCSIZE mnCount; // No of data points
92  bool mbInitialised;
93  int mnMonthDay; // n-month X-interval, value is day of month
94  // accuracy indicators
95  double mfMAE; // mean absolute error
96  double mfMASE; // mean absolute scaled error
97  double mfMSE; // mean squared error (variation)
98  double mfRMSE; // root mean squared error (standard deviation)
99  double mfSMAPE; // symmetric mean absolute error
100  FormulaError mnErrorValue;
101  bool bAdditive; // true: additive method, false: multiplicative method
102  bool bEDS; // true: EDS, false: ETS
103 
104  // constants used in determining best fit for alpha, beta, gamma
105  static constexpr double cfMinABCResolution = 0.001; // minimum change of alpha, beta, gamma
106  static const SCSIZE cnScenarios = 1000; // No. of scenarios to calculate for PI calculations
107 
108  bool initData();
109  void prefillBaseData();
110  bool prefillTrendData();
111  bool prefillPerIdx();
112  void initCalc();
113  void refill();
114  SCSIZE CalcPeriodLen();
115  void CalcAlphaBetaGamma();
116  void CalcBetaGamma();
117  void CalcGamma();
118  void calcAccuracyIndicators();
119  void GetForecast( double fTarget, double& rForecast );
120  double RandDev();
121  double convertXtoMonths( double x );
122 
123 public:
124  ScETSForecastCalculation( SCSIZE nSize, SvNumberFormatter* pFormatter );
125 
126  bool PreprocessDataRange( const ScMatrixRef& rMatX, const ScMatrixRef& rMatY, int nSmplInPrd,
127  bool bDataCompletion, int nAggregation, const ScMatrixRef& rTMat,
128  ScETSType eETSType );
129  FormulaError GetError() const { return mnErrorValue; };
130  void GetForecastRange( const ScMatrixRef& rTMat, const ScMatrixRef& rFcMat );
131  void GetStatisticValue( const ScMatrixRef& rTypeMat, const ScMatrixRef& rStatMat );
132  void GetSamplesInPeriod( double& rVal );
133  void GetEDSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel );
134  void GetETSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel );
135 };
136 
137 }
138 
139 ScETSForecastCalculation::ScETSForecastCalculation( SCSIZE nSize, SvNumberFormatter* pFormatter )
140  : mpFormatter(pFormatter)
141  , mnSmplInPrd(0)
142  , mfStepSize(0.0)
143  , mfAlpha(0.0)
144  , mfBeta(0.0)
145  , mfGamma(0.0)
146  , mnCount(nSize)
147  , mbInitialised(false)
148  , mnMonthDay(0)
149  , mfMAE(0.0)
150  , mfMASE(0.0)
151  , mfMSE(0.0)
152  , mfRMSE(0.0)
153  , mfSMAPE(0.0)
154  , mnErrorValue(FormulaError::NONE)
155  , bAdditive(false)
156  , bEDS(false)
157 {
158  maRange.reserve( mnCount );
159 }
160 
161 bool ScETSForecastCalculation::PreprocessDataRange( const ScMatrixRef& rMatX, const ScMatrixRef& rMatY, int nSmplInPrd,
162  bool bDataCompletion, int nAggregation, const ScMatrixRef& rTMat,
163  ScETSType eETSType )
164 {
165  bEDS = ( nSmplInPrd == 0 );
166  bAdditive = ( eETSType == etsAdd || eETSType == etsPIAdd || eETSType == etsStatAdd );
167 
168  // maRange needs to be sorted by X
169  for ( SCSIZE i = 0; i < mnCount; i++ )
170  maRange.emplace_back( rMatX->GetDouble( i ), rMatY->GetDouble( i ) );
171  sort( maRange.begin(), maRange.end(), lcl_SortByX );
172 
173  if ( rTMat )
174  {
175  if ( eETSType != etsPIAdd && eETSType != etsPIMult )
176  {
177  if ( rTMat->GetDouble( 0 ) < maRange[ 0 ].X )
178  {
179  // target cannot be less than start of X-range
180  mnErrorValue = FormulaError::IllegalFPOperation;
181  return false;
182  }
183  }
184  else
185  {
186  if ( rTMat->GetDouble( 0 ) < maRange[ mnCount - 1 ].X )
187  {
188  // target cannot be before end of X-range
189  mnErrorValue = FormulaError::IllegalFPOperation;
190  return false;
191  }
192  }
193  }
194 
195  // Month intervals don't have exact stepsize, so first
196  // detect if month interval is used.
197  // Method: assume there is an month interval and verify.
198  // If month interval is used, replace maRange.X with month values
199  // for ease of calculations.
200  Date aNullDate = mpFormatter->GetNullDate();
201  Date aDate = aNullDate + static_cast< sal_Int32 >( maRange[ 0 ].X );
202  mnMonthDay = aDate.GetDay();
203  for ( SCSIZE i = 1; i < mnCount && mnMonthDay; i++ )
204  {
205  Date aDate1 = aNullDate + static_cast< sal_Int32 >( maRange[ i ].X );
206  if ( aDate != aDate1 )
207  {
208  if ( aDate1.GetDay() != mnMonthDay )
209  mnMonthDay = 0;
210  }
211  }
212 
213  mfStepSize = ::std::numeric_limits<double>::max();
214  if ( mnMonthDay )
215  {
216  for ( SCSIZE i = 0; i < mnCount; i++ )
217  {
218  aDate = aNullDate + static_cast< sal_Int32 >( maRange[ i ].X );
219  maRange[ i ].X = aDate.GetYear() * 12 + aDate.GetMonth();
220  }
221  }
222  for ( SCSIZE i = 1; i < mnCount; i++ )
223  {
224  double fStep = maRange[ i ].X - maRange[ i - 1 ].X;
225  if ( fStep == 0.0 )
226  {
227  if ( nAggregation == 0 )
228  {
229  // identical X-values are not allowed
230  mnErrorValue = FormulaError::NoValue;
231  return false;
232  }
233  double fTmp = maRange[ i - 1 ].Y;
234  SCSIZE nCounter = 1;
235  switch ( nAggregation )
236  {
237  case 1 : // AVERAGE (default)
238  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
239  {
240  maRange.erase( maRange.begin() + i );
241  --mnCount;
242  }
243  break;
244  case 7 : // SUM
245  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
246  {
247  fTmp += maRange[ i ].Y;
248  maRange.erase( maRange.begin() + i );
249  --mnCount;
250  }
251  maRange[ i - 1 ].Y = fTmp;
252  break;
253 
254  case 2 : // COUNT
255  case 3 : // COUNTA (same as COUNT as there are no non-numeric Y-values)
256  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
257  {
258  nCounter++;
259  maRange.erase( maRange.begin() + i );
260  --mnCount;
261  }
262  maRange[ i - 1 ].Y = nCounter;
263  break;
264 
265  case 4 : // MAX
266  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
267  {
268  if ( maRange[ i ].Y > fTmp )
269  fTmp = maRange[ i ].Y;
270  maRange.erase( maRange.begin() + i );
271  --mnCount;
272  }
273  maRange[ i - 1 ].Y = fTmp;
274  break;
275 
276  case 5 : // MEDIAN
277  {
278  std::vector< double > aTmp;
279  aTmp.push_back( maRange[ i - 1 ].Y );
280  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
281  {
282  aTmp.push_back( maRange[ i ].Y );
283  nCounter++;
284  maRange.erase( maRange.begin() + i );
285  --mnCount;
286  }
287  sort( aTmp.begin(), aTmp.end() );
288 
289  if ( nCounter % 2 )
290  maRange[ i - 1 ].Y = aTmp[ nCounter / 2 ];
291  else
292  maRange[ i - 1 ].Y = ( aTmp[ nCounter / 2 ] + aTmp[ nCounter / 2 - 1 ] ) / 2.0;
293  }
294  break;
295 
296  case 6 : // MIN
297  while ( i < mnCount && maRange[ i ].X == maRange[ i - 1 ].X )
298  {
299  if ( maRange[ i ].Y < fTmp )
300  fTmp = maRange[ i ].Y;
301  maRange.erase( maRange.begin() + i );
302  --mnCount;
303  }
304  maRange[ i - 1 ].Y = fTmp;
305  break;
306  }
307  if ( i < mnCount - 1 )
308  fStep = maRange[ i ].X - maRange[ i - 1 ].X;
309  else
310  fStep = mfStepSize;
311  }
312  if ( fStep > 0 && fStep < mfStepSize )
313  mfStepSize = fStep;
314  }
315 
316  // step must be constant (or gap multiple of step)
317  bool bHasGap = false;
318  for ( SCSIZE i = 1; i < mnCount && !bHasGap; i++ )
319  {
320  double fStep = maRange[ i ].X - maRange[ i - 1 ].X;
321 
322  if ( fStep != mfStepSize )
323  {
324  if ( fmod( fStep, mfStepSize ) != 0.0 )
325  {
326  // step not constant nor multiple of mfStepSize in case of gaps
327  mnErrorValue = FormulaError::NoValue;
328  return false;
329  }
330  bHasGap = true;
331  }
332  }
333 
334  // fill gaps with values depending on bDataCompletion
335  if ( bHasGap )
336  {
337  SCSIZE nMissingXCount = 0;
338  double fOriginalCount = static_cast< double >( mnCount );
339  if ( mnMonthDay )
340  aDate = aNullDate + static_cast< sal_Int32 >( maRange[ 0 ].X );
341  for ( SCSIZE i = 1; i < mnCount; i++ )
342  {
343  double fDist;
344  if ( mnMonthDay )
345  {
346  Date aDate1 = aNullDate + static_cast< sal_Int32 >( maRange[ i ].X );
347  fDist = 12 * ( aDate1.GetYear() - aDate.GetYear() ) +
348  ( aDate1.GetMonth() - aDate.GetMonth() );
349  aDate = aDate1;
350  }
351  else
352  fDist = maRange[ i ].X - maRange[ i - 1 ].X;
353  if ( fDist > mfStepSize )
354  {
355  // gap, insert missing data points
356  double fYGap = ( maRange[ i ].Y + maRange[ i - 1 ].Y ) / 2.0;
357  for ( KahanSum fXGap = maRange[ i - 1].X + mfStepSize; fXGap < maRange[ i ].X; fXGap += mfStepSize )
358  {
359  maRange.insert( maRange.begin() + i, DataPoint( fXGap.get(), ( bDataCompletion ? fYGap : 0.0 ) ) );
360  i++;
361  mnCount++;
362  nMissingXCount++;
363  if ( static_cast< double >( nMissingXCount ) / fOriginalCount > 0.3 )
364  {
365  // maximum of 30% missing points exceeded
366  mnErrorValue = FormulaError::NoValue;
367  return false;
368  }
369  }
370  }
371  }
372  }
373 
374  if ( nSmplInPrd != 1 )
375  mnSmplInPrd = nSmplInPrd;
376  else
377  {
378  mnSmplInPrd = CalcPeriodLen();
379  if ( mnSmplInPrd == 1 )
380  bEDS = true; // period length 1 means no periodic data: EDS suffices
381  }
382 
383  if ( !initData() )
384  return false; // note: mnErrorValue is set in called function(s)
385 
386  return true;
387 }
388 
389 bool ScETSForecastCalculation::initData( )
390 {
391  // give various vectors size and initial value
392  mpBase.reset( new double[ mnCount ] );
393  mpTrend.reset( new double[ mnCount ] );
394  if ( !bEDS )
395  mpPerIdx.reset( new double[ mnCount ] );
396  mpForecast.reset( new double[ mnCount ] );
397  mpForecast[ 0 ] = maRange[ 0 ].Y;
398 
399  if ( prefillTrendData() )
400  {
401  if ( prefillPerIdx() )
402  {
403  prefillBaseData();
404  return true;
405  }
406  }
407  return false;
408 }
409 
410 bool ScETSForecastCalculation::prefillTrendData()
411 {
412  if ( bEDS )
413  mpTrend[ 0 ] = ( maRange[ mnCount - 1 ].Y - maRange[ 0 ].Y ) / static_cast< double >( mnCount - 1 );
414  else
415  {
416  // we need at least 2 periods in the data range
417  if ( mnCount < 2 * mnSmplInPrd )
418  {
419  mnErrorValue = FormulaError::NoValue;
420  return false;
421  }
422 
423  KahanSum fSum = 0.0;
424  for ( SCSIZE i = 0; i < mnSmplInPrd; i++ )
425  {
426  fSum += maRange[ i + mnSmplInPrd ].Y;
427  fSum -= maRange[ i ].Y;
428  }
429  double fTrend = fSum.get() / static_cast< double >( mnSmplInPrd * mnSmplInPrd );
430 
431  mpTrend[ 0 ] = fTrend;
432  }
433 
434  return true;
435 }
436 
437 bool ScETSForecastCalculation::prefillPerIdx()
438 {
439  if ( !bEDS )
440  {
441  // use as many complete periods as available
442  if ( mnSmplInPrd == 0 )
443  {
444  // should never happen; if mnSmplInPrd equals 0, bEDS is true
445  mnErrorValue = FormulaError::UnknownState;
446  return false;
447  }
448  SCSIZE nPeriods = mnCount / mnSmplInPrd;
449  std::vector< KahanSum > aPeriodAverage( nPeriods, 0.0 );
450  for ( SCSIZE i = 0; i < nPeriods ; i++ )
451  {
452  for ( SCSIZE j = 0; j < mnSmplInPrd; j++ )
453  aPeriodAverage[ i ] += maRange[ i * mnSmplInPrd + j ].Y;
454  aPeriodAverage[ i ] /= static_cast< double >( mnSmplInPrd );
455  if ( aPeriodAverage[ i ] == 0.0 )
456  {
457  SAL_WARN( "sc.core", "prefillPerIdx(), average of 0 will cause divide by zero error, quitting calculation" );
458  mnErrorValue = FormulaError::DivisionByZero;
459  return false;
460  }
461  }
462 
463  for ( SCSIZE j = 0; j < mnSmplInPrd; j++ )
464  {
465  KahanSum fI = 0.0;
466  for ( SCSIZE i = 0; i < nPeriods ; i++ )
467  {
468  // adjust average value for position within period
469  if ( bAdditive )
470  fI += maRange[ i * mnSmplInPrd + j ].Y -
471  ( aPeriodAverage[ i ].get() + ( static_cast< double >( j ) - 0.5 * ( mnSmplInPrd - 1 ) ) *
472  mpTrend[ 0 ] );
473  else
474  fI += maRange[ i * mnSmplInPrd + j ].Y /
475  ( aPeriodAverage[ i ].get() + ( static_cast< double >( j ) - 0.5 * ( mnSmplInPrd - 1 ) ) *
476  mpTrend[ 0 ] );
477  }
478  mpPerIdx[ j ] = fI.get() / nPeriods;
479  }
480  if (mnSmplInPrd < mnCount)
481  mpPerIdx[mnSmplInPrd] = 0.0;
482  }
483  return true;
484 }
485 
486 void ScETSForecastCalculation::prefillBaseData()
487 {
488  if ( bEDS )
489  mpBase[ 0 ] = maRange[ 0 ].Y;
490  else
491  mpBase[ 0 ] = maRange[ 0 ].Y / mpPerIdx[ 0 ];
492 }
493 
494 void ScETSForecastCalculation::initCalc()
495 {
496  if ( !mbInitialised )
497  {
498  CalcAlphaBetaGamma();
499 
500  mbInitialised = true;
501  calcAccuracyIndicators();
502  }
503 }
504 
505 void ScETSForecastCalculation::calcAccuracyIndicators()
506 {
507  KahanSum fSumAbsErr = 0.0;
508  KahanSum fSumDivisor = 0.0;
509  KahanSum fSumErrSq = 0.0;
510  KahanSum fSumAbsPercErr = 0.0;
511 
512  for ( SCSIZE i = 1; i < mnCount; i++ )
513  {
514  double fError = mpForecast[ i ] - maRange[ i ].Y;
515  fSumAbsErr += fabs( fError );
516  fSumErrSq += fError * fError;
517  fSumAbsPercErr += fabs( fError ) / ( fabs( mpForecast[ i ] ) + fabs( maRange[ i ].Y ) );
518  }
519 
520  for ( SCSIZE i = 2; i < mnCount; i++ )
521  fSumDivisor += fabs( maRange[ i ].Y - maRange[ i - 1 ].Y );
522 
523  int nCalcCount = mnCount - 1;
524  mfMAE = fSumAbsErr.get() / nCalcCount;
525  mfMASE = fSumAbsErr.get() / ( nCalcCount * fSumDivisor.get() / ( nCalcCount - 1 ) );
526  mfMSE = fSumErrSq.get() / nCalcCount;
527  mfRMSE = sqrt( mfMSE );
528  mfSMAPE = fSumAbsPercErr.get() * 2.0 / nCalcCount;
529 }
530 
531 /*
532  * CalcPeriodLen() calculates the most likely length of a period.
533  *
534  * Method used: for all possible values (between mnCount/2 and 2) compare for
535  * each (sample-previous sample) with next period and calculate mean error.
536  * Use as much samples as possible for each period length and the most recent samples
537  * Return the period length with the lowest mean error.
538  */
539 SCSIZE ScETSForecastCalculation::CalcPeriodLen()
540 {
541  SCSIZE nBestVal = mnCount;
542  double fBestME = ::std::numeric_limits<double>::max();
543 
544  for ( SCSIZE nPeriodLen = mnCount / 2; nPeriodLen >= 1; nPeriodLen-- )
545  {
546  KahanSum fMeanError = 0.0;
547  SCSIZE nPeriods = mnCount / nPeriodLen;
548  SCSIZE nStart = mnCount - ( nPeriods * nPeriodLen ) + 1;
549  for ( SCSIZE i = nStart; i < ( mnCount - nPeriodLen ); i++ )
550  {
551  fMeanError += fabs( ( maRange[ i ].Y - maRange[ i - 1 ].Y ) -
552  ( maRange[ nPeriodLen + i ].Y - maRange[ nPeriodLen + i - 1 ].Y ) );
553  }
554  double fMeanErrorGet = fMeanError.get();
555  fMeanErrorGet /= static_cast< double >( ( nPeriods - 1 ) * nPeriodLen - 1 );
556 
557  if ( fMeanErrorGet <= fBestME || fMeanErrorGet == 0.0 )
558  {
559  nBestVal = nPeriodLen;
560  fBestME = fMeanErrorGet;
561  }
562  }
563  return nBestVal;
564 }
565 
566 void ScETSForecastCalculation::CalcAlphaBetaGamma()
567 {
568  double f0 = 0.0;
569  mfAlpha = f0;
570  if ( bEDS )
571  {
572  mfBeta = 0.0; // beta is not used with EDS
573  CalcGamma();
574  }
575  else
576  CalcBetaGamma();
577  refill();
578  double fE0 = mfMSE;
579 
580  double f2 = 1.0;
581  mfAlpha = f2;
582  if ( bEDS )
583  CalcGamma();
584  else
585  CalcBetaGamma();
586  refill();
587  double fE2 = mfMSE;
588 
589  double f1 = 0.5;
590  mfAlpha = f1;
591  if ( bEDS )
592  CalcGamma();
593  else
594  CalcBetaGamma();
595  refill();
596 
597  if ( fE0 == mfMSE && mfMSE == fE2 )
598  {
599  mfAlpha = 0;
600  if ( bEDS )
601  CalcGamma();
602  else
603  CalcBetaGamma();
604  refill();
605  return;
606  }
607  while ( ( f2 - f1 ) > cfMinABCResolution )
608  {
609  if ( fE2 > fE0 )
610  {
611  f2 = f1;
612  fE2 = mfMSE;
613  f1 = ( f0 + f1 ) / 2;
614  }
615  else
616  {
617  f0 = f1;
618  fE0 = mfMSE;
619  f1 = ( f1 + f2 ) / 2;
620  }
621  mfAlpha = f1;
622  if ( bEDS )
623  CalcGamma();
624  else
625  CalcBetaGamma();
626  refill();
627  }
628  if ( fE2 > fE0 )
629  {
630  if ( fE0 < mfMSE )
631  {
632  mfAlpha = f0;
633  if ( bEDS )
634  CalcGamma();
635  else
636  CalcBetaGamma();
637  refill();
638  }
639  }
640  else
641  {
642  if ( fE2 < mfMSE )
643  {
644  mfAlpha = f2;
645  if ( bEDS )
646  CalcGamma();
647  else
648  CalcBetaGamma();
649  refill();
650  }
651  }
652  calcAccuracyIndicators();
653 }
654 
655 void ScETSForecastCalculation::CalcBetaGamma()
656 {
657  double f0 = 0.0;
658  mfBeta = f0;
659  CalcGamma();
660  refill();
661  double fE0 = mfMSE;
662 
663  double f2 = 1.0;
664  mfBeta = f2;
665  CalcGamma();
666  refill();
667  double fE2 = mfMSE;
668 
669  double f1 = 0.5;
670  mfBeta = f1;
671  CalcGamma();
672  refill();
673 
674  if ( fE0 == mfMSE && mfMSE == fE2 )
675  {
676  mfBeta = 0;
677  CalcGamma();
678  refill();
679  return;
680  }
681  while ( ( f2 - f1 ) > cfMinABCResolution )
682  {
683  if ( fE2 > fE0 )
684  {
685  f2 = f1;
686  fE2 = mfMSE;
687  f1 = ( f0 + f1 ) / 2;
688  }
689  else
690  {
691  f0 = f1;
692  fE0 = mfMSE;
693  f1 = ( f1 + f2 ) / 2;
694  }
695  mfBeta = f1;
696  CalcGamma();
697  refill();
698  }
699  if ( fE2 > fE0 )
700  {
701  if ( fE0 < mfMSE )
702  {
703  mfBeta = f0;
704  CalcGamma();
705  refill();
706  }
707  }
708  else
709  {
710  if ( fE2 < mfMSE )
711  {
712  mfBeta = f2;
713  CalcGamma();
714  refill();
715  }
716  }
717 }
718 
719 void ScETSForecastCalculation::CalcGamma()
720 {
721  double f0 = 0.0;
722  mfGamma = f0;
723  refill();
724  double fE0 = mfMSE;
725 
726  double f2 = 1.0;
727  mfGamma = f2;
728  refill();
729  double fE2 = mfMSE;
730 
731  double f1 = 0.5;
732  mfGamma = f1;
733  refill();
734 
735  if ( fE0 == mfMSE && mfMSE == fE2 )
736  {
737  mfGamma = 0;
738  refill();
739  return;
740  }
741  while ( ( f2 - f1 ) > cfMinABCResolution )
742  {
743  if ( fE2 > fE0 )
744  {
745  f2 = f1;
746  fE2 = mfMSE;
747  f1 = ( f0 + f1 ) / 2;
748  }
749  else
750  {
751  f0 = f1;
752  fE0 = mfMSE;
753  f1 = ( f1 + f2 ) / 2;
754  }
755  mfGamma = f1;
756  refill();
757  }
758  if ( fE2 > fE0 )
759  {
760  if ( fE0 < mfMSE )
761  {
762  mfGamma = f0;
763  refill();
764  }
765  }
766  else
767  {
768  if ( fE2 < mfMSE )
769  {
770  mfGamma = f2;
771  refill();
772  }
773  }
774 }
775 
776 void ScETSForecastCalculation::refill()
777 {
778  // refill mpBase, mpTrend, mpPerIdx and mpForecast with values
779  // using the calculated mfAlpha, (mfBeta), mfGamma
780  // forecast 1 step ahead
781  for ( SCSIZE i = 1; i < mnCount; i++ )
782  {
783  if ( bEDS )
784  {
785  mpBase[ i ] = mfAlpha * maRange[ i ].Y +
786  ( 1 - mfAlpha ) * ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] );
787  mpTrend[ i ] = mfGamma * ( mpBase[ i ] - mpBase[ i - 1 ] ) +
788  ( 1 - mfGamma ) * mpTrend[ i - 1 ];
789  mpForecast[ i ] = mpBase[ i - 1 ] + mpTrend[ i - 1 ];
790  }
791  else
792  {
793  SCSIZE nIdx;
794  if ( bAdditive )
795  {
796  nIdx = ( i > mnSmplInPrd ? i - mnSmplInPrd : i );
797  mpBase[ i ] = mfAlpha * ( maRange[ i ].Y - mpPerIdx[ nIdx ] ) +
798  ( 1 - mfAlpha ) * ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] );
799  mpPerIdx[ i ] = mfBeta * ( maRange[ i ].Y - mpBase[ i ] ) +
800  ( 1 - mfBeta ) * mpPerIdx[ nIdx ];
801  }
802  else
803  {
804  nIdx = ( i >= mnSmplInPrd ? i - mnSmplInPrd : i );
805  mpBase[ i ] = mfAlpha * ( maRange[ i ].Y / mpPerIdx[ nIdx ] ) +
806  ( 1 - mfAlpha ) * ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] );
807  mpPerIdx[ i ] = mfBeta * ( maRange[ i ].Y / mpBase[ i ] ) +
808  ( 1 - mfBeta ) * mpPerIdx[ nIdx ];
809  }
810  mpTrend[ i ] = mfGamma * ( mpBase[ i ] - mpBase[ i - 1 ] ) +
811  ( 1 - mfGamma ) * mpTrend[ i - 1 ];
812 
813  if ( bAdditive )
814  mpForecast[ i ] = mpBase[ i - 1 ] + mpTrend[ i - 1 ] + mpPerIdx[ nIdx ];
815  else
816  mpForecast[ i ] = ( mpBase[ i - 1 ] + mpTrend[ i - 1 ] ) * mpPerIdx[ nIdx ];
817  }
818  }
819  calcAccuracyIndicators();
820 }
821 
822 double ScETSForecastCalculation::convertXtoMonths( double x )
823 {
824  Date aDate = mpFormatter->GetNullDate() + static_cast< sal_Int32 >( x );
825  int nYear = aDate.GetYear();
826  int nMonth = aDate.GetMonth();
827  double fMonthLength;
828  switch ( nMonth )
829  {
830  case 1 :
831  case 3 :
832  case 5 :
833  case 7 :
834  case 8 :
835  case 10 :
836  case 12 :
837  fMonthLength = 31.0;
838  break;
839  case 2 :
840  fMonthLength = ( aDate.IsLeapYear() ? 29.0 : 28.0 );
841  break;
842  default :
843  fMonthLength = 30.0;
844  }
845  return ( 12.0 * nYear + nMonth + ( aDate.GetDay() - mnMonthDay ) / fMonthLength );
846 }
847 
848 void ScETSForecastCalculation::GetForecast( double fTarget, double& rForecast )
849 {
850  initCalc();
851 
852  if ( fTarget <= maRange[ mnCount - 1 ].X )
853  {
854  SCSIZE n = ( fTarget - maRange[ 0 ].X ) / mfStepSize;
855  double fInterpolate = fmod( fTarget - maRange[ 0 ].X, mfStepSize );
856  rForecast = maRange[ n ].Y;
857 
858  if ( fInterpolate >= cfMinABCResolution )
859  {
860  double fInterpolateFactor = fInterpolate / mfStepSize;
861  double fFc_1 = mpForecast[ n + 1 ];
862  rForecast = rForecast + fInterpolateFactor * ( fFc_1 - rForecast );
863  }
864  }
865  else
866  {
867  SCSIZE n = ( fTarget - maRange[ mnCount - 1 ].X ) / mfStepSize;
868  double fInterpolate = fmod( fTarget - maRange[ mnCount - 1 ].X, mfStepSize );
869 
870  if ( bEDS )
871  rForecast = mpBase[ mnCount - 1 ] + n * mpTrend[ mnCount - 1 ];
872  else if ( bAdditive )
873  rForecast = mpBase[ mnCount - 1 ] + n * mpTrend[ mnCount - 1 ] +
874  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( n % mnSmplInPrd ) ];
875  else
876  rForecast = ( mpBase[ mnCount - 1 ] + n * mpTrend[ mnCount - 1 ] ) *
877  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( n % mnSmplInPrd ) ];
878 
879  if ( fInterpolate >= cfMinABCResolution )
880  {
881  double fInterpolateFactor = fInterpolate / mfStepSize;
882  double fFc_1;
883  if ( bEDS )
884  fFc_1 = mpBase[ mnCount - 1 ] + ( n + 1 ) * mpTrend[ mnCount - 1 ];
885  else if ( bAdditive )
886  fFc_1 = mpBase[ mnCount - 1 ] + ( n + 1 ) * mpTrend[ mnCount - 1 ] +
887  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( ( n + 1 ) % mnSmplInPrd ) ];
888  else
889  fFc_1 = ( mpBase[ mnCount - 1 ] + ( n + 1 ) * mpTrend[ mnCount - 1 ] ) *
890  mpPerIdx[ mnCount - 1 - mnSmplInPrd + ( ( n + 1 ) % mnSmplInPrd ) ];
891  rForecast = rForecast + fInterpolateFactor * ( fFc_1 - rForecast );
892  }
893  }
894 }
895 
896 void ScETSForecastCalculation::GetForecastRange( const ScMatrixRef& rTMat, const ScMatrixRef& rFcMat )
897 {
898  SCSIZE nC, nR;
899  rTMat->GetDimensions( nC, nR );
900 
901  for ( SCSIZE i = 0; i < nR; i++ )
902  {
903  for ( SCSIZE j = 0; j < nC; j++ )
904  {
905  double fTarget;
906  if ( mnMonthDay )
907  fTarget = convertXtoMonths( rTMat->GetDouble( j, i ) );
908  else
909  fTarget = rTMat->GetDouble( j, i );
910  double fForecast;
911  GetForecast( fTarget, fForecast );
912  rFcMat->PutDouble( fForecast, j, i );
913  }
914  }
915 }
916 
917 void ScETSForecastCalculation::GetStatisticValue( const ScMatrixRef& rTypeMat, const ScMatrixRef& rStatMat )
918 {
919  initCalc();
920 
921  SCSIZE nC, nR;
922  rTypeMat->GetDimensions( nC, nR );
923  for ( SCSIZE i = 0; i < nR; i++ )
924  {
925  for ( SCSIZE j = 0; j < nC; j++ )
926  {
927  switch ( static_cast< int >( rTypeMat->GetDouble( j, i ) ) )
928  {
929  case 1 : // alpha
930  rStatMat->PutDouble( mfAlpha, j, i );
931  break;
932  case 2 : // gamma
933  rStatMat->PutDouble( mfGamma, j, i );
934  break;
935  case 3 : // beta
936  rStatMat->PutDouble( mfBeta, j, i );
937  break;
938  case 4 : // MASE
939  rStatMat->PutDouble( mfMASE, j, i );
940  break;
941  case 5 : // SMAPE
942  rStatMat->PutDouble( mfSMAPE, j, i );
943  break;
944  case 6 : // MAE
945  rStatMat->PutDouble( mfMAE, j, i );
946  break;
947  case 7 : // RMSE
948  rStatMat->PutDouble( mfRMSE, j, i );
949  break;
950  case 8 : // step size
951  rStatMat->PutDouble( mfStepSize, j, i );
952  break;
953  case 9 : // samples in period
954  rStatMat->PutDouble( mnSmplInPrd, j, i );
955  break;
956  }
957  }
958  }
959 }
960 
961 void ScETSForecastCalculation::GetSamplesInPeriod( double& rVal )
962 {
963  rVal = mnSmplInPrd;
964 }
965 
966 double ScETSForecastCalculation::RandDev()
967 {
968  // return a random deviation given the standard deviation
969  return ( mfRMSE * ScInterpreter::gaussinv(
971 }
972 
973 void ScETSForecastCalculation::GetETSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel )
974 {
975  initCalc();
976 
977  SCSIZE nC, nR;
978  rTMat->GetDimensions( nC, nR );
979 
980  // find maximum target value and calculate size of scenario-arrays
981  double fMaxTarget = rTMat->GetDouble( 0, 0 );
982  for ( SCSIZE i = 0; i < nR; i++ )
983  {
984  for ( SCSIZE j = 0; j < nC; j++ )
985  {
986  if ( fMaxTarget < rTMat->GetDouble( j, i ) )
987  fMaxTarget = rTMat->GetDouble( j, i );
988  }
989  }
990  if ( mnMonthDay )
991  fMaxTarget = convertXtoMonths( fMaxTarget ) - maRange[ mnCount - 1 ].X;
992  else
993  fMaxTarget -= maRange[ mnCount - 1 ].X;
994  SCSIZE nSize = fMaxTarget / mfStepSize;
995  if ( fmod( fMaxTarget, mfStepSize ) != 0.0 )
996  nSize++;
997 
998  if (nSize == 0)
999  {
1000  mnErrorValue = FormulaError::IllegalArgument;
1001  return;
1002  }
1003 
1004  std::unique_ptr< double[] > xScenRange( new double[nSize]);
1005  std::unique_ptr< double[] > xScenBase( new double[nSize]);
1006  std::unique_ptr< double[] > xScenTrend( new double[nSize]);
1007  std::unique_ptr< double[] > xScenPerIdx( new double[nSize]);
1008  std::vector< std::vector< double > > aPredictions( nSize, std::vector< double >( cnScenarios ) );
1009 
1010  // fill scenarios
1011  for ( SCSIZE k = 0; k < cnScenarios; k++ )
1012  {
1013  // fill array with forecasts, with RandDev() added to xScenRange
1014  if ( bAdditive )
1015  {
1016  double nPIdx = !bEDS ? mpPerIdx[mnCount - mnSmplInPrd] : 0.0;
1017  // calculation based on additive model
1018  xScenRange[ 0 ] = mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] +
1019  nPIdx +
1020  RandDev();
1021  aPredictions[ 0 ][ k ] = xScenRange[ 0 ];
1022  xScenBase[ 0 ] = mfAlpha * ( xScenRange[ 0 ] - nPIdx ) +
1023  ( 1 - mfAlpha ) * ( mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] );
1024  xScenTrend[ 0 ] = mfGamma * ( xScenBase[ 0 ] - mpBase[ mnCount - 1 ] ) +
1025  ( 1 - mfGamma ) * mpTrend[ mnCount - 1 ];
1026  xScenPerIdx[ 0 ] = mfBeta * ( xScenRange[ 0 ] - xScenBase[ 0 ] ) +
1027  ( 1 - mfBeta ) * nPIdx;
1028  for ( SCSIZE i = 1; i < nSize; i++ )
1029  {
1030  double fPerIdx;
1031  if ( i < mnSmplInPrd )
1032  fPerIdx = mpPerIdx[ mnCount + i - mnSmplInPrd ];
1033  else
1034  fPerIdx = xScenPerIdx[ i - mnSmplInPrd ];
1035  xScenRange[ i ] = xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] + fPerIdx + RandDev();
1036  aPredictions[ i ][ k ] = xScenRange[ i ];
1037  xScenBase[ i ] = mfAlpha * ( xScenRange[ i ] - fPerIdx ) +
1038  ( 1 - mfAlpha ) * ( xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] );
1039  xScenTrend[ i ] = mfGamma * ( xScenBase[ i ] - xScenBase[ i - 1 ] ) +
1040  ( 1 - mfGamma ) * xScenTrend[ i - 1 ];
1041  xScenPerIdx[ i ] = mfBeta * ( xScenRange[ i ] - xScenBase[ i ] ) +
1042  ( 1 - mfBeta ) * fPerIdx;
1043  }
1044  }
1045  else
1046  {
1047  // calculation based on multiplicative model
1048  xScenRange[ 0 ] = ( mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] ) *
1049  mpPerIdx[ mnCount - mnSmplInPrd ] +
1050  RandDev();
1051  aPredictions[ 0 ][ k ] = xScenRange[ 0 ];
1052  xScenBase[ 0 ] = mfAlpha * ( xScenRange[ 0 ] / mpPerIdx[ mnCount - mnSmplInPrd ] ) +
1053  ( 1 - mfAlpha ) * ( mpBase[ mnCount - 1 ] + mpTrend[ mnCount - 1 ] );
1054  xScenTrend[ 0 ] = mfGamma * ( xScenBase[ 0 ] - mpBase[ mnCount - 1 ] ) +
1055  ( 1 - mfGamma ) * mpTrend[ mnCount - 1 ];
1056  xScenPerIdx[ 0 ] = mfBeta * ( xScenRange[ 0 ] / xScenBase[ 0 ] ) +
1057  ( 1 - mfBeta ) * mpPerIdx[ mnCount - mnSmplInPrd ];
1058  for ( SCSIZE i = 1; i < nSize; i++ )
1059  {
1060  double fPerIdx;
1061  if ( i < mnSmplInPrd )
1062  fPerIdx = mpPerIdx[ mnCount + i - mnSmplInPrd ];
1063  else
1064  fPerIdx = xScenPerIdx[ i - mnSmplInPrd ];
1065  xScenRange[ i ] = ( xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] ) * fPerIdx + RandDev();
1066  aPredictions[ i ][ k ] = xScenRange[ i ];
1067  xScenBase[ i ] = mfAlpha * ( xScenRange[ i ] / fPerIdx ) +
1068  ( 1 - mfAlpha ) * ( xScenBase[ i - 1 ] + xScenTrend[ i - 1 ] );
1069  xScenTrend[ i ] = mfGamma * ( xScenBase[ i ] - xScenBase[ i - 1 ] ) +
1070  ( 1 - mfGamma ) * xScenTrend[ i - 1 ];
1071  xScenPerIdx[ i ] = mfBeta * ( xScenRange[ i ] / xScenBase[ i ] ) +
1072  ( 1 - mfBeta ) * fPerIdx;
1073  }
1074  }
1075  }
1076 
1077  // create array of Percentile values;
1078  std::unique_ptr< double[] > xPercentile( new double[nSize]);
1079  for ( SCSIZE i = 0; i < nSize; i++ )
1080  {
1081  xPercentile[ i ] = ScInterpreter::GetPercentile( aPredictions[ i ], ( 1 + fPILevel ) / 2 ) -
1082  ScInterpreter::GetPercentile( aPredictions[ i ], 0.5 );
1083  }
1084 
1085  for ( SCSIZE i = 0; i < nR; i++ )
1086  {
1087  for ( SCSIZE j = 0; j < nC; j++ )
1088  {
1089  double fTarget;
1090  if ( mnMonthDay )
1091  fTarget = convertXtoMonths( rTMat->GetDouble( j, i ) ) - maRange[ mnCount - 1 ].X;
1092  else
1093  fTarget = rTMat->GetDouble( j, i ) - maRange[ mnCount - 1 ].X;
1094  SCSIZE nSteps = ( fTarget / mfStepSize ) - 1;
1095  double fFactor = fmod( fTarget, mfStepSize );
1096  double fPI = xPercentile[ nSteps ];
1097  if ( fFactor != 0.0 )
1098  {
1099  // interpolate
1100  double fPI1 = xPercentile[ nSteps + 1 ];
1101  fPI = fPI + fFactor * ( fPI1 - fPI );
1102  }
1103  rPIMat->PutDouble( fPI, j, i );
1104  }
1105  }
1106 }
1107 
1108 
1109 void ScETSForecastCalculation::GetEDSPredictionIntervals( const ScMatrixRef& rTMat, const ScMatrixRef& rPIMat, double fPILevel )
1110 {
1111  initCalc();
1112 
1113  SCSIZE nC, nR;
1114  rTMat->GetDimensions( nC, nR );
1115 
1116  // find maximum target value and calculate size of coefficient- array c
1117  double fMaxTarget = rTMat->GetDouble( 0, 0 );
1118  for ( SCSIZE i = 0; i < nR; i++ )
1119  {
1120  for ( SCSIZE j = 0; j < nC; j++ )
1121  {
1122  if ( fMaxTarget < rTMat->GetDouble( j, i ) )
1123  fMaxTarget = rTMat->GetDouble( j, i );
1124  }
1125  }
1126  if ( mnMonthDay )
1127  fMaxTarget = convertXtoMonths( fMaxTarget ) - maRange[ mnCount - 1 ].X;
1128  else
1129  fMaxTarget -= maRange[ mnCount - 1 ].X;
1130  SCSIZE nSize = fMaxTarget / mfStepSize;
1131  if ( fmod( fMaxTarget, mfStepSize ) != 0.0 )
1132  nSize++;
1133 
1134  if (nSize == 0)
1135  {
1136  mnErrorValue = FormulaError::IllegalArgument;
1137  return;
1138  }
1139 
1140  double z = ScInterpreter::gaussinv( ( 1.0 + fPILevel ) / 2.0 );
1141  double o = 1 - fPILevel;
1142  std::vector< double > c( nSize );
1143  for ( SCSIZE i = 0; i < nSize; i++ )
1144  {
1145  c[ i ] = sqrt( 1 + ( fPILevel / pow( 1 + o, 3.0 ) ) *
1146  ( ( 1 + 4 * o + 5 * o * o ) +
1147  2 * static_cast< double >( i ) * fPILevel * ( 1 + 3 * o ) +
1148  2 * static_cast< double >( i * i ) * fPILevel * fPILevel ) );
1149  }
1150 
1151 
1152  for ( SCSIZE i = 0; i < nR; i++ )
1153  {
1154  for ( SCSIZE j = 0; j < nC; j++ )
1155  {
1156  double fTarget;
1157  if ( mnMonthDay )
1158  fTarget = convertXtoMonths( rTMat->GetDouble( j, i ) ) - maRange[ mnCount - 1 ].X;
1159  else
1160  fTarget = rTMat->GetDouble( j, i ) - maRange[ mnCount - 1 ].X;
1161  SCSIZE nSteps = ( fTarget / mfStepSize ) - 1;
1162  double fFactor = fmod( fTarget, mfStepSize );
1163  double fPI = z * mfRMSE * c[ nSteps ] / c[ 0 ];
1164  if ( fFactor != 0.0 )
1165  {
1166  // interpolate
1167  double fPI1 = z * mfRMSE * c[ nSteps + 1 ] / c[ 0 ];
1168  fPI = fPI + fFactor * ( fPI1 - fPI );
1169  }
1170  rPIMat->PutDouble( fPI, j, i );
1171  }
1172  }
1173 }
1174 
1175 
1177 {
1179  switch ( eETSType )
1180  {
1181  case etsAdd :
1182  case etsMult :
1183  case etsStatAdd :
1184  case etsStatMult :
1185  if ( !MustHaveParamCount( nParamCount, 3, 6 ) )
1186  return;
1187  break;
1188  case etsPIAdd :
1189  case etsPIMult :
1190  if ( !MustHaveParamCount( nParamCount, 3, 7 ) )
1191  {
1192  return;
1193  }
1194  break;
1195  case etsSeason :
1196  if ( !MustHaveParamCount( nParamCount, 2, 4 ) )
1197  return;
1198  break;
1199  }
1200 
1201  int nAggregation;
1202  if ( ( nParamCount == 6 && eETSType != etsPIAdd && eETSType != etsPIMult ) ||
1203  ( nParamCount == 4 && eETSType == etsSeason ) ||
1204  nParamCount == 7 )
1205  nAggregation = static_cast< int >( GetDoubleWithDefault( 1.0 ) );
1206  else
1207  nAggregation = 1;
1208  if ( nAggregation < 1 || nAggregation > 7 )
1209  {
1211  return;
1212  }
1213 
1214  bool bDataCompletion;
1215  if ( ( nParamCount >= 5 && eETSType != etsPIAdd && eETSType != etsPIMult ) ||
1216  ( nParamCount >= 3 && eETSType == etsSeason ) ||
1217  ( nParamCount >= 6 && ( eETSType == etsPIAdd || eETSType == etsPIMult ) ) )
1218  {
1219  int nTemp = static_cast< int >( GetDoubleWithDefault( 1.0 ) );
1220  if ( nTemp == 0 || nTemp == 1 )
1221  bDataCompletion = nTemp;
1222  else
1223  {
1225  return;
1226  }
1227  }
1228  else
1229  bDataCompletion = true;
1230 
1231  int nSmplInPrd;
1232  if ( ( ( nParamCount >= 4 && eETSType != etsPIAdd && eETSType != etsPIMult ) ||
1233  ( nParamCount >= 5 && ( eETSType == etsPIAdd || eETSType == etsPIMult ) ) ) &&
1234  eETSType != etsSeason )
1235  {
1236  double fVal = GetDoubleWithDefault( 1.0 );
1237  if ( fmod( fVal, 1.0 ) != 0 || fVal < 0.0 )
1238  {
1239  PushError( FormulaError::IllegalFPOperation );
1240  return;
1241  }
1242  nSmplInPrd = static_cast< int >( fVal );
1243  }
1244  else
1245  nSmplInPrd = 1;
1246 
1247  // required arguments
1248  double fPILevel = 0.0;
1249  if ( nParamCount < 3 && ( nParamCount != 2 || eETSType != etsSeason ) )
1250  {
1252  return;
1253  }
1254 
1255  if ( eETSType == etsPIAdd || eETSType == etsPIMult )
1256  {
1257  fPILevel = (nParamCount < 4 ? 0.95 : GetDoubleWithDefault( 0.95 ));
1258  if ( fPILevel < 0 || fPILevel > 1 )
1259  {
1261  return;
1262  }
1263  }
1264 
1265  ScMatrixRef pTypeMat;
1266  if ( eETSType == etsStatAdd || eETSType == etsStatMult )
1267  {
1268  pTypeMat = GetMatrix();
1269  SCSIZE nC, nR;
1270  pTypeMat->GetDimensions( nC, nR );
1271  for ( SCSIZE i = 0; i < nR; i++ )
1272  {
1273  for ( SCSIZE j = 0; j < nC; j++ )
1274  {
1275  if ( static_cast< int >( pTypeMat->GetDouble( j, i ) ) < 1 ||
1276  static_cast< int >( pTypeMat->GetDouble( j, i ) ) > 9 )
1277  {
1279  return;
1280  }
1281  }
1282  }
1283  }
1284 
1285  ScMatrixRef pMatX = GetMatrix();
1286  ScMatrixRef pMatY = GetMatrix();
1287  if ( !pMatX || !pMatY )
1288  {
1290  return;
1291  }
1292  SCSIZE nCX, nCY;
1293  SCSIZE nRX, nRY;
1294  pMatX->GetDimensions( nCX, nRX );
1295  pMatY->GetDimensions( nCY, nRY );
1296  if ( nRX != nRY || nCX != nCY ||
1297  !pMatX->IsNumeric() || !pMatY->IsNumeric() )
1298  {
1300  return;
1301  }
1302 
1303  ScMatrixRef pTMat;
1304  if ( eETSType != etsStatAdd && eETSType != etsStatMult && eETSType != etsSeason )
1305  {
1306  pTMat = GetMatrix();
1307  if ( !pTMat )
1308  {
1310  return;
1311  }
1312  }
1313 
1314  ScETSForecastCalculation aETSCalc( pMatX->GetElementCount(), pFormatter );
1315  if ( !aETSCalc.PreprocessDataRange( pMatX, pMatY, nSmplInPrd, bDataCompletion,
1316  nAggregation,
1317  ( eETSType != etsStatAdd && eETSType != etsStatMult ? pTMat : nullptr ),
1318  eETSType ) )
1319  {
1320  PushError( aETSCalc.GetError() );
1321  return;
1322  }
1323 
1324  switch ( eETSType )
1325  {
1326  case etsAdd :
1327  case etsMult :
1328  {
1329  SCSIZE nC, nR;
1330  pTMat->GetDimensions( nC, nR );
1331  ScMatrixRef pFcMat = GetNewMat( nC, nR, /*bEmpty*/true );
1332  aETSCalc.GetForecastRange( pTMat, pFcMat );
1333  if (aETSCalc.GetError() != FormulaError::NONE)
1334  PushError( aETSCalc.GetError()); // explicitly push error, PushMatrix() does not
1335  else
1336  PushMatrix( pFcMat );
1337  }
1338  break;
1339  case etsPIAdd :
1340  case etsPIMult :
1341  {
1342  SCSIZE nC, nR;
1343  pTMat->GetDimensions( nC, nR );
1344  ScMatrixRef pPIMat = GetNewMat( nC, nR, /*bEmpty*/true );
1345  if ( nSmplInPrd == 0 )
1346  {
1347  aETSCalc.GetEDSPredictionIntervals( pTMat, pPIMat, fPILevel );
1348  }
1349  else
1350  {
1351  aETSCalc.GetETSPredictionIntervals( pTMat, pPIMat, fPILevel );
1352  }
1353  if (aETSCalc.GetError() != FormulaError::NONE)
1354  PushError( aETSCalc.GetError()); // explicitly push error, PushMatrix() does not
1355  else
1356  PushMatrix( pPIMat );
1357  }
1358  break;
1359  case etsStatAdd :
1360  case etsStatMult :
1361  {
1362  SCSIZE nC, nR;
1363  pTypeMat->GetDimensions( nC, nR );
1364  ScMatrixRef pStatMat = GetNewMat( nC, nR, /*bEmpty*/true );
1365  aETSCalc.GetStatisticValue( pTypeMat, pStatMat );
1366  if (aETSCalc.GetError() != FormulaError::NONE)
1367  PushError( aETSCalc.GetError()); // explicitly push error, PushMatrix() does not
1368  else
1369  PushMatrix( pStatMat );
1370  }
1371  break;
1372  case etsSeason :
1373  {
1374  double rVal;
1375  aETSCalc.GetSamplesInPeriod( rVal );
1376  SetError( aETSCalc.GetError() );
1377  PushDouble( rVal );
1378  }
1379  break;
1380  }
1381 }
1382 
1384 {
1385  OUStringBuffer aResBuf;
1386  short nParamCount = GetByte();
1387 
1388  //reverse order of parameter stack to simplify concatenation:
1389  ReverseStack( nParamCount );
1390 
1391  size_t nRefInList = 0;
1392  while ( nParamCount-- > 0 && nGlobalError == FormulaError::NONE )
1393  {
1394  switch ( GetStackType() )
1395  {
1396  case svString:
1397  case svDouble:
1398  {
1399  const OUString& rStr = GetString().getString();
1400  if (CheckStringResultLen( aResBuf, rStr))
1401  aResBuf.append( rStr);
1402  }
1403  break;
1404  case svSingleRef :
1405  {
1406  ScAddress aAdr;
1407  PopSingleRef( aAdr );
1408  if ( nGlobalError != FormulaError::NONE )
1409  break;
1410  ScRefCellValue aCell( mrDoc, aAdr );
1411  if ( !aCell.isEmpty() )
1412  {
1413  if ( !aCell.hasEmptyValue() )
1414  {
1415  svl::SharedString aSS;
1416  GetCellString( aSS, aCell);
1417  const OUString& rStr = aSS.getString();
1418  if (CheckStringResultLen( aResBuf, rStr))
1419  aResBuf.append( rStr);
1420  }
1421  }
1422  }
1423  break;
1424  case svDoubleRef :
1425  case svRefList :
1426  {
1427  ScRange aRange;
1428  PopDoubleRef( aRange, nParamCount, nRefInList);
1429  if ( nGlobalError != FormulaError::NONE )
1430  break;
1431  // we need to read row for row, so we can't use ScCellIter
1432  SCCOL nCol1, nCol2;
1433  SCROW nRow1, nRow2;
1434  SCTAB nTab1, nTab2;
1435  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1436  if ( nTab1 != nTab2 )
1437  {
1438  SetError( FormulaError::IllegalParameter);
1439  break;
1440  }
1441  if ( nRow1 > nRow2 )
1442  std::swap( nRow1, nRow2 );
1443  if ( nCol1 > nCol2 )
1444  std::swap( nCol1, nCol2 );
1445  ScAddress aAdr;
1446  aAdr.SetTab( nTab1 );
1447  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1448  {
1449  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1450  {
1451  aAdr.SetRow( nRow );
1452  aAdr.SetCol( nCol );
1453  ScRefCellValue aCell( mrDoc, aAdr );
1454  if ( !aCell.isEmpty() )
1455  {
1456  if ( !aCell.hasEmptyValue() )
1457  {
1458  svl::SharedString aSS;
1459  GetCellString( aSS, aCell);
1460  const OUString& rStr = aSS.getString();
1461  if (CheckStringResultLen( aResBuf, rStr))
1462  aResBuf.append( rStr);
1463  }
1464  }
1465  }
1466  }
1467  }
1468  break;
1469  case svMatrix :
1470  case svExternalSingleRef:
1471  case svExternalDoubleRef:
1472  {
1473  ScMatrixRef pMat = GetMatrix();
1474  if (pMat)
1475  {
1476  SCSIZE nC, nR;
1477  pMat->GetDimensions(nC, nR);
1478  if (nC == 0 || nR == 0)
1479  SetError(FormulaError::IllegalArgument);
1480  else
1481  {
1482  for (SCSIZE k = 0; k < nR; ++k)
1483  {
1484  for (SCSIZE j = 0; j < nC; ++j)
1485  {
1486  if ( pMat->IsStringOrEmpty( j, k ) )
1487  {
1488  const OUString& rStr = pMat->GetString( j, k ).getString();
1489  if (CheckStringResultLen( aResBuf, rStr))
1490  aResBuf.append( rStr);
1491  }
1492  else
1493  {
1494  if ( pMat->IsValue( j, k ) )
1495  {
1496  const OUString& rStr = pMat->GetString( *pFormatter, j, k ).getString();
1497  if (CheckStringResultLen( aResBuf, rStr))
1498  aResBuf.append( rStr);
1499  }
1500  }
1501  }
1502  }
1503  }
1504  }
1505  }
1506  break;
1507  default:
1508  PopError();
1509  SetError( FormulaError::IllegalArgument);
1510  break;
1511  }
1512  }
1513  PushString( aResBuf.makeStringAndClear() );
1514 }
1515 
1517 {
1518  short nParamCount = GetByte();
1519 
1520  if ( !MustHaveParamCountMin( nParamCount, 3 ) )
1521  return;
1522 
1523  //reverse order of parameter stack to simplify processing
1524  ReverseStack( nParamCount );
1525 
1526  // get aDelimiters and bSkipEmpty
1527  std::vector< OUString > aDelimiters;
1528  size_t nRefInList = 0;
1529  switch ( GetStackType() )
1530  {
1531  case svString:
1532  case svDouble:
1533  aDelimiters.push_back( GetString().getString() );
1534  break;
1535  case svSingleRef :
1536  {
1537  ScAddress aAdr;
1538  PopSingleRef( aAdr );
1539  if ( nGlobalError != FormulaError::NONE )
1540  break;
1541  ScRefCellValue aCell( mrDoc, aAdr );
1542  if ( !aCell.isEmpty() )
1543  {
1544  if ( !aCell.hasEmptyValue() )
1545  {
1546  svl::SharedString aSS;
1547  GetCellString( aSS, aCell);
1548  aDelimiters.push_back( aSS.getString());
1549  }
1550  }
1551  }
1552  break;
1553  case svDoubleRef :
1554  case svRefList :
1555  {
1556  ScRange aRange;
1557  PopDoubleRef( aRange, nParamCount, nRefInList);
1558  if ( nGlobalError != FormulaError::NONE )
1559  break;
1560  // we need to read row for row, so we can't use ScCellIterator
1561  SCCOL nCol1, nCol2;
1562  SCROW nRow1, nRow2;
1563  SCTAB nTab1, nTab2;
1564  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1565  if ( nTab1 != nTab2 )
1566  {
1567  SetError( FormulaError::IllegalParameter);
1568  break;
1569  }
1570  if ( nRow1 > nRow2 )
1571  std::swap( nRow1, nRow2 );
1572  if ( nCol1 > nCol2 )
1573  std::swap( nCol1, nCol2 );
1574  ScAddress aAdr;
1575  aAdr.SetTab( nTab1 );
1576  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1577  {
1578  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1579  {
1580  aAdr.SetRow( nRow );
1581  aAdr.SetCol( nCol );
1582  ScRefCellValue aCell( mrDoc, aAdr );
1583  if ( !aCell.isEmpty() )
1584  {
1585  if ( !aCell.hasEmptyValue() )
1586  {
1587  svl::SharedString aSS;
1588  GetCellString( aSS, aCell);
1589  aDelimiters.push_back( aSS.getString());
1590  }
1591  }
1592  else
1593  aDelimiters.emplace_back("" );
1594  }
1595  }
1596  }
1597  break;
1598  case svMatrix :
1599  case svExternalSingleRef:
1600  case svExternalDoubleRef:
1601  {
1602  ScMatrixRef pMat = GetMatrix();
1603  if (pMat)
1604  {
1605  SCSIZE nC, nR;
1606  pMat->GetDimensions(nC, nR);
1607  if (nC == 0 || nR == 0)
1608  SetError(FormulaError::IllegalArgument);
1609  else
1610  {
1611  for (SCSIZE k = 0; k < nR; ++k)
1612  {
1613  for (SCSIZE j = 0; j < nC; ++j)
1614  {
1615  if ( !pMat->IsEmpty( j, k ) )
1616  {
1617  if ( pMat->IsStringOrEmpty( j, k ) )
1618  aDelimiters.push_back( pMat->GetString( j, k ).getString() );
1619  else
1620  {
1621  if ( pMat->IsValue( j, k ) )
1622  aDelimiters.push_back( pMat->GetString( *pFormatter, j, k ).getString() );
1623  }
1624  }
1625  else
1626  aDelimiters.emplace_back("" );
1627  }
1628  }
1629  }
1630  }
1631  }
1632  break;
1633  default:
1634  PopError();
1635  SetError( FormulaError::IllegalArgument);
1636  break;
1637  }
1638  if ( aDelimiters.empty() )
1639  {
1641  return;
1642  }
1643  SCSIZE nSize = aDelimiters.size();
1644  bool bSkipEmpty = static_cast< bool >( GetDouble() );
1645  nParamCount -= 2;
1646 
1647  OUStringBuffer aResBuf;
1648  bool bFirst = true;
1649  SCSIZE nIdx = 0;
1650  nRefInList = 0;
1651  // get the strings to be joined
1652  while ( nParamCount-- > 0 && nGlobalError == FormulaError::NONE )
1653  {
1654  switch ( GetStackType() )
1655  {
1656  case svString:
1657  case svDouble:
1658  {
1659  OUString aStr = GetString().getString();
1660  if ( !aStr.isEmpty() || !bSkipEmpty )
1661  {
1662  if ( !bFirst )
1663  {
1664  aResBuf.append( aDelimiters[ nIdx ] );
1665  if ( nSize > 1 )
1666  {
1667  if ( ++nIdx >= nSize )
1668  nIdx = 0;
1669  }
1670  }
1671  else
1672  bFirst = false;
1673  if (CheckStringResultLen( aResBuf, aStr))
1674  aResBuf.append( aStr );
1675  }
1676  }
1677  break;
1678  case svSingleRef :
1679  {
1680  ScAddress aAdr;
1681  PopSingleRef( aAdr );
1682  if ( nGlobalError != FormulaError::NONE )
1683  break;
1684  ScRefCellValue aCell( mrDoc, aAdr );
1685  OUString aStr;
1686  if ( !aCell.isEmpty() )
1687  {
1688  if ( !aCell.hasEmptyValue() )
1689  {
1690  svl::SharedString aSS;
1691  GetCellString( aSS, aCell);
1692  aStr = aSS.getString();
1693  }
1694  }
1695  else
1696  aStr.clear();
1697  if ( !aStr.isEmpty() || !bSkipEmpty )
1698  {
1699  if ( !bFirst )
1700  {
1701  aResBuf.append( aDelimiters[ nIdx ] );
1702  if ( nSize > 1 )
1703  {
1704  if ( ++nIdx >= nSize )
1705  nIdx = 0;
1706  }
1707  }
1708  else
1709  bFirst = false;
1710  if (CheckStringResultLen( aResBuf, aStr))
1711  aResBuf.append( aStr );
1712  }
1713  }
1714  break;
1715  case svDoubleRef :
1716  case svRefList :
1717  {
1718  ScRange aRange;
1719  PopDoubleRef( aRange, nParamCount, nRefInList);
1720  if ( nGlobalError != FormulaError::NONE )
1721  break;
1722  // we need to read row for row, so we can't use ScCellIterator
1723  SCCOL nCol1, nCol2;
1724  SCROW nRow1, nRow2;
1725  SCTAB nTab1, nTab2;
1726  aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
1727  if ( nTab1 != nTab2 )
1728  {
1729  SetError( FormulaError::IllegalParameter);
1730  break;
1731  }
1732  if ( nRow1 > nRow2 )
1733  std::swap( nRow1, nRow2 );
1734  if ( nCol1 > nCol2 )
1735  std::swap( nCol1, nCol2 );
1736  ScAddress aAdr;
1737  aAdr.SetTab( nTab1 );
1738  OUString aStr;
1739  for ( SCROW nRow = nRow1; nRow <= nRow2; nRow++ )
1740  {
1741  for ( SCCOL nCol = nCol1; nCol <= nCol2; nCol++ )
1742  {
1743  aAdr.SetRow( nRow );
1744  aAdr.SetCol( nCol );
1745  ScRefCellValue aCell( mrDoc, aAdr );
1746  if ( !aCell.isEmpty() )
1747  {
1748  if ( !aCell.hasEmptyValue() )
1749  {
1750  svl::SharedString aSS;
1751  GetCellString( aSS, aCell);
1752  aStr = aSS.getString();
1753  }
1754  }
1755  else
1756  aStr.clear();
1757  if ( !aStr.isEmpty() || !bSkipEmpty )
1758  {
1759  if ( !bFirst )
1760  {
1761  aResBuf.append( aDelimiters[ nIdx ] );
1762  if ( nSize > 1 )
1763  {
1764  if ( ++nIdx >= nSize )
1765  nIdx = 0;
1766  }
1767  }
1768  else
1769  bFirst = false;
1770  if (CheckStringResultLen( aResBuf, aStr))
1771  aResBuf.append( aStr );
1772  }
1773  }
1774  }
1775  }
1776  break;
1777  case svMatrix :
1778  case svExternalSingleRef:
1779  case svExternalDoubleRef:
1780  {
1781  ScMatrixRef pMat = GetMatrix();
1782  if (pMat)
1783  {
1784  SCSIZE nC, nR;
1785  pMat->GetDimensions(nC, nR);
1786  if (nC == 0 || nR == 0)
1787  SetError(FormulaError::IllegalArgument);
1788  else
1789  {
1790  OUString aStr;
1791  for (SCSIZE k = 0; k < nR; ++k)
1792  {
1793  for (SCSIZE j = 0; j < nC; ++j)
1794  {
1795  if ( !pMat->IsEmpty( j, k ) )
1796  {
1797  if ( pMat->IsStringOrEmpty( j, k ) )
1798  aStr = pMat->GetString( j, k ).getString();
1799  else
1800  {
1801  if ( pMat->IsValue( j, k ) )
1802  aStr = pMat->GetString( *pFormatter, j, k ).getString();
1803  }
1804  }
1805  else
1806  aStr.clear();
1807  if ( !aStr.isEmpty() || !bSkipEmpty )
1808  {
1809  if ( !bFirst )
1810  {
1811  aResBuf.append( aDelimiters[ nIdx ] );
1812  if ( nSize > 1 )
1813  {
1814  if ( ++nIdx >= nSize )
1815  nIdx = 0;
1816  }
1817  }
1818  else
1819  bFirst = false;
1820  if (CheckStringResultLen( aResBuf, aStr))
1821  aResBuf.append( aStr );
1822  }
1823  }
1824  }
1825  }
1826  }
1827  }
1828  break;
1829  case svMissing :
1830  {
1831  if ( !bSkipEmpty )
1832  {
1833  if ( !bFirst )
1834  {
1835  aResBuf.append( aDelimiters[ nIdx ] );
1836  if ( nSize > 1 )
1837  {
1838  if ( ++nIdx >= nSize )
1839  nIdx = 0;
1840  }
1841  }
1842  else
1843  bFirst = false;
1844  }
1845  }
1846  break;
1847  default:
1848  PopError();
1849  SetError( FormulaError::IllegalArgument);
1850  break;
1851  }
1852  }
1853  PushString( aResBuf.makeStringAndClear() );
1854 }
1855 
1856 
1858 {
1859  short nParamCount = GetByte();
1860 
1861  ReverseStack( nParamCount );
1862 
1863  nGlobalError = FormulaError::NONE; // propagate only for condition or active result path
1864  bool bFinished = false;
1865  while ( nParamCount > 0 && !bFinished && nGlobalError == FormulaError::NONE )
1866  {
1867  bool bVal = GetBool();
1868  nParamCount--;
1869  if ( bVal )
1870  {
1871  // TRUE
1872  if ( nParamCount < 1 )
1873  {
1874  // no parameter given for THEN
1876  return;
1877  }
1878  bFinished = true;
1879  }
1880  else
1881  {
1882  // FALSE
1883  if ( nParamCount >= 3 )
1884  {
1885  // ELSEIF path
1886  Pop();
1887  nParamCount--;
1888  }
1889  else
1890  {
1891  // no parameter given for ELSE
1892  PushNA();
1893  return;
1894  }
1895  }
1896  }
1897 
1898  if ( nGlobalError != FormulaError::NONE || !bFinished )
1899  {
1900  if ( !bFinished )
1901  PushNA(); // no true expression found
1902  if ( nGlobalError != FormulaError::NONE )
1903  PushNoValue(); // expression returned something other than true or false
1904  return;
1905  }
1906 
1907  //push result :
1908  FormulaConstTokenRef xToken( PopToken() );
1909  if ( xToken )
1910  {
1911  // Remove unused arguments of IFS from the stack before pushing the result.
1912  while ( nParamCount > 1 )
1913  {
1914  Pop();
1915  nParamCount--;
1916  }
1917  PushTokenRef( xToken );
1918  }
1919  else
1920  PushError( FormulaError::UnknownStackVariable );
1921 }
1922 
1923 
1925 {
1926  short nParamCount = GetByte();
1927 
1928  if (!MustHaveParamCountMin( nParamCount, 3))
1929  return;
1930 
1931  ReverseStack( nParamCount );
1932 
1933  nGlobalError = FormulaError::NONE; // propagate only for match or active result path
1934  bool isValue = false;
1935  double fRefVal = 0;
1936  svl::SharedString aRefStr;
1937  switch ( GetStackType() )
1938  {
1939  case svDouble:
1940  isValue = true;
1941  fRefVal = GetDouble();
1942  break;
1943  case svString:
1944  isValue = false;
1945  aRefStr = GetString();
1946  break;
1947  case svSingleRef :
1948  case svDoubleRef :
1949  {
1950  ScAddress aAdr;
1951  if (!PopDoubleRefOrSingleRef( aAdr ))
1952  break;
1953  ScRefCellValue aCell( mrDoc, aAdr );
1954  isValue = !( aCell.hasString() || aCell.hasEmptyValue() || aCell.isEmpty() );
1955  if ( isValue )
1956  fRefVal = GetCellValue( aAdr, aCell);
1957  else
1958  GetCellString( aRefStr, aCell);
1959  }
1960  break;
1961  case svExternalSingleRef:
1962  case svExternalDoubleRef:
1963  case svMatrix:
1964  isValue = ScMatrix::IsValueType( GetDoubleOrStringFromMatrix( fRefVal, aRefStr ) );
1965  break;
1966  default :
1967  PopError();
1969  return;
1970  }
1971  nParamCount--;
1972  bool bFinished = false;
1973  while ( nParamCount > 1 && !bFinished && nGlobalError == FormulaError::NONE )
1974  {
1975  double fVal = 0;
1977  if ( isValue )
1978  fVal = GetDouble();
1979  else
1980  aStr = GetString();
1981  nParamCount--;
1982  if ((nGlobalError != FormulaError::NONE && nParamCount < 2)
1983  || (isValue && rtl::math::approxEqual( fRefVal, fVal))
1984  || (!isValue && aRefStr.getDataIgnoreCase() == aStr.getDataIgnoreCase()))
1985  {
1986  // TRUE
1987  bFinished = true;
1988  }
1989  else
1990  {
1991  // FALSE
1992  if ( nParamCount >= 2 )
1993  {
1994  // ELSEIF path
1995  Pop();
1996  nParamCount--;
1997  // if nParamCount equals 1: default value to be returned
1998  bFinished = ( nParamCount == 1 );
1999  }
2000  else
2001  {
2002  // no parameter given for ELSE
2003  PushNA();
2004  return;
2005  }
2006  nGlobalError = FormulaError::NONE;
2007  }
2008  }
2009 
2010  if ( nGlobalError != FormulaError::NONE || !bFinished )
2011  {
2012  if ( !bFinished )
2013  PushNA(); // no true expression found
2014  else
2016  return;
2017  }
2018 
2019  // push result
2020  FormulaConstTokenRef xToken( PopToken() );
2021  if ( xToken )
2022  {
2023  // Remove unused arguments of SWITCH from the stack before pushing the result.
2024  while ( nParamCount > 1 )
2025  {
2026  Pop();
2027  nParamCount--;
2028  }
2029  PushTokenRef( xToken );
2030  }
2031  else
2032  PushError( FormulaError::UnknownStackVariable );
2033 }
2034 
2035 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
svString
::boost::intrusive_ptr< const FormulaToken > FormulaConstTokenRef
svRefList
OUString getString() const
void ScSwitch_MS()
Definition: interpr8.cxx:1924
static double GetPercentile(::std::vector< double > &rArray, double fPercentile)
Definition: interpr3.cxx:3401
void PushIllegalParameter()
Definition: interpr4.cxx:1902
rtl_uString * getDataIgnoreCase()
bool isEmpty() const
Definition: cellvalue.cxx:670
void ScTextJoin_MS()
Definition: interpr8.cxx:1516
void ReverseStack(sal_uInt8 nParamCount)
Definition: interpr4.cxx:1973
ScMatrixRef GetMatrix()
Definition: interpr5.cxx:477
sal_Int64 n
void PopSingleRef(ScAddress &)
Definition: interpr4.cxx:902
bool IsLeapYear() const
void ScConcat_MS()
Definition: interpr8.cxx:1383
void ScIfs_MS()
Definition: interpr8.cxx:1857
This is very similar to ScCellValue, except that it references the original value instead of copying ...
Definition: cellvalue.hxx:103
double GetCellValue(const ScAddress &, ScRefCellValue &rCell)
Definition: interpr4.cxx:176
constexpr double get() const
Returns the final sum.
Definition: kahan.hxx:199
double GetDoubleWithDefault(double nDefault)
Definition: interpr4.cxx:2142
bool PopDoubleRefOrSingleRef(ScAddress &rAdr)
Definition: interpr4.cxx:1317
NONE
SvNumberFormatter * pFormatter
Definition: interpre.hxx:203
void PushParameterExpected()
Definition: interpr4.cxx:1897
char sal_uInt16 & nParamCount
Definition: callform.cxx:53
This class provides LO with Kahan summation algorithm About this algorithm: https://en.wikipedia.org/wiki/Kahan_summation_algorithm For general purpose software we assume first order error is enough.
Definition: kahan.hxx:21
size_t SCSIZE
size_t typedef to be able to find places where code was changed from USHORT to size_t and is used to ...
Definition: address.hxx:44
ScMatrixRef GetNewMat(SCSIZE nC, SCSIZE nR, bool bEmpty=false)
Definition: interpr5.cxx:297
sal_uInt16 GetMonth() const
bool hasString() const
Definition: cellvalue.cxx:617
static double gaussinv(double x)
Definition: interpr3.cxx:269
ScETSType
Definition: interpre.hxx:118
svDouble
::boost::intrusive_ptr< ScMatrix > ScMatrixRef
Definition: types.hxx:25
void PushString(const OUString &rStr)
Definition: interpr4.cxx:1789
void SetRow(SCROW nRowP)
Definition: address.hxx:274
bool hasEmptyValue()
Definition: cellvalue.cxx:675
void PushError(FormulaError nError)
Definition: interpr4.cxx:1891
void SetCol(SCCOL nColP)
Definition: address.hxx:278
double GetDouble()
Definition: interpr4.cxx:2061
sal_Int16 GetYear() const
bool MustHaveParamCount(short nAct, short nMust)
Definition: interpre.hxx:1055
void SetTab(SCTAB nTabP)
Definition: address.hxx:282
formula::FormulaConstTokenRef PopToken()
Definition: interpr4.cxx:761
ScMatValType GetDoubleOrStringFromMatrix(double &rDouble, svl::SharedString &rString)
Definition: interpr4.cxx:2399
int i
void PushDouble(double nVal)
Definition: interpr4.cxx:1765
sal_Int16 SCCOL
Definition: types.hxx:21
static bool IsValueType(ScMatValType nType)
Value or boolean.
Definition: scmatrix.hxx:178
void PopDoubleRef(ScRange &rRange, short &rParam, size_t &rRefInList)
If formula::StackVar formula::svDoubleRef pop ScDoubleRefToken and return values of ScComplexRefData...
Definition: interpr4.cxx:1042
bool MustHaveParamCountMin(short nAct, short nMin)
Definition: interpre.hxx:1077
void PushMatrix(const sc::RangeMatrix &rMat)
Definition: interpr4.cxx:1866
svExternalDoubleRef
sal_uInt16 GetDay() const
void GetCellString(svl::SharedString &rStr, ScRefCellValue &rCell)
Definition: interpr4.cxx:243
svExternalSingleRef
FormulaError nGlobalError
Definition: interpre.hxx:208
void GetVars(SCCOL &nCol1, SCROW &nRow1, SCTAB &nTab1, SCCOL &nCol2, SCROW &nRow2, SCTAB &nTab2) const
Definition: address.hxx:692
std::size_t mnCount
formula::StackVar GetStackType()
Stack type with replacement of defaults, e.g. svMissing and formula::svEmptyCell will result in formu...
Definition: interpr4.cxx:1942
double uniform_real_distribution(double a=0.0, double b=1.0)
svSingleRef
#define Y
FormulaError
sal_uInt8 GetByte() const
Definition: interpre.hxx:423
svDoubleRef
void PushIllegalArgument()
Definition: interpr4.cxx:1907
sal_Int32 SCROW
Definition: types.hxx:17
unsigned char sal_uInt8
void ScForecast_Ets(ScETSType eETSType)
Definition: interpr8.cxx:1176
float z
ScDocument & mrDoc
Definition: interpre.hxx:196
svMatrix
int GetError()
svl::SharedString GetString()
Definition: interpr4.cxx:2298
void PushNoValue()
Definition: interpr4.cxx:1917
void PopError()
Definition: interpr4.cxx:749
#define SAL_WARN(area, stream)
bool CheckStringResultLen(OUString &rResult, const OUString &rAdd)
Definition: interpre.hxx:1116
void PushTokenRef(const formula::FormulaConstTokenRef &)
Pushes the token or substitutes with formula::FormulaErrorToken in case nGlobalError is set and the t...
Definition: interpr4.cxx:662
OUString getString(const Any &_rAny)
svMissing
void SetError(FormulaError nError)
Definition: interpre.hxx:1015
aStr
bool GetBool()
Definition: interpre.hxx:441
static bool lcl_SortByX(const DataPoint &lhs, const DataPoint &rhs)
Definition: interpr8.cxx:36
B2DRange maRange
sal_Int16 SCTAB
Definition: types.hxx:22