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