xref: /trunk/main/sc/source/core/tool/interpr3.cxx (revision 195598447694ec8542914a9b51ae9942e8ebacd9)
1 /**************************************************************
2  *
3  * Licensed to the Apache Software Foundation (ASF) under one
4  * or more contributor license agreements.  See the NOTICE file
5  * distributed with this work for additional information
6  * regarding copyright ownership.  The ASF licenses this file
7  * to you under the Apache License, Version 2.0 (the
8  * "License"); you may not use this file except in compliance
9  * with the License.  You may obtain a copy of the License at
10  *
11  *   http://www.apache.org/licenses/LICENSE-2.0
12  *
13  * Unless required by applicable law or agreed to in writing,
14  * software distributed under the License is distributed on an
15  * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
16  * KIND, either express or implied.  See the License for the
17  * specific language governing permissions and limitations
18  * under the License.
19  *
20  *************************************************************/
21 
22 
23 
24 // MARKER(update_precomp.py): autogen include statement, do not remove
25 #include "precompiled_sc.hxx"
26 
27 // INCLUDE ---------------------------------------------------------------
28 
29 #include <tools/solar.h>
30 #include <stdlib.h>
31 #include <string.h>
32 #include <rtl/logfile.hxx>
33 
34 #include "interpre.hxx"
35 #include "global.hxx"
36 #include "compiler.hxx"
37 #include "cell.hxx"
38 #include "document.hxx"
39 #include "dociter.hxx"
40 #include "scmatrix.hxx"
41 #include "globstr.hrc"
42 
43 #include <math.h>
44 #include <vector>
45 #include <algorithm>
46 
47 #include <cmath>
48 
49 using ::std::vector;
50 using namespace formula;
51 
52 // STATIC DATA -----------------------------------------------------------
53 
54 #define SCdEpsilon                1.0E-7
55 #define SC_MAX_ITERATION_COUNT    20
56 #define MAX_ANZ_DOUBLE_FOR_SORT 100000
57 // PI jetzt als F_PI aus solar.h
58 //#define   PI            3.1415926535897932
59 
60 const double ScInterpreter::fMaxGammaArgument = 171.624376956302;  // found experimental
61 const double fMachEps = ::std::numeric_limits<double>::epsilon();
62 
63 //-----------------------------------------------------------------------------
64 
65 class ScDistFunc
66 {
67 public:
68     virtual double GetValue(double x) const = 0;
69 };
70 
71 //  iteration for inverse distributions
72 
73 //template< class T > double lcl_IterateInverse( const T& rFunction, double x0, double x1, bool& rConvError )
74 
75 /** u*w<0.0 fails for values near zero */
lcl_HasChangeOfSign(double u,double w)76 inline bool lcl_HasChangeOfSign( double u, double w )
77 {
78     return (u < 0.0 && w > 0.0) || (u > 0.0 && w < 0.0);
79 }
80 
lcl_IterateInverse(const ScDistFunc & rFunction,double fAx,double fBx,bool & rConvError)81 double lcl_IterateInverse( const ScDistFunc& rFunction, double fAx, double fBx, bool& rConvError )
82 {
83     rConvError = false;
84     const double fYEps = 1.0E-307;
85     const double fXEps = ::std::numeric_limits<double>::epsilon();
86 
87     DBG_ASSERT(fAx<fBx, "IterateInverse: wrong interval");
88 
89     //  find enclosing interval
90 
91     double fAy = rFunction.GetValue(fAx);
92     double fBy = rFunction.GetValue(fBx);
93     double fTemp;
94     unsigned short nCount;
95     for (nCount = 0; nCount < 1000 && !lcl_HasChangeOfSign(fAy,fBy); nCount++)
96     {
97         if (fabs(fAy) <= fabs(fBy))
98         {
99             fTemp = fAx;
100             fAx += 2.0 * (fAx - fBx);
101             if (fAx < 0.0)
102                 fAx = 0.0;
103             fBx = fTemp;
104             fBy = fAy;
105             fAy = rFunction.GetValue(fAx);
106         }
107         else
108         {
109             fTemp = fBx;
110             fBx += 2.0 * (fBx - fAx);
111             fAx = fTemp;
112             fAy = fBy;
113             fBy = rFunction.GetValue(fBx);
114         }
115     }
116 
117     if (fAy == 0.0)
118         return fAx;
119     if (fBy == 0.0)
120         return fBx;
121     if (!lcl_HasChangeOfSign( fAy, fBy))
122     {
123         rConvError = true;
124         return 0.0;
125     }
126     // inverse quadric interpolation with additional brackets
127     // set three points
128     double fPx = fAx;
129     double fPy = fAy;
130     double fQx = fBx;
131     double fQy = fBy;
132     double fRx = fAx;
133     double fRy = fAy;
134     double fSx = 0.5 * (fAx + fBx); // potential next point
135     bool bHasToInterpolate = true;
136     nCount = 0;
137     while ( nCount < 500 && fabs(fRy) > fYEps &&
138             (fBx-fAx) > ::std::max( fabs(fAx), fabs(fBx)) * fXEps )
139     {
140         if (bHasToInterpolate)
141         {
142             if (fPy!=fQy && fQy!=fRy && fRy!=fPy)
143             {
144                 fSx = fPx * fRy * fQy / (fRy-fPy) / (fQy-fPy)
145                     + fRx * fQy * fPy / (fQy-fRy) / (fPy-fRy)
146                     + fQx * fPy * fRy / (fPy-fQy) / (fRy-fQy);
147                 bHasToInterpolate = (fAx < fSx) && (fSx < fBx); // inside the brackets?
148             }
149             else
150                 bHasToInterpolate = false;
151         }
152         if(!bHasToInterpolate)
153         {
154             fSx = 0.5 * (fAx + fBx);
155             // reset points
156             fPx = fAx; fPy = fAy;
157             fQx = fBx; fQy = fBy;
158             bHasToInterpolate = true;
159         }
160         // shift points for next interpolation
161         fPx = fQx; fQx = fRx; fRx = fSx;
162         fPy = fQy; fQy = fRy; fRy = rFunction.GetValue(fSx);
163         // update brackets
164         if (lcl_HasChangeOfSign( fAy, fRy))
165         {
166             fBx = fRx; fBy = fRy;
167         }
168         else
169         {
170             fAx = fRx; fAy = fRy;
171         }
172         // if last interration brought to small advance, then do bisection next
173         // time, for safety
174         bHasToInterpolate = bHasToInterpolate && (fabs(fRy) * 2.0 <= fabs(fQy));
175         ++nCount;
176     }
177     return fRx;
178 }
179 
180 //-----------------------------------------------------------------------------
181 // Allgemeine Funktionen
182 //-----------------------------------------------------------------------------
183 
ScNoName()184 void ScInterpreter::ScNoName()
185 {
186     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNoName" );
187     PushError(errNoName);
188 }
189 
ScBadName()190 void ScInterpreter::ScBadName()
191 {
192     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScBadName" );
193     short nParamCount = GetByte();
194     while (nParamCount-- > 0)
195     {
196         PopError();
197     }
198     PushError( errNoName);
199 }
200 
phi(double x)201 double ScInterpreter::phi(double x)
202 {
203     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::phi" );
204     return  0.39894228040143268 * exp(-(x * x) / 2.0);
205 }
206 
integralPhi(double x)207 double ScInterpreter::integralPhi(double x)
208 { // Using gauss(x)+0.5 has severe cancellation errors for x<-4
209     return 0.5 * ::rtl::math::erfc(-x * 0.7071067811865475); // * 1/sqrt(2)
210 }
211 
taylor(double * pPolynom,sal_uInt16 nMax,double x)212 double ScInterpreter::taylor(double* pPolynom, sal_uInt16 nMax, double x)
213 {
214     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::taylor" );
215     double nVal = pPolynom[nMax];
216     for (short i = nMax-1; i >= 0; i--)
217     {
218         nVal = pPolynom[i] + (nVal * x);
219     }
220     return nVal;
221 }
222 
gauss(double x)223 double ScInterpreter::gauss(double x)
224 {
225     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::gauss" );
226     double t0[] =
227     { 0.39894228040143268, -0.06649038006690545,  0.00997355701003582,
228      -0.00118732821548045,  0.00011543468761616, -0.00000944465625950,
229       0.00000066596935163, -0.00000004122667415,  0.00000000227352982,
230       0.00000000011301172,  0.00000000000511243, -0.00000000000021218 };
231     double t2[] =
232     { 0.47724986805182079,  0.05399096651318805, -0.05399096651318805,
233       0.02699548325659403, -0.00449924720943234, -0.00224962360471617,
234       0.00134977416282970, -0.00011783742691370, -0.00011515930357476,
235       0.00003704737285544,  0.00000282690796889, -0.00000354513195524,
236       0.00000037669563126,  0.00000019202407921, -0.00000005226908590,
237      -0.00000000491799345,  0.00000000366377919, -0.00000000015981997,
238      -0.00000000017381238,  0.00000000002624031,  0.00000000000560919,
239      -0.00000000000172127, -0.00000000000008634,  0.00000000000007894 };
240     double t4[] =
241     { 0.49996832875816688,  0.00013383022576489, -0.00026766045152977,
242       0.00033457556441221, -0.00028996548915725,  0.00018178605666397,
243      -0.00008252863922168,  0.00002551802519049, -0.00000391665839292,
244      -0.00000074018205222,  0.00000064422023359, -0.00000017370155340,
245       0.00000000909595465,  0.00000000944943118, -0.00000000329957075,
246       0.00000000029492075,  0.00000000011874477, -0.00000000004420396,
247       0.00000000000361422,  0.00000000000143638, -0.00000000000045848 };
248     double asympt[] = { -1.0, 1.0, -3.0, 15.0, -105.0 };
249 
250     double xAbs = fabs(x);
251     sal_uInt16 xShort = (sal_uInt16)::rtl::math::approxFloor(xAbs);
252     double nVal = 0.0;
253     if (xShort == 0)
254         nVal = taylor(t0, 11, (xAbs * xAbs)) * xAbs;
255     else if ((xShort >= 1) && (xShort <= 2))
256         nVal = taylor(t2, 23, (xAbs - 2.0));
257     else if ((xShort >= 3) && (xShort <= 4))
258         nVal = taylor(t4, 20, (xAbs - 4.0));
259     else
260         nVal = 0.5 + phi(xAbs) * taylor(asympt, 4, 1.0 / (xAbs * xAbs)) / xAbs;
261     if (x < 0.0)
262         return -nVal;
263     else
264         return nVal;
265 }
266 
267 //
268 //  #i26836# new gaussinv implementation by Martin Eitzenberger <m.eitzenberger@unix.net>
269 //
270 
gaussinv(double x)271 double ScInterpreter::gaussinv(double x)
272 {
273     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::gaussinv" );
274     double q,t,z;
275 
276     q=x-0.5;
277 
278     if(fabs(q)<=.425)
279     {
280         t=0.180625-q*q;
281 
282         z=
283         q*
284         (
285             (
286                 (
287                     (
288                         (
289                             (
290                                 (
291                                     t*2509.0809287301226727+33430.575583588128105
292                                 )
293                                 *t+67265.770927008700853
294                             )
295                             *t+45921.953931549871457
296                         )
297                         *t+13731.693765509461125
298                     )
299                     *t+1971.5909503065514427
300                 )
301                 *t+133.14166789178437745
302             )
303             *t+3.387132872796366608
304         )
305         /
306         (
307             (
308                 (
309                     (
310                         (
311                             (
312                                 (
313                                     t*5226.495278852854561+28729.085735721942674
314                                 )
315                                 *t+39307.89580009271061
316                             )
317                             *t+21213.794301586595867
318                         )
319                         *t+5394.1960214247511077
320                     )
321                     *t+687.1870074920579083
322                 )
323                 *t+42.313330701600911252
324             )
325             *t+1.0
326         );
327 
328     }
329     else
330     {
331         if(q>0) t=1-x;
332         else        t=x;
333 
334         t=sqrt(-log(t));
335 
336         if(t<=5.0)
337         {
338             t+=-1.6;
339 
340             z=
341             (
342                 (
343                     (
344                         (
345                             (
346                                 (
347                                     (
348                                         t*7.7454501427834140764e-4+0.0227238449892691845833
349                                     )
350                                     *t+0.24178072517745061177
351                                 )
352                                 *t+1.27045825245236838258
353                             )
354                             *t+3.64784832476320460504
355                         )
356                         *t+5.7694972214606914055
357                     )
358                     *t+4.6303378461565452959
359                 )
360                 *t+1.42343711074968357734
361             )
362             /
363             (
364                 (
365                     (
366                         (
367                             (
368                                 (
369                                     (
370                                         t*1.05075007164441684324e-9+5.475938084995344946e-4
371                                     )
372                                     *t+0.0151986665636164571966
373                                 )
374                                 *t+0.14810397642748007459
375                             )
376                             *t+0.68976733498510000455
377                         )
378                         *t+1.6763848301838038494
379                     )
380                     *t+2.05319162663775882187
381                 )
382                 *t+1.0
383             );
384 
385         }
386         else
387         {
388             t+=-5.0;
389 
390             z=
391             (
392                 (
393                     (
394                         (
395                             (
396                                 (
397                                     (
398                                         t*2.01033439929228813265e-7+2.71155556874348757815e-5
399                                     )
400                                     *t+0.0012426609473880784386
401                                 )
402                                 *t+0.026532189526576123093
403                             )
404                             *t+0.29656057182850489123
405                         )
406                         *t+1.7848265399172913358
407                     )
408                     *t+5.4637849111641143699
409                 )
410                 *t+6.6579046435011037772
411             )
412             /
413             (
414                 (
415                     (
416                         (
417                             (
418                                 (
419                                     (
420                                         t*2.04426310338993978564e-15+1.4215117583164458887e-7
421                                     )
422                                     *t+1.8463183175100546818e-5
423                                 )
424                                 *t+7.868691311456132591e-4
425                             )
426                             *t+0.0148753612908506148525
427                         )
428                         *t+0.13692988092273580531
429                     )
430                     *t+0.59983220655588793769
431                 )
432                 *t+1.0
433             );
434 
435         }
436 
437         if(q<0.0) z=-z;
438     }
439 
440     return z;
441 }
442 
Fakultaet(double x)443 double ScInterpreter::Fakultaet(double x)
444 {
445     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::Fakultaet" );
446     x = ::rtl::math::approxFloor(x);
447     if (x < 0.0)
448         return 0.0;
449     else if (x == 0.0)
450         return 1.0;
451     else if (x <= 170.0)
452     {
453         double fTemp = x;
454         while (fTemp > 2.0)
455         {
456             fTemp--;
457             x *= fTemp;
458         }
459     }
460     else
461         SetError(errNoValue);
462 /*                                           // Stirlingsche Naeherung zu ungenau
463     else
464         x = pow(x/exp(1), x) * sqrt(x) * SQRT_2_PI * (1.0 + 1.0 / (12.0 * x));
465 */
466     return x;
467 }
468 
BinomKoeff(double n,double k)469 double ScInterpreter::BinomKoeff(double n, double k)
470 {
471     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::BinomKoeff" );
472     double nVal = 0.0;
473     k = ::rtl::math::approxFloor(k);
474     if (n < k)
475         nVal = 0.0;
476     else if (k == 0.0)
477         nVal = 1.0;
478     else
479     {
480         nVal = n/k;
481         n--;
482         k--;
483         while (k > 0.0)
484         {
485             nVal *= n/k;
486             k--;
487             n--;
488         }
489 /*
490         double f1 = n;                      // Zaehler
491         double f2 = k;                      // Nenner
492         n--;
493         k--;
494         while (k > 0.0)
495         {
496             f2 *= k;
497             f1 *= n;
498             k--;
499             n--;
500         }
501         nVal = f1 / f2;
502 */
503     }
504     return nVal;
505 }
506 
507 
508 // The algorithm is based on lanczos13m53 in lanczos.hpp
509 // in math library from http://www.boost.org
510 /** you must ensure fZ>0
511     Uses a variant of the Lanczos sum with a rational function. */
lcl_getLanczosSum(double fZ)512 double lcl_getLanczosSum(double fZ)
513 {
514     const double fNum[13] ={
515         23531376880.41075968857200767445163675473,
516         42919803642.64909876895789904700198885093,
517         35711959237.35566804944018545154716670596,
518         17921034426.03720969991975575445893111267,
519         6039542586.35202800506429164430729792107,
520         1439720407.311721673663223072794912393972,
521         248874557.8620541565114603864132294232163,
522         31426415.58540019438061423162831820536287,
523         2876370.628935372441225409051620849613599,
524         186056.2653952234950402949897160456992822,
525         8071.672002365816210638002902272250613822,
526         210.8242777515793458725097339207133627117,
527         2.506628274631000270164908177133837338626
528         };
529     const double fDenom[13] = {
530         0,
531         39916800,
532         120543840,
533         150917976,
534         105258076,
535         45995730,
536         13339535,
537         2637558,
538         357423,
539         32670,
540         1925,
541         66,
542         1
543         };
544     // Horner scheme
545     double fSumNum;
546     double fSumDenom;
547     int nI;
548     double fZInv;
549     if (fZ<=1.0)
550     {
551         fSumNum = fNum[12];
552         fSumDenom = fDenom[12];
553         for (nI = 11; nI >= 0; --nI)
554         {
555             fSumNum *= fZ;
556             fSumNum += fNum[nI];
557             fSumDenom *= fZ;
558             fSumDenom += fDenom[nI];
559         }
560     }
561     else
562     // Cancel down with fZ^12; Horner scheme with reverse coefficients
563     {
564         fZInv = 1/fZ;
565         fSumNum = fNum[0];
566         fSumDenom = fDenom[0];
567         for (nI = 1; nI <=12; ++nI)
568         {
569             fSumNum *= fZInv;
570             fSumNum += fNum[nI];
571             fSumDenom *= fZInv;
572             fSumDenom += fDenom[nI];
573         }
574     }
575     return fSumNum/fSumDenom;
576 }
577 
578 // The algorithm is based on tgamma in gamma.hpp
579 // in math library from http://www.boost.org
580 /** You must ensure fZ>0; fZ>171.624376956302 will overflow. */
lcl_GetGammaHelper(double fZ)581 double lcl_GetGammaHelper(double fZ)
582 {
583     double fGamma = lcl_getLanczosSum(fZ);
584     const double fg = 6.024680040776729583740234375;
585     double fZgHelp = fZ + fg - 0.5;
586     // avoid intermediate overflow
587     double fHalfpower = pow( fZgHelp, fZ / 2 - 0.25);
588     fGamma *= fHalfpower;
589     fGamma /= exp(fZgHelp);
590     fGamma *= fHalfpower;
591     if (fZ <= 20.0 && fZ == ::rtl::math::approxFloor(fZ))
592         fGamma = ::rtl::math::round(fGamma);
593     return fGamma;
594 }
595 
596 // The algorithm is based on tgamma in gamma.hpp
597 // in math library from http://www.boost.org
598 /** You must ensure fZ>0 */
lcl_GetLogGammaHelper(double fZ)599 double lcl_GetLogGammaHelper(double fZ)
600 {
601     const double fg = 6.024680040776729583740234375;
602     double fZgHelp = fZ + fg - 0.5;
603     return log( lcl_getLanczosSum(fZ)) + (fZ-0.5) * log(fZgHelp) - fZgHelp;
604 }
605 
606 /** You must ensure non integer arguments for fZ<1 */
GetGamma(double fZ)607 double ScInterpreter::GetGamma(double fZ)
608 {
609     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetGamma" );
610     const double fLogPi = log(F_PI);
611     const double fLogDblMax = log( ::std::numeric_limits<double>::max());
612 
613     if (fZ > fMaxGammaArgument)
614     {
615         SetError(errIllegalFPOperation);
616         return HUGE_VAL;
617     }
618 
619     if (fZ >= 1.0)
620         return lcl_GetGammaHelper(fZ);
621 
622     if (fZ >= 0.5)  // shift to x>=1 using Gamma(x)=Gamma(x+1)/x
623         return lcl_GetGammaHelper(fZ+1) / fZ;
624 
625     if (fZ >= -0.5) // shift to x>=1, might overflow
626     {
627         double fLogTest = lcl_GetLogGammaHelper(fZ+2) - log(fZ+1) - log( fabs(fZ));
628         if (fLogTest >= fLogDblMax)
629         {
630             SetError( errIllegalFPOperation);
631             return HUGE_VAL;
632         }
633         return lcl_GetGammaHelper(fZ+2) / (fZ+1) / fZ;
634     }
635     // fZ<-0.5
636     // Use Euler's reflection formula: gamma(x)= pi/ ( gamma(1-x)*sin(pi*x) )
637     double fLogDivisor = lcl_GetLogGammaHelper(1-fZ) + log( fabs( ::rtl::math::sin( F_PI*fZ)));
638     if (fLogDivisor - fLogPi >= fLogDblMax)     // underflow
639         return 0.0;
640 
641     if (fLogDivisor<0.0)
642         if (fLogPi - fLogDivisor > fLogDblMax)  // overflow
643         {
644             SetError(errIllegalFPOperation);
645             return HUGE_VAL;
646         }
647 
648     return exp( fLogPi - fLogDivisor) * ((::rtl::math::sin( F_PI*fZ) < 0.0) ? -1.0 : 1.0);
649 }
650 
651 
652 /** You must ensure fZ>0 */
GetLogGamma(double fZ)653 double ScInterpreter::GetLogGamma(double fZ)
654 {
655     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetLogGamma" );
656     if (fZ >= fMaxGammaArgument)
657         return lcl_GetLogGammaHelper(fZ);
658     if (fZ >= 1.0)
659         return log(lcl_GetGammaHelper(fZ));
660     if (fZ >= 0.5)
661         return log( lcl_GetGammaHelper(fZ+1) / fZ);
662     return lcl_GetLogGammaHelper(fZ+2) - log(fZ+1) - log(fZ);
663 }
664 
GetFDist(double x,double fF1,double fF2)665 double ScInterpreter::GetFDist(double x, double fF1, double fF2)
666 {
667     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetFDist" );
668     double arg = fF2/(fF2+fF1*x);
669     double alpha = fF2/2.0;
670     double beta = fF1/2.0;
671     return (GetBetaDist(arg, alpha, beta));
672 /*
673     double Z = (pow(fF,1.0/3.0)*(1.0-2.0/(9.0*fF2)) - (1.0-2.0/(9.0*fF1))) /
674                sqrt(2.0/(9.0*fF1) + pow(fF,2.0/3.0)*2.0/(9.0*fF2));
675     return (0.5-gauss(Z));
676 */
677 }
678 
GetTDist(double T,double fDF)679 double ScInterpreter::GetTDist(double T, double fDF)
680 {
681     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetTDist" );
682     return 0.5 * GetBetaDist(fDF/(fDF+T*T), fDF/2.0, 0.5);
683 /*
684     sal_uInt16 DF = (sal_uInt16) fDF;
685     double A = T / sqrt(DF);
686     double B = 1.0 + A*A;
687     double R;
688     if (DF == 1)
689         R = 0.5 + atan(A)/F_PI;
690     else if (DF % 2 == 0)
691     {
692         double S0 = A/(2.0 * sqrt(B));
693         double C0 = S0;
694         for (sal_uInt16 i = 2; i <= DF-2; i+=2)
695         {
696             C0 *= (1.0 - 1.0/(double)i)/B;
697             S0 += C0;
698         }
699         R = 0.5 + S0;
700     }
701     else
702     {
703         double S1 = A / (B * F_PI);
704         double C1 = S1;
705         for (sal_uInt16 i = 3; i <= DF-2; i+=2)
706         {
707             C1 *= (1.0 - 1.0/(double)i)/B;
708             S1 += C1;
709         }
710         R = 0.5 + atan(A)/F_PI + S1;
711     }
712     return 1.0 - R;
713 */
714 }
715 
716 // for LEGACY.CHIDIST, returns right tail, fDF=degrees of freedom
717 /** You must ensure fDF>0.0 */
GetChiDist(double fX,double fDF)718 double ScInterpreter::GetChiDist(double fX, double fDF)
719 {
720     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetChiDist" );
721     if (fX <= 0.0)
722         return 1.0; // see ODFF
723     else
724         return GetUpRegIGamma( fDF/2.0, fX/2.0);
725 }
726 
727 // ready for ODF 1.2
728 // for ODF CHISQDIST; cumulative distribution function, fDF=degrees of freedom
729 // returns left tail
730 /** You must ensure fDF>0.0 */
GetChiSqDistCDF(double fX,double fDF)731 double ScInterpreter::GetChiSqDistCDF(double fX, double fDF)
732 {
733     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetChiSqDistCDF" );
734     if (fX <= 0.0)
735         return 0.0; // see ODFF
736     else
737         return GetLowRegIGamma( fDF/2.0, fX/2.0);
738 }
739 
GetChiSqDistPDF(double fX,double fDF)740 double ScInterpreter::GetChiSqDistPDF(double fX, double fDF)
741 {
742     // you must ensure fDF is positive integer
743     double fValue;
744     double fCount;
745     if (fX <= 0.0)
746         return 0.0; // see ODFF
747     if (fDF*fX > 1391000.0)
748     {
749         // intermediate invalid values, use log
750         fValue = exp((0.5*fDF - 1) * log(fX*0.5) - 0.5 * fX - log(2.0) - GetLogGamma(0.5*fDF));
751     }
752     else // fDF is small in most cases, we can iterate
753     {
754         if (fmod(fDF,2.0)<0.5)
755         {
756             // even
757             fValue = 0.5;
758             fCount = 2.0;
759         }
760         else
761         {
762             fValue = 1/sqrt(fX*2*F_PI);
763             fCount = 1.0;
764         }
765         while ( fCount < fDF)
766         {
767             fValue *= (fX / fCount);
768             fCount += 2.0;
769         }
770         if (fX>=1425.0) // underflow in e^(-x/2)
771             fValue = exp(log(fValue)-fX/2);
772         else
773             fValue *= exp(-fX/2);
774     }
775     return fValue;
776 }
777 
ScChiSqDist()778 void ScInterpreter::ScChiSqDist()
779 {
780     sal_uInt8 nParamCount = GetByte();
781     if ( !MustHaveParamCount( nParamCount, 2, 3 ) )
782         return;
783     double fX;
784     bool bCumulative;
785     if (nParamCount == 3)
786         bCumulative = GetBool();
787     else
788         bCumulative = true;
789     double fDF = ::rtl::math::approxFloor(GetDouble());
790     if (fDF < 1.0)
791         PushIllegalArgument();
792     else
793     {
794         fX = GetDouble();
795         if (bCumulative)
796             PushDouble(GetChiSqDistCDF(fX,fDF));
797         else
798             PushDouble(GetChiSqDistPDF(fX,fDF));
799     }
800 }
801 
ScGamma()802 void ScInterpreter::ScGamma()
803 {
804     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGamma" );
805     double x = GetDouble();
806     double fResult;
807     if (x <= 0.0 && x == ::rtl::math::approxFloor(x))
808         PushIllegalArgument();
809     else
810     {
811         fResult = GetGamma(x);
812         if (nGlobalError)
813         {
814             PushError( nGlobalError);
815             return;
816         }
817         PushDouble(fResult);
818     }
819 }
820 
821 
ScLogGamma()822 void ScInterpreter::ScLogGamma()
823 {
824     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLogGamma" );
825     double x = GetDouble();
826     if (x > 0.0)    // constraint from ODFF
827         PushDouble( GetLogGamma(x));
828     else
829         PushIllegalArgument();
830 }
831 
GetBeta(double fAlpha,double fBeta)832 double ScInterpreter::GetBeta(double fAlpha, double fBeta)
833 {
834     double fA;
835     double fB;
836     if (fAlpha > fBeta)
837     {
838         fA = fAlpha; fB = fBeta;
839     }
840     else
841     {
842         fA = fBeta; fB = fAlpha;
843     }
844     if (fA+fB < fMaxGammaArgument) // simple case
845         return GetGamma(fA)/GetGamma(fA+fB)*GetGamma(fB);
846     // need logarithm
847     // GetLogGamma is not accurate enough, back to Lanczos for all three
848     // GetGamma and arrange factors newly.
849     const double fg = 6.024680040776729583740234375; //see GetGamma
850     double fgm = fg - 0.5;
851     double fLanczos = lcl_getLanczosSum(fA);
852     fLanczos /= lcl_getLanczosSum(fA+fB);
853     fLanczos *= lcl_getLanczosSum(fB);
854     double fABgm = fA+fB+fgm;
855     fLanczos *= sqrt((fABgm/(fA+fgm))/(fB+fgm));
856     double fTempA = fB/(fA+fgm); // (fA+fgm)/fABgm = 1 / ( 1 + fB/(fA+fgm))
857     double fTempB = fA/(fB+fgm);
858     double fResult = exp(-fA * std::log1p(fTempA)
859                             -fB * std::log1p(fTempB)-fgm);
860     fResult *= fLanczos;
861     return fResult;
862 }
863 
864 // Same as GetBeta but with logarithm
GetLogBeta(double fAlpha,double fBeta)865 double ScInterpreter::GetLogBeta(double fAlpha, double fBeta)
866 {
867     double fA;
868     double fB;
869     if (fAlpha > fBeta)
870     {
871         fA = fAlpha; fB = fBeta;
872     }
873     else
874     {
875         fA = fBeta; fB = fAlpha;
876     }
877     const double fg = 6.024680040776729583740234375; //see GetGamma
878     double fgm = fg - 0.5;
879     double fLanczos = lcl_getLanczosSum(fA);
880     fLanczos /= lcl_getLanczosSum(fA+fB);
881     fLanczos *= lcl_getLanczosSum(fB);
882     double fLogLanczos = log(fLanczos);
883     double fABgm = fA+fB+fgm;
884     fLogLanczos += 0.5*(log(fABgm)-log(fA+fgm)-log(fB+fgm));
885     double fTempA = fB/(fA+fgm); // (fA+fgm)/fABgm = 1 / ( 1 + fB/(fA+fgm))
886     double fTempB = fA/(fB+fgm);
887     double fResult = -fA * std::log1p(fTempA)
888                         -fB * std::log1p(fTempB)-fgm;
889     fResult += fLogLanczos;
890     return fResult;
891 }
892 
893 // beta distribution probability density function
GetBetaDistPDF(double fX,double fA,double fB)894 double ScInterpreter::GetBetaDistPDF(double fX, double fA, double fB)
895 {
896     // special cases
897     if (fA == 1.0) // result b*(1-x)^(b-1)
898     {
899         if (fB == 1.0)
900             return 1.0;
901         if (fB == 2.0)
902             return -2.0*fX + 2.0;
903         if (fX == 1.0 && fB < 1.0)
904         {
905             SetError(errIllegalArgument);
906             return HUGE_VAL;
907         }
908         if (fX <= 0.01)
909             return fB + fB * std::expm1((fB-1.0) * std::log1p(-fX));
910         else
911             return fB * pow(0.5-fX+0.5,fB-1.0);
912     }
913     if (fB == 1.0) // result a*x^(a-1)
914     {
915         if (fA == 2.0)
916             return fA * fX;
917         if (fX == 0.0 && fA < 1.0)
918         {
919             SetError(errIllegalArgument);
920             return HUGE_VAL;
921         }
922         return fA * pow(fX,fA-1);
923     }
924     if (fX <= 0.0)
925     {
926         if (fA < 1.0 && fX == 0.0)
927         {
928             SetError(errIllegalArgument);
929             return HUGE_VAL;
930         }
931         else
932             return 0.0;
933     }
934     if (fX >= 1.0)
935     {
936         if (fB < 1.0 && fX == 1.0)
937         {
938             SetError(errIllegalArgument);
939             return HUGE_VAL;
940         }
941         else
942             return 0.0;
943     }
944 
945     // normal cases; result x^(a-1)*(1-x)^(b-1)/Beta(a,b)
946     const double fLogDblMax = log( ::std::numeric_limits<double>::max());
947     const double fLogDblMin = log( ::std::numeric_limits<double>::min());
948     double fLogY = (fX < 0.1) ? std::log1p(-fX) : log(0.5-fX+0.5);
949     double fLogX = log(fX);
950     double fAm1LogX = (fA-1.0) * fLogX;
951     double fBm1LogY = (fB-1.0) * fLogY;
952     double fLogBeta = GetLogBeta(fA,fB);
953     // check whether parts over- or underflow
954     if (   fAm1LogX < fLogDblMax  && fAm1LogX > fLogDblMin
955         && fBm1LogY < fLogDblMax  && fBm1LogY > fLogDblMin
956         && fLogBeta < fLogDblMax  && fLogBeta > fLogDblMin
957         && fAm1LogX + fBm1LogY < fLogDblMax && fAm1LogX + fBm1LogY > fLogDblMin)
958         return pow(fX,fA-1.0) * pow(0.5-fX+0.5,fB-1.0) / GetBeta(fA,fB);
959     else // need logarithm;
960         // might overflow as a whole, but seldom, not worth to pre-detect it
961         return exp( fAm1LogX + fBm1LogY - fLogBeta);
962 }
963 
964 
965 /*
966                 x^a * (1-x)^b
967     I_x(a,b) = ----------------  * result of ContFrac
968                 a * Beta(a,b)
969 */
lcl_GetBetaHelperContFrac(double fX,double fA,double fB)970 double lcl_GetBetaHelperContFrac(double fX, double fA, double fB)
971 {   // like old version
972     double a1, b1, a2, b2, fnorm, apl2m, d2m, d2m1, cfnew, cf;
973     a1 = 1.0; b1 = 1.0;
974     b2 = 1.0 - (fA+fB)/(fA+1.0)*fX;
975     if (b2 == 0.0)
976     {
977         a2 = 0.0;
978         fnorm = 1.0;
979         cf = 1.0;
980     }
981     else
982     {
983         a2 = 1.0;
984         fnorm = 1.0/b2;
985         cf = a2*fnorm;
986     }
987     cfnew = 1.0;
988     double rm = 1.0;
989 
990     const double fMaxIter = 50000.0;
991     // loop security, normal cases converge in less than 100 iterations.
992     // FIXME: You will get so much iteratons for fX near mean,
993     // I do not know a better algorithm.
994     bool bfinished = false;
995     do
996     {
997         apl2m = fA + 2.0*rm;
998         d2m = rm*(fB-rm)*fX/((apl2m-1.0)*apl2m);
999         d2m1 = -(fA+rm)*(fA+fB+rm)*fX/(apl2m*(apl2m+1.0));
1000         a1 = (a2+d2m*a1)*fnorm;
1001         b1 = (b2+d2m*b1)*fnorm;
1002         a2 = a1 + d2m1*a2*fnorm;
1003         b2 = b1 + d2m1*b2*fnorm;
1004         if (b2 != 0.0)
1005         {
1006             fnorm = 1.0/b2;
1007             cfnew = a2*fnorm;
1008             bfinished = (fabs(cf-cfnew) < fabs(cf)*fMachEps);
1009         }
1010         cf = cfnew;
1011         rm += 1.0;
1012     }
1013     while (rm < fMaxIter && !bfinished);
1014     return cf;
1015 }
1016 
1017 // cumulative distribution function, normalized
GetBetaDist(double fXin,double fAlpha,double fBeta)1018 double ScInterpreter::GetBetaDist(double fXin, double fAlpha, double fBeta)
1019 {
1020 // special cases
1021     if (fXin <= 0.0)  // values are valid, see spec
1022         return 0.0;
1023     if (fXin >= 1.0)  // values are valid, see spec
1024         return 1.0;
1025     if (fBeta == 1.0)
1026         return pow(fXin, fAlpha);
1027     if (fAlpha == 1.0)
1028     //            1.0 - pow(1.0-fX,fBeta) is not accurate enough
1029         return -std::expm1(fBeta * std::log1p(-fXin));
1030     //FIXME: need special algorithm for fX near fP for large fA,fB
1031     double fResult;
1032     // I use always continued fraction, power series are neither
1033     // faster nor more accurate.
1034     double fY = (0.5-fXin)+0.5;
1035     double flnY = std::log1p(-fXin);
1036     double fX = fXin;
1037     double flnX = log(fXin);
1038     double fA = fAlpha;
1039     double fB = fBeta;
1040     bool bReflect = fXin > fAlpha/(fAlpha+fBeta);
1041     if (bReflect)
1042     {
1043         fA = fBeta;
1044         fB = fAlpha;
1045         fX = fY;
1046         fY = fXin;
1047         flnX = flnY;
1048         flnY = log(fXin);
1049     }
1050     fResult = lcl_GetBetaHelperContFrac(fX,fA,fB);
1051     fResult = fResult/fA;
1052     double fP = fA/(fA+fB);
1053     double fQ = fB/(fA+fB);
1054     double fTemp;
1055     if (fA > 1.0 && fB > 1.0 && fP < 0.97 && fQ < 0.97) //found experimental
1056         fTemp = GetBetaDistPDF(fX,fA,fB)*fX*fY;
1057     else
1058         fTemp = exp(fA*flnX + fB*flnY - GetLogBeta(fA,fB));
1059     fResult *= fTemp;
1060     if (bReflect)
1061         fResult = 0.5 - fResult + 0.5;
1062     if (fResult > 1.0) // ensure valid range
1063         fResult = 1.0;
1064     if (fResult < 0.0)
1065         fResult = 0.0;
1066     return fResult;
1067 }
1068 
ScBetaDist()1069   void ScInterpreter::ScBetaDist()
1070   {
1071       sal_uInt8 nParamCount = GetByte();
1072     if ( !MustHaveParamCount( nParamCount, 3, 6 ) ) // expanded, see #i91547#
1073           return;
1074     double fLowerBound, fUpperBound;
1075     double alpha, beta, x;
1076     bool bIsCumulative;
1077     if (nParamCount == 6)
1078         bIsCumulative = GetBool();
1079       else
1080         bIsCumulative = true;
1081     if (nParamCount >= 5)
1082         fUpperBound = GetDouble();
1083     else
1084         fUpperBound = 1.0;
1085       if (nParamCount >= 4)
1086         fLowerBound = GetDouble();
1087       else
1088         fLowerBound = 0.0;
1089       beta = GetDouble();
1090       alpha = GetDouble();
1091       x = GetDouble();
1092     double fScale = fUpperBound - fLowerBound;
1093     if (fScale <= 0.0 || alpha <= 0.0 || beta <= 0.0)
1094       {
1095           PushIllegalArgument();
1096           return;
1097       }
1098     if (bIsCumulative) // cumulative distribution function
1099     {
1100         // special cases
1101         if (x < fLowerBound)
1102         {
1103             PushDouble(0.0); return; //see spec
1104         }
1105         if (x > fUpperBound)
1106         {
1107             PushDouble(1.0); return; //see spec
1108         }
1109         // normal cases
1110         x = (x-fLowerBound)/fScale;  // convert to standard form
1111         PushDouble(GetBetaDist(x, alpha, beta));
1112         return;
1113     }
1114     else // probability density function
1115     {
1116         if (x < fLowerBound || x > fUpperBound)
1117         {
1118             PushDouble(0.0);
1119             return;
1120         }
1121         x = (x-fLowerBound)/fScale;
1122         PushDouble(GetBetaDistPDF(x, alpha, beta)/fScale);
1123         return;
1124     }
1125 }
1126 
ScPhi()1127 void ScInterpreter::ScPhi()
1128 {
1129     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPhi" );
1130     PushDouble(phi(GetDouble()));
1131 }
1132 
ScGauss()1133 void ScInterpreter::ScGauss()
1134 {
1135     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGauss" );
1136     PushDouble(gauss(GetDouble()));
1137 }
1138 
ScFisher()1139 void ScInterpreter::ScFisher()
1140 {
1141     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFisher" );
1142     double fVal = GetDouble();
1143     if (fabs(fVal) >= 1.0)
1144         PushIllegalArgument();
1145     else
1146         PushDouble( std::atanh( fVal));
1147 }
1148 
ScFisherInv()1149 void ScInterpreter::ScFisherInv()
1150 {
1151     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFisherInv" );
1152     PushDouble( tanh( GetDouble()));
1153 }
1154 
ScFact()1155 void ScInterpreter::ScFact()
1156 {
1157     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFact" );
1158     double nVal = GetDouble();
1159     if (nVal < 0.0)
1160         PushIllegalArgument();
1161     else
1162         PushDouble(Fakultaet(nVal));
1163 }
1164 
ScKombin()1165 void ScInterpreter::ScKombin()
1166 {
1167     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScKombin" );
1168     if ( MustHaveParamCount( GetByte(), 2 ) )
1169     {
1170         double k = ::rtl::math::approxFloor(GetDouble());
1171         double n = ::rtl::math::approxFloor(GetDouble());
1172         if (k < 0.0 || n < 0.0 || k > n)
1173             PushIllegalArgument();
1174         else
1175             PushDouble(BinomKoeff(n, k));
1176     }
1177 }
1178 
ScKombin2()1179 void ScInterpreter::ScKombin2()
1180 {
1181     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScKombin2" );
1182     if ( MustHaveParamCount( GetByte(), 2 ) )
1183     {
1184         double k = ::rtl::math::approxFloor(GetDouble());
1185         double n = ::rtl::math::approxFloor(GetDouble());
1186         if (k < 0.0 || n < 0.0 || k > n)
1187             PushIllegalArgument();
1188         else
1189             PushDouble(BinomKoeff(n + k - 1, k));
1190     }
1191 }
1192 
ScVariationen()1193 void ScInterpreter::ScVariationen()
1194 {
1195     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScVariationen" );
1196     if ( MustHaveParamCount( GetByte(), 2 ) )
1197     {
1198         double k = ::rtl::math::approxFloor(GetDouble());
1199         double n = ::rtl::math::approxFloor(GetDouble());
1200         if (n < 0.0 || k < 0.0 || k > n)
1201             PushIllegalArgument();
1202         else if (k == 0.0)
1203             PushInt(1);     // (n! / (n - 0)!) == 1
1204         else
1205         {
1206             double nVal = n;
1207             for (sal_uLong i = (sal_uLong)k-1; i >= 1; i--)
1208                 nVal *= n-(double)i;
1209             PushDouble(nVal);
1210         }
1211     }
1212 }
1213 
ScVariationen2()1214 void ScInterpreter::ScVariationen2()
1215 {
1216     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScVariationen2" );
1217     if ( MustHaveParamCount( GetByte(), 2 ) )
1218     {
1219         double k = ::rtl::math::approxFloor(GetDouble());
1220         double n = ::rtl::math::approxFloor(GetDouble());
1221         if (n < 0.0 || k < 0.0 || k > n)
1222             PushIllegalArgument();
1223         else
1224             PushDouble(pow(n,k));
1225     }
1226 }
1227 
1228 
GetBinomDistPMF(double x,double n,double p)1229 double ScInterpreter::GetBinomDistPMF(double x, double n, double p)
1230 // used in ScB and ScBinomDist
1231 // preconditions: 0.0 <= x <= n, 0.0 < p < 1.0;  x,n integral although double
1232         {
1233     double q = (0.5 - p) + 0.5;
1234             double fFactor = pow(q, n);
1235     if (fFactor <=::std::numeric_limits<double>::min())
1236             {
1237                 fFactor = pow(p, n);
1238         if (fFactor <= ::std::numeric_limits<double>::min())
1239             return GetBetaDistPDF(p, x+1.0, n-x+1.0)/(n+1.0);
1240                 else
1241                 {
1242             sal_uInt32 max = static_cast<sal_uInt32>(n - x);
1243             for (sal_uInt32 i = 0; i < max && fFactor > 0.0; i++)
1244                         fFactor *= (n-i)/(i+1)*q/p;
1245             return fFactor;
1246                 }
1247             }
1248             else
1249             {
1250         sal_uInt32 max = static_cast<sal_uInt32>(x);
1251         for (sal_uInt32 i = 0; i < max && fFactor > 0.0; i++)
1252                     fFactor *= (n-i)/(i+1)*p/q;
1253         return fFactor;
1254         }
1255     }
1256 
lcl_GetBinomDistRange(double n,double xs,double xe,double fFactor,double p,double q)1257 double lcl_GetBinomDistRange(double n, double xs,double xe,
1258             double fFactor /* q^n */, double p, double q)
1259 //preconditions: 0.0 <= xs < xe <= n; xs,xe,n integral although double
1260                 {
1261     sal_uInt32 i;
1262     double fSum;
1263     // skip summands index 0 to xs-1, start sum with index xs
1264     sal_uInt32 nXs = static_cast<sal_uInt32>( xs );
1265     for (i = 1; i <= nXs && fFactor > 0.0; i++)
1266         fFactor *= (n-i+1)/i * p/q;
1267     fSum = fFactor; // Summand xs
1268     sal_uInt32 nXe = static_cast<sal_uInt32>(xe);
1269     for (i = nXs+1; i <= nXe && fFactor > 0.0; i++)
1270                     {
1271         fFactor *= (n-i+1)/i * p/q;
1272                         fSum += fFactor;
1273                     }
1274     return (fSum>1.0) ? 1.0 : fSum;
1275                 }
1276 
ScB()1277 void ScInterpreter::ScB()
1278 {
1279     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScB" );
1280     sal_uInt8 nParamCount = GetByte();
1281     if ( !MustHaveParamCount( nParamCount, 3, 4 ) )
1282         return ;
1283     if (nParamCount == 3)   // mass function
1284     {
1285         double x = ::rtl::math::approxFloor(GetDouble());
1286         double p = GetDouble();
1287         double n = ::rtl::math::approxFloor(GetDouble());
1288         if (n < 0.0 || x < 0.0 || x > n || p < 0.0 || p > 1.0)
1289             PushIllegalArgument();
1290         else
1291             if (p == 0.0)
1292                 PushDouble( (x == 0.0) ? 1.0 : 0.0 );
1293             else
1294                 if ( p == 1.0)
1295                     PushDouble( (x == n) ? 1.0 : 0.0);
1296                 else
1297                     PushDouble(GetBinomDistPMF(x,n,p));
1298             }
1299             else
1300     {   // nParamCount == 4
1301         double xe = ::rtl::math::approxFloor(GetDouble());
1302         double xs = ::rtl::math::approxFloor(GetDouble());
1303         double p = GetDouble();
1304         double n = ::rtl::math::approxFloor(GetDouble());
1305         double q = (0.5 - p) + 0.5;
1306         bool bIsValidX = ( 0.0 <= xs && xs <= xe && xe <= n);
1307         if ( bIsValidX && 0.0 < p && p < 1.0)
1308             {
1309             if (xs == xe)       // mass function
1310                 PushDouble(GetBinomDistPMF(xs,n,p));
1311             else
1312                 {
1313                 double fFactor = pow(q, n);
1314                 if (fFactor > ::std::numeric_limits<double>::min())
1315                     PushDouble(lcl_GetBinomDistRange(n,xs,xe,fFactor,p,q));
1316                 else
1317                 {
1318                     fFactor = pow(p, n);
1319                     if (fFactor > ::std::numeric_limits<double>::min())
1320                     {
1321                     // sum from j=xs to xe {(n choose j) * p^j * q^(n-j)}
1322                     // = sum from i = n-xe to n-xs { (n choose i) * q^i * p^(n-i)}
1323                         PushDouble(lcl_GetBinomDistRange(n,n-xe,n-xs,fFactor,q,p));
1324                 }
1325                 else
1326                         PushDouble(GetBetaDist(q,n-xe,xe+1.0)-GetBetaDist(q,n-xs+1,xs) );
1327                 }
1328             }
1329         }
1330         else
1331         {
1332             if ( bIsValidX ) // not(0<p<1)
1333             {
1334                 if ( p == 0.0 )
1335                     PushDouble( (xs == 0.0) ? 1.0 : 0.0 );
1336                 else if ( p == 1.0 )
1337                     PushDouble( (xe == n) ? 1.0 : 0.0 );
1338                 else
1339                     PushIllegalArgument();
1340             }
1341             else
1342                 PushIllegalArgument();
1343         }
1344     }
1345 }
1346 
ScBinomDist()1347 void ScInterpreter::ScBinomDist()
1348 {
1349     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScBinomDist" );
1350     if ( MustHaveParamCount( GetByte(), 4 ) )
1351     {
1352         bool bIsCum   = GetBool();     // false=mass function; true=cumulative
1353         double p      = GetDouble();
1354         double n      = ::rtl::math::approxFloor(GetDouble());
1355         double x      = ::rtl::math::approxFloor(GetDouble());
1356         double q = (0.5 - p) + 0.5;           // get one bit more for p near 1.0
1357         double fFactor, fSum;
1358         if (n < 0.0 || x < 0.0 || x > n || p < 0.0 || p > 1.0)
1359         {
1360             PushIllegalArgument();
1361             return;
1362             }
1363         if ( p == 0.0)
1364             {
1365             PushDouble( (x==0.0 || bIsCum) ? 1.0 : 0.0 );
1366             return;
1367             }
1368         if ( p == 1.0)
1369         {
1370             PushDouble( (x==n) ? 1.0 : 0.0);
1371             return;
1372         }
1373         if (!bIsCum)
1374             PushDouble( GetBinomDistPMF(x,n,p));
1375         else
1376         {
1377             if (x == n)
1378                 PushDouble(1.0);
1379             else
1380             {
1381                 fFactor = pow(q, n);
1382                 if (x == 0.0)
1383                     PushDouble(fFactor);
1384                 else
1385                     if (fFactor <= ::std::numeric_limits<double>::min())
1386                 {
1387                     fFactor = pow(p, n);
1388                         if (fFactor <= ::std::numeric_limits<double>::min())
1389                             PushDouble(GetBetaDist(q,n-x,x+1.0));
1390                     else
1391                     {
1392                             if (fFactor > fMachEps)
1393                             {
1394                         fSum = 1.0 - fFactor;
1395                                 sal_uInt32 max = static_cast<sal_uInt32> (n - x) - 1;
1396                                 for (sal_uInt32 i = 0; i < max && fFactor > 0.0; i++)
1397                         {
1398                             fFactor *= (n-i)/(i+1)*q/p;
1399                             fSum -= fFactor;
1400                         }
1401                                 PushDouble( (fSum < 0.0) ? 0.0 : fSum );
1402                 }
1403                 else
1404                                 PushDouble(lcl_GetBinomDistRange(n,n-x,n,fFactor,q,p));
1405                     }
1406                 }
1407                     else
1408                         PushDouble( lcl_GetBinomDistRange(n,0.0,x,fFactor,p,q)) ;
1409             }
1410         }
1411     }
1412 }
1413 
ScCritBinom()1414 void ScInterpreter::ScCritBinom()
1415 {
1416     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCritBinom" );
1417     if ( MustHaveParamCount( GetByte(), 3 ) )
1418     {
1419         double alpha  = GetDouble();                    // alpha
1420         double p      = GetDouble();                    // p
1421         double n      = ::rtl::math::approxFloor(GetDouble());
1422         if (n < 0.0 || alpha <= 0.0 || alpha >= 1.0 || p < 0.0 || p > 1.0)
1423             PushIllegalArgument();
1424         else
1425         {
1426             double q = 1.0 - p;
1427             double fFactor = pow(q,n);
1428             if (fFactor == 0.0)
1429             {
1430                 fFactor = pow(p, n);
1431                 if (fFactor == 0.0)
1432                     PushNoValue();
1433                 else
1434                 {
1435                     double fSum = 1.0 - fFactor; sal_uLong max = (sal_uLong) n;
1436                     sal_uLong i;
1437 
1438                     for ( i = 0; i < max && fSum >= alpha; i++)
1439                     {
1440                         fFactor *= (n-i)/(i+1)*q/p;
1441                         fSum -= fFactor;
1442                     }
1443                     PushDouble(n-i);
1444                 }
1445             }
1446             else
1447             {
1448                 double fSum = fFactor; sal_uLong max = (sal_uLong) n;
1449                 sal_uLong i;
1450 
1451                 for ( i = 0; i < max && fSum < alpha; i++)
1452                 {
1453                     fFactor *= (n-i)/(i+1)*p/q;
1454                     fSum += fFactor;
1455                 }
1456                 PushDouble(i);
1457             }
1458         }
1459     }
1460 }
1461 
ScNegBinomDist()1462 void ScInterpreter::ScNegBinomDist()
1463 {
1464     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNegBinomDist" );
1465     if ( MustHaveParamCount( GetByte(), 3 ) )
1466     {
1467         double p      = GetDouble();                    // p
1468         double r      = GetDouble();                    // r
1469         double x      = GetDouble();                    // x
1470         if (r < 0.0 || x < 0.0 || p < 0.0 || p > 1.0)
1471             PushIllegalArgument();
1472         else
1473         {
1474             double q = 1.0 - p;
1475             double fFactor = pow(p,r);
1476             for (double i = 0.0; i < x; i++)
1477                 fFactor *= (i+r)/(i+1.0)*q;
1478             PushDouble(fFactor);
1479         }
1480     }
1481 }
1482 
ScNormDist()1483 void ScInterpreter::ScNormDist()
1484 {
1485     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNormDist" );
1486     sal_uInt8 nParamCount = GetByte();
1487     if ( !MustHaveParamCount( nParamCount, 3, 4))
1488         return;
1489     bool bCumulative = nParamCount == 4 ? GetBool() : true;
1490     double sigma = GetDouble();                 // standard deviation
1491     double mue = GetDouble();                   // mean
1492     double x = GetDouble();                     // x
1493     if (sigma <= 0.0)
1494     {
1495         PushIllegalArgument();
1496         return;
1497     }
1498     if (bCumulative)
1499         PushDouble(integralPhi((x-mue)/sigma));
1500     else
1501         PushDouble(phi((x-mue)/sigma)/sigma);
1502 }
1503 
ScLogNormDist()1504 void ScInterpreter::ScLogNormDist() //expanded, see #i100119#
1505 {
1506     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLogNormDist" );
1507     sal_uInt8 nParamCount = GetByte();
1508     if ( !MustHaveParamCount( nParamCount, 1, 4))
1509         return;
1510     bool bCumulative = nParamCount == 4 ? GetBool() : true; // cumulative
1511     double sigma = nParamCount >= 3 ? GetDouble() : 1.0; // standard deviation
1512     double mue = nParamCount >= 2 ? GetDouble() : 0.0;   // mean
1513     double x = GetDouble();                              // x
1514     if (sigma <= 0.0)
1515     {
1516         PushIllegalArgument();
1517         return;
1518     }
1519     if (bCumulative)
1520     { // cumulative
1521         if (x <= 0.0)
1522             PushDouble(0.0);
1523         else
1524             PushDouble(integralPhi((log(x)-mue)/sigma));
1525     }
1526     else
1527     { // density
1528         if (x <= 0.0)
1529             PushIllegalArgument();
1530         else
1531             PushDouble(phi((log(x)-mue)/sigma)/sigma/x);
1532     }
1533 }
1534 
ScStdNormDist()1535 void ScInterpreter::ScStdNormDist()
1536 {
1537     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScStdNormDist" );
1538     PushDouble(integralPhi(GetDouble()));
1539 }
1540 
ScExpDist()1541 void ScInterpreter::ScExpDist()
1542 {
1543     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScExpDist" );
1544     if ( MustHaveParamCount( GetByte(), 3 ) )
1545     {
1546         double kum    = GetDouble();                    // 0 oder 1
1547         double lambda = GetDouble();                    // lambda
1548         double x      = GetDouble();                    // x
1549         if (lambda <= 0.0)
1550             PushIllegalArgument();
1551         else if (kum == 0.0)                        // Dichte
1552         {
1553             if (x >= 0.0)
1554                 PushDouble(lambda * exp(-lambda*x));
1555             else
1556                 PushInt(0);
1557         }
1558         else                                        // Verteilung
1559         {
1560             if (x > 0.0)
1561                 PushDouble(-expm1(-lambda*x));
1562             else
1563                 PushInt(0);
1564         }
1565     }
1566 }
1567 
ScTDist()1568 void ScInterpreter::ScTDist()
1569 {
1570     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTDist" );
1571     if ( !MustHaveParamCount( GetByte(), 3 ) )
1572         return;
1573     double fFlag = ::rtl::math::approxFloor(GetDouble());
1574     double fDF   = ::rtl::math::approxFloor(GetDouble());
1575     double T     = GetDouble();
1576     if (fDF < 1.0 || T < 0.0 || (fFlag != 1.0 && fFlag != 2.0) )
1577     {
1578         PushIllegalArgument();
1579         return;
1580     }
1581     double R = GetTDist(T, fDF);
1582     if (fFlag == 1.0)
1583         PushDouble(R);
1584     else
1585         PushDouble(2.0*R);
1586 }
1587 
ScFDist()1588 void ScInterpreter::ScFDist()
1589 {
1590     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFDist" );
1591     if ( !MustHaveParamCount( GetByte(), 3 ) )
1592         return;
1593     double fF2 = ::rtl::math::approxFloor(GetDouble());
1594     double fF1 = ::rtl::math::approxFloor(GetDouble());
1595     double fF  = GetDouble();
1596     if (fF < 0.0 || fF1 < 1.0 || fF2 < 1.0 || fF1 >= 1.0E10 || fF2 >= 1.0E10)
1597     {
1598         PushIllegalArgument();
1599         return;
1600     }
1601     PushDouble(GetFDist(fF, fF1, fF2));
1602 }
1603 
1604 // r1*x/(r1*x+r2) without forming inf/inf when r1*x overflows.
lcl_FDistLeftBetaArg(double fF1,double fF2,double fX)1605 static double lcl_FDistLeftBetaArg( double fF1, double fF2, double fX )
1606 {
1607     double fR = fF1 * fX;
1608     return ::rtl::math::isFinite( fR ) ? fR / (fR + fF2) : 1.0;
1609 }
1610 
ScFDistLeft()1611 void ScInterpreter::ScFDistLeft()
1612 {
1613     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFDistLeft" );
1614     sal_uInt8 nParamCount = GetByte();
1615     if ( !MustHaveParamCount( nParamCount, 3, 4 ) )
1616         return;
1617     bool bCumulative = ( nParamCount == 4 ? GetBool() : true );
1618     double fF2 = ::rtl::math::approxFloor(GetDouble());
1619     double fF1 = ::rtl::math::approxFloor(GetDouble());
1620     double fF  = GetDouble();
1621     if (fF1 < 1.0 || fF2 < 1.0 || fF1 >= 1.0E10 || fF2 >= 1.0E10)
1622     {
1623         PushIllegalArgument();
1624         return;
1625     }
1626     if (fF < 0.0)
1627     {
1628         PushDouble(0.0);
1629         return;
1630     }
1631     // F(x;r1;r2) is Beta(r1/2;r2/2) at y = r1*x/(r1*x+r2).
1632     double fDenom = fF1 * fF + fF2;
1633     double fY = lcl_FDistLeftBetaArg( fF1, fF2, fF );
1634     if (bCumulative)
1635         PushDouble( GetBetaDist( fY, fF1 / 2.0, fF2 / 2.0));
1636     else if (fY >= 1.0 || fDenom > 1.0E150)
1637     {
1638         // y has rounded to 1 or denom^2 would overflow: use the density in log form
1639         double fA = fF1 / 2.0;
1640         double fB = fF2 / 2.0;
1641         double fLogDenom = log( fF1) + log( fF) + std::log1p( fF2 / (fF1 * fF));
1642         PushDouble( exp( (fA - 1.0) * log( fF) + fA * log( fF1) + fB * log( fF2)
1643                          - GetLogBeta( fA, fB) - (fA + fB) * fLogDenom));
1644     }
1645     else
1646         PushDouble( GetBetaDistPDF( fY, fF1 / 2.0, fF2 / 2.0) * fF1 * fF2 / (fDenom * fDenom));
1647 }
1648 
ScChiDist()1649 void ScInterpreter::ScChiDist()
1650 {
1651     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScChiDist" );
1652     double fResult;
1653     if ( !MustHaveParamCount( GetByte(), 2 ) )
1654         return;
1655     double fDF  = ::rtl::math::approxFloor(GetDouble());
1656     double fChi = GetDouble();
1657     if (fDF < 1.0) // x<=0 returns 1, see ODFF 6.17.10
1658     {
1659         PushIllegalArgument();
1660         return;
1661     }
1662     fResult = GetChiDist( fChi, fDF);
1663     if (nGlobalError)
1664     {
1665         PushError( nGlobalError);
1666         return;
1667     }
1668     PushDouble(fResult);
1669 }
1670 
ScWeibull()1671 void ScInterpreter::ScWeibull()
1672 {
1673     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScWeibull" );
1674     if ( MustHaveParamCount( GetByte(), 4 ) )
1675     {
1676         double kum   = GetDouble();                 // 0 oder 1
1677         double beta  = GetDouble();                 // beta
1678         double alpha = GetDouble();                 // alpha
1679         double x     = GetDouble();                 // x
1680         if (alpha <= 0.0 || beta <= 0.0 || x < 0.0)
1681             PushIllegalArgument();
1682         else if (kum == 0.0)                        // Dichte
1683             PushDouble(alpha/pow(beta,alpha)*pow(x,alpha-1.0)*
1684                        exp(-pow(x/beta,alpha)));
1685         else                                        // Verteilung
1686             PushDouble(-expm1(-pow(x/beta,alpha)));
1687     }
1688 }
1689 
ScPoissonDist()1690 void ScInterpreter::ScPoissonDist()
1691 {
1692     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPoissonDist" );
1693     sal_uInt8 nParamCount = GetByte();
1694     if ( MustHaveParamCount( nParamCount, 2, 3 ) )
1695     {
1696         bool bCumulative = (nParamCount == 3 ? GetBool() : true); // default cumulative
1697         double lambda    = GetDouble();                           // Mean
1698         double x         = ::rtl::math::approxFloor(GetDouble()); // discrete distribution
1699         if (lambda < 0.0 || x < 0.0)
1700             PushIllegalArgument();
1701         else if (!bCumulative)                            // Probability mass function
1702         {
1703             if (lambda == 0.0)
1704                 PushInt(0);
1705             else
1706             {
1707                 if (lambda >712)    // underflow in exp(-lambda)
1708                 {   // accuracy 11 Digits
1709                     PushDouble( exp(x*log(lambda)-lambda-GetLogGamma(x+1.0)));
1710                 }
1711                 else
1712                 {
1713                     double fPoissonVar = 1.0;
1714                     for ( double f = 0.0; f < x; ++f )
1715                         fPoissonVar *= lambda / ( f + 1.0 );
1716                     PushDouble( fPoissonVar * exp( -lambda ) );
1717                 }
1718             }
1719         }
1720         else                                // Cumulative distribution function
1721         {
1722             if (lambda == 0.0)
1723                 PushInt(1);
1724             else
1725             {
1726                 if (lambda > 712 )  // underflow in exp(-lambda)
1727                 {   // accuracy 12 Digits
1728                     PushDouble(GetUpRegIGamma(x+1.0,lambda));
1729                 }
1730                 else
1731                 {
1732                     if (x >= 936.0) // result is always undistinghable from 1
1733                         PushDouble (1.0);
1734                     else
1735                     {
1736                         double fSummand = exp(-lambda);
1737                         double fSum = fSummand;
1738                         int nEnd = sal::static_int_cast<int>( x );
1739                         for (int i = 1; i <= nEnd; i++)
1740                         {
1741                             fSummand = (fSummand * lambda)/(double)i;
1742                             fSum += fSummand;
1743                         }
1744                         PushDouble(fSum);
1745                     }
1746                 }
1747             }
1748         }
1749     }
1750 }
1751 
1752 /** Calculates a value of the hypergeometric distribution.
1753 
1754     C(M,x) * C(N-M,n-x) / C(N,n) is the product of the x factors of C(n,x),
1755     which are at least 1, and of n ratios, which are at most 1. Taking a
1756     ratio whenever the product has reached 1 and a factor otherwise keeps it
1757     from overflowing on the way to a result of at most 1.
1758  */
GetHypGeomDist(double x,double n,double M,double N)1759 double ScInterpreter::GetHypGeomDist( double x, double n, double M, double N )
1760 {
1761     if( (x < 0.0) || (n < x) || (M < x) || (N < n) || (N < M) || (x < n - N + M) )
1762     {
1763         SetError(errIllegalArgument);
1764         return 0.0;
1765     }
1766     if ( x + ::std::min( n, M ) > 500000.0 )    // arbitrary limit, as before
1767     {
1768         SetError(errNoValue);
1769         return 0.0;
1770     }
1771 
1772     // The same value with the fewest factors: the drawn and the remaining
1773     // items swap roles, and so do the sample and the successes.
1774     if ( n > N - n )
1775     {
1776         x = M - x;
1777         n = N - n;
1778     }
1779     if ( n > M )
1780         ::std::swap( n, M );
1781 
1782     double fRes = 1.0;
1783     double fFactors = 0.0;
1784     double fRatios = 0.0;
1785     while ( fFactors < x || fRatios < n )
1786     {
1787         if ( fFactors < x && (fRes < 1.0 || fRatios >= n) )
1788         {
1789             fFactors += 1.0;
1790             fRes *= (n - x + fFactors) / fFactors;
1791         }
1792         else
1793         {
1794             if ( fRatios < x )
1795                 fRes *= (M - fRatios) / (N - fRatios);
1796             else
1797                 fRes *= (N - M - fRatios + x) / (N - fRatios);
1798             fRatios += 1.0;
1799         }
1800     }
1801     return fRes;
1802 }
1803 
ScHypGeomDist()1804 void ScInterpreter::ScHypGeomDist()
1805 {
1806     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScHypGeomDist" );
1807     sal_uInt8 nParamCount = GetByte();
1808     if ( !MustHaveParamCount( nParamCount, 4, 5 ) )
1809         return;
1810 
1811     bool bCumulative = ( nParamCount == 5 ? GetBool() : false );
1812     double N = ::rtl::math::approxFloor(GetDouble());
1813     double M = ::rtl::math::approxFloor(GetDouble());
1814     double n = ::rtl::math::approxFloor(GetDouble());
1815     double x = ::rtl::math::approxFloor(GetDouble());
1816 
1817     double fVal = GetHypGeomDist( x, n, M, N );
1818     if (bCumulative && !nGlobalError)
1819     {
1820         // Sum the smaller tail with the term ratios, walking away from the
1821         // mode so the terms only shrink and the summed value stays accurate.
1822         double fMode = ::rtl::math::approxFloor( (n + 1.0) * (M + 1.0) / (N + 2.0));
1823         double fSum = 0.0;
1824         double fTerm = fVal;
1825         if (x < fMode)
1826         {
1827             double fLow = ::std::max( 0.0, n - N + M);
1828             for (double k = x; k > fLow; --k)
1829             {
1830                 fSum += fTerm;
1831                 fTerm *= k * (N - M - n + k) / ((M - k + 1.0) * (n - k + 1.0));
1832             }
1833             fVal = fSum + fTerm;
1834         }
1835         else
1836         {
1837             double fHigh = ::std::min( n, M);
1838             for (double k = x; k < fHigh; ++k)
1839             {
1840                 fTerm *= (M - k) * (n - k) / ((k + 1.0) * (N - M - n + k + 1.0));
1841                 fSum += fTerm;
1842             }
1843             fVal = 1.0 - fSum;
1844         }
1845     }
1846     PushDouble( fVal);
1847 }
1848 
ScGammaDist()1849 void ScInterpreter::ScGammaDist()
1850 {
1851     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGammaDist" );
1852     sal_uInt8 nParamCount = GetByte();
1853     if ( !MustHaveParamCount( nParamCount, 3, 4 ) )
1854         return;
1855     double bCumulative;
1856     if (nParamCount == 4)
1857         bCumulative = GetBool();
1858     else
1859         bCumulative = true;
1860     double fBeta = GetDouble();                 // scale
1861     double fAlpha = GetDouble();                // shape
1862     double fX = GetDouble();                    // x
1863     if (fAlpha <= 0.0 || fBeta <= 0.0)
1864         PushIllegalArgument();
1865     else
1866     {
1867         if (bCumulative)                        // distribution
1868             PushDouble( GetGammaDist( fX, fAlpha, fBeta));
1869         else                                    // density
1870             PushDouble( GetGammaDistPDF( fX, fAlpha, fBeta));
1871     }
1872 }
1873 
ScNormInv()1874 void ScInterpreter::ScNormInv()
1875 {
1876     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScNormInv" );
1877     if ( MustHaveParamCount( GetByte(), 3 ) )
1878     {
1879         double sigma = GetDouble();
1880         double mue   = GetDouble();
1881         double x     = GetDouble();
1882         if (sigma <= 0.0 || x < 0.0 || x > 1.0)
1883             PushIllegalArgument();
1884         else if (x == 0.0 || x == 1.0)
1885             PushNoValue();
1886         else
1887             PushDouble(gaussinv(x)*sigma + mue);
1888     }
1889 }
1890 
ScSNormInv()1891 void ScInterpreter::ScSNormInv()
1892 {
1893     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSNormInv" );
1894     double x = GetDouble();
1895     if (x < 0.0 || x > 1.0)
1896         PushIllegalArgument();
1897     else if (x == 0.0 || x == 1.0)
1898         PushNoValue();
1899     else
1900         PushDouble(gaussinv(x));
1901 }
1902 
ScLogNormInv()1903 void ScInterpreter::ScLogNormInv()
1904 {
1905     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLogNormInv" );
1906     if ( MustHaveParamCount( GetByte(), 3 ) )
1907     {
1908         double sigma = GetDouble();                 // Stdabw
1909         double mue = GetDouble();                   // Mittelwert
1910         double y = GetDouble();                     // y
1911         if (sigma <= 0.0 || y <= 0.0 || y >= 1.0)
1912             PushIllegalArgument();
1913         else
1914             PushDouble(exp(mue+sigma*gaussinv(y)));
1915     }
1916 }
1917 
1918 class ScGammaDistFunction : public ScDistFunc
1919 {
1920     ScInterpreter&  rInt;
1921     double          fp, fAlpha, fBeta;
1922 
1923 public:
ScGammaDistFunction(ScInterpreter & rI,double fpVal,double fAlphaVal,double fBetaVal)1924             ScGammaDistFunction( ScInterpreter& rI, double fpVal, double fAlphaVal, double fBetaVal ) :
1925                 rInt(rI), fp(fpVal), fAlpha(fAlphaVal), fBeta(fBetaVal) {}
1926 
GetValue(double x) const1927     double  GetValue( double x ) const  { return fp - rInt.GetGammaDist(x, fAlpha, fBeta); }
1928 };
1929 
ScGammaInv()1930 void ScInterpreter::ScGammaInv()
1931 {
1932     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGammaInv" );
1933     if ( !MustHaveParamCount( GetByte(), 3 ) )
1934         return;
1935     double fBeta  = GetDouble();
1936     double fAlpha = GetDouble();
1937     double fP = GetDouble();
1938     if (fAlpha <= 0.0 || fBeta <= 0.0 || fP < 0.0 || fP >= 1.0 )
1939     {
1940         PushIllegalArgument();
1941         return;
1942     }
1943     if (fP == 0.0)
1944         PushInt(0);
1945     else
1946     {
1947         bool bConvError;
1948         ScGammaDistFunction aFunc( *this, fP, fAlpha, fBeta );
1949         double fStart = fAlpha * fBeta;
1950         double fVal = lcl_IterateInverse( aFunc, fStart*0.5, fStart, bConvError );
1951         if (bConvError)
1952             SetError(errNoConvergence);
1953         PushDouble(fVal);
1954     }
1955 }
1956 
1957 class ScBetaDistFunction : public ScDistFunc
1958 {
1959     ScInterpreter&  rInt;
1960     double          fp, fAlpha, fBeta;
1961 
1962 public:
ScBetaDistFunction(ScInterpreter & rI,double fpVal,double fAlphaVal,double fBetaVal)1963             ScBetaDistFunction( ScInterpreter& rI, double fpVal, double fAlphaVal, double fBetaVal ) :
1964                 rInt(rI), fp(fpVal), fAlpha(fAlphaVal), fBeta(fBetaVal) {}
1965 
GetValue(double x) const1966     double  GetValue( double x ) const  { return fp - rInt.GetBetaDist(x, fAlpha, fBeta); }
1967 };
1968 
ScBetaInv()1969 void ScInterpreter::ScBetaInv()
1970 {
1971     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScBetaInv" );
1972     sal_uInt8 nParamCount = GetByte();
1973     if ( !MustHaveParamCount( nParamCount, 3, 5 ) )
1974         return;
1975     double fP, fA, fB, fAlpha, fBeta;
1976     if (nParamCount == 5)
1977         fB = GetDouble();
1978     else
1979         fB = 1.0;
1980     if (nParamCount >= 4)
1981         fA = GetDouble();
1982     else
1983         fA = 0.0;
1984     fBeta  = GetDouble();
1985     fAlpha = GetDouble();
1986     fP     = GetDouble();
1987     if (fP < 0.0 || fP >= 1.0 || fA == fB || fAlpha <= 0.0 || fBeta <= 0.0)
1988     {
1989         PushIllegalArgument();
1990         return;
1991     }
1992     if (fP == 0.0)
1993         PushInt(0);
1994     else
1995     {
1996         bool bConvError;
1997         ScBetaDistFunction aFunc( *this, fP, fAlpha, fBeta );
1998         // 0..1 as range for iteration so it isn't extended beyond the valid range
1999         double fVal = lcl_IterateInverse( aFunc, 0.0, 1.0, bConvError );
2000         if (bConvError)
2001             PushError( errNoConvergence);
2002         else
2003             PushDouble(fA + fVal*(fB-fA));                  // scale to (A,B)
2004     }
2005 }
2006 
2007                                                             // Achtung: T, F und Chi
2008                                                             // sind monoton fallend,
2009                                                             // deshalb 1-Dist als Funktion
2010 
2011 class ScTDistFunction : public ScDistFunc
2012 {
2013     ScInterpreter&  rInt;
2014     double          fp, fDF;
2015 
2016 public:
ScTDistFunction(ScInterpreter & rI,double fpVal,double fDFVal)2017             ScTDistFunction( ScInterpreter& rI, double fpVal, double fDFVal ) :
2018                 rInt(rI), fp(fpVal), fDF(fDFVal) {}
2019 
GetValue(double x) const2020     double  GetValue( double x ) const  { return fp - 2 * rInt.GetTDist(x, fDF); }
2021 };
2022 
ScTInv()2023 void ScInterpreter::ScTInv()
2024 {
2025     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTInv" );
2026     if ( !MustHaveParamCount( GetByte(), 2 ) )
2027         return;
2028     double fDF  = ::rtl::math::approxFloor(GetDouble());
2029     double fP = GetDouble();
2030     if (fDF < 1.0 || fDF >= 1.0E5 || fP <= 0.0 || fP > 1.0 )
2031     {
2032         PushIllegalArgument();
2033         return;
2034     }
2035 
2036     bool bConvError;
2037     ScTDistFunction aFunc( *this, fP, fDF );
2038     double fVal = lcl_IterateInverse( aFunc, fDF*0.5, fDF, bConvError );
2039     if (bConvError)
2040         SetError(errNoConvergence);
2041     PushDouble(fVal);
2042 }
2043 
2044 class ScFDistLeftFunction : public ScDistFunc
2045 {
2046     ScInterpreter&  rInt;
2047     double          fp, fF1, fF2;
2048 
2049 public:
ScFDistLeftFunction(ScInterpreter & rI,double fpVal,double fF1Val,double fF2Val)2050             ScFDistLeftFunction( ScInterpreter& rI, double fpVal, double fF1Val, double fF2Val ) :
2051                 rInt(rI), fp(fpVal), fF1(fF1Val), fF2(fF2Val) {}
2052 
GetValue(double x) const2053     double  GetValue( double x ) const  { return fp - rInt.GetBetaDist( lcl_FDistLeftBetaArg( fF1, fF2, x), fF1 / 2.0, fF2 / 2.0); }
2054 };
2055 
2056 class ScFDistFunction : public ScDistFunc
2057 {
2058     ScInterpreter&  rInt;
2059     double          fp, fF1, fF2;
2060 
2061 public:
ScFDistFunction(ScInterpreter & rI,double fpVal,double fF1Val,double fF2Val)2062             ScFDistFunction( ScInterpreter& rI, double fpVal, double fF1Val, double fF2Val ) :
2063                 rInt(rI), fp(fpVal), fF1(fF1Val), fF2(fF2Val) {}
2064 
GetValue(double x) const2065     double  GetValue( double x ) const  { return fp - rInt.GetFDist(x, fF1, fF2); }
2066 };
2067 
ScFInv()2068 void ScInterpreter::ScFInv()
2069 {
2070     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFInv" );
2071     if ( !MustHaveParamCount( GetByte(), 3 ) )
2072         return;
2073     double fF2 = ::rtl::math::approxFloor(GetDouble());
2074     double fF1 = ::rtl::math::approxFloor(GetDouble());
2075     double fP  = GetDouble();
2076     if (fP <= 0.0 || fF1 < 1.0 || fF2 < 1.0 || fF1 >= 1.0E10 || fF2 >= 1.0E10 || fP > 1.0)
2077     {
2078         PushIllegalArgument();
2079         return;
2080     }
2081 
2082     bool bConvError;
2083     ScFDistFunction aFunc( *this, fP, fF1, fF2 );
2084     double fVal = lcl_IterateInverse( aFunc, fF1*0.5, fF1, bConvError );
2085     if (bConvError)
2086         SetError(errNoConvergence);
2087     PushDouble(fVal);
2088 }
2089 
ScFInvLeft()2090 void ScInterpreter::ScFInvLeft()
2091 {
2092     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFInvLeft" );
2093     if ( !MustHaveParamCount( GetByte(), 3 ) )
2094         return;
2095     double fF2 = ::rtl::math::approxFloor(GetDouble());
2096     double fF1 = ::rtl::math::approxFloor(GetDouble());
2097     double fP  = GetDouble();
2098     if (fP < 0.0 || fP >= 1.0 || fF1 < 1.0 || fF2 < 1.0 || fF1 >= 1.0E10 || fF2 >= 1.0E10)
2099     {
2100         PushIllegalArgument();
2101         return;
2102     }
2103     if (fP == 0.0)
2104     {
2105         PushDouble(0.0);
2106         return;
2107     }
2108 
2109     bool bConvError;
2110     ScFDistLeftFunction aFunc( *this, fP, fF1, fF2 );
2111     double fVal = lcl_IterateInverse( aFunc, fF1*0.5, fF1, bConvError );
2112     if (bConvError)
2113         SetError(errNoConvergence);
2114     PushDouble(fVal);
2115 }
2116 
2117 class ScChiDistFunction : public ScDistFunc
2118 {
2119     ScInterpreter&  rInt;
2120     double          fp, fDF;
2121 
2122 public:
ScChiDistFunction(ScInterpreter & rI,double fpVal,double fDFVal)2123             ScChiDistFunction( ScInterpreter& rI, double fpVal, double fDFVal ) :
2124                 rInt(rI), fp(fpVal), fDF(fDFVal) {}
2125 
GetValue(double x) const2126     double  GetValue( double x ) const  { return fp - rInt.GetChiDist(x, fDF); }
2127 };
2128 
ScChiInv()2129 void ScInterpreter::ScChiInv()
2130 {
2131     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScChiInv" );
2132     if ( !MustHaveParamCount( GetByte(), 2 ) )
2133         return;
2134     double fDF  = ::rtl::math::approxFloor(GetDouble());
2135     double fP = GetDouble();
2136     if (fDF < 1.0 || fP <= 0.0 || fP > 1.0 )
2137     {
2138         PushIllegalArgument();
2139         return;
2140     }
2141 
2142     bool bConvError;
2143     ScChiDistFunction aFunc( *this, fP, fDF );
2144     double fVal = lcl_IterateInverse( aFunc, fDF*0.5, fDF, bConvError );
2145     if (bConvError)
2146         SetError(errNoConvergence);
2147     PushDouble(fVal);
2148 }
2149 
2150 /***********************************************/
2151 class ScChiSqDistFunction : public ScDistFunc
2152 {
2153     ScInterpreter&  rInt;
2154     double          fp, fDF;
2155 
2156 public:
ScChiSqDistFunction(ScInterpreter & rI,double fpVal,double fDFVal)2157             ScChiSqDistFunction( ScInterpreter& rI, double fpVal, double fDFVal ) :
2158                 rInt(rI), fp(fpVal), fDF(fDFVal) {}
2159 
GetValue(double x) const2160     double  GetValue( double x ) const  { return fp - rInt.GetChiSqDistCDF(x, fDF); }
2161 };
2162 
2163 
ScChiSqInv()2164 void ScInterpreter::ScChiSqInv()
2165 {
2166     if ( !MustHaveParamCount( GetByte(), 2 ) )
2167         return;
2168     double fDF  = ::rtl::math::approxFloor(GetDouble());
2169     double fP = GetDouble();
2170     if (fDF < 1.0 || fP < 0.0 || fP >= 1.0 )
2171     {
2172         PushIllegalArgument();
2173         return;
2174     }
2175 
2176     bool bConvError;
2177     ScChiSqDistFunction aFunc( *this, fP, fDF );
2178     double fVal = lcl_IterateInverse( aFunc, fDF*0.5, fDF, bConvError );
2179     if (bConvError)
2180         SetError(errNoConvergence);
2181     PushDouble(fVal);
2182 }
2183 
2184 
ScConfidence()2185 void ScInterpreter::ScConfidence()
2186 {
2187     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScConfidence" );
2188     if ( MustHaveParamCount( GetByte(), 3 ) )
2189     {
2190         double n     = ::rtl::math::approxFloor(GetDouble());
2191         double sigma = GetDouble();
2192         double alpha = GetDouble();
2193         if (sigma <= 0.0 || alpha <= 0.0 || alpha >= 1.0 || n < 1.0)
2194             PushIllegalArgument();
2195         else
2196             PushDouble( gaussinv(1.0-alpha/2.0) * sigma/sqrt(n) );
2197     }
2198 }
2199 
ScZTest()2200 void ScInterpreter::ScZTest()
2201 {
2202     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScZTest" );
2203     sal_uInt8 nParamCount = GetByte();
2204     if ( !MustHaveParamCount( nParamCount, 2, 3 ) )
2205         return;
2206     double sigma = 0.0, mue, x;
2207     if (nParamCount == 3)
2208     {
2209         sigma = GetDouble();
2210         if (sigma <= 0.0)
2211         {
2212             PushIllegalArgument();
2213             return;
2214         }
2215     }
2216     x = GetDouble();
2217 
2218     double fSum    = 0.0;
2219     double fSumSqr = 0.0;
2220     double fVal;
2221     double rValCount = 0.0;
2222     switch (GetStackType())
2223     {
2224         case formula::svDouble :
2225         {
2226             fVal = GetDouble();
2227             fSum    += fVal;
2228             fSumSqr += fVal*fVal;
2229             rValCount++;
2230         }
2231         break;
2232         case svSingleRef :
2233         {
2234             ScAddress aAdr;
2235             PopSingleRef( aAdr );
2236             ScBaseCell* pCell = GetCell( aAdr );
2237             if (HasCellValueData(pCell))
2238             {
2239                 fVal = GetCellValue( aAdr, pCell );
2240                 fSum += fVal;
2241                 fSumSqr += fVal*fVal;
2242                 rValCount++;
2243             }
2244         }
2245         break;
2246         case svRefList :
2247         case formula::svDoubleRef :
2248         {
2249             short nParam = 1;
2250             size_t nRefInList = 0;
2251             while (nParam-- > 0)
2252             {
2253                 ScRange aRange;
2254                 sal_uInt16 nErr = 0;
2255                 PopDoubleRef( aRange, nParam, nRefInList);
2256                 ScValueIterator aValIter(pDok, aRange, glSubTotal);
2257                 if (aValIter.GetFirst(fVal, nErr))
2258                 {
2259                     fSum += fVal;
2260                     fSumSqr += fVal*fVal;
2261                     rValCount++;
2262                     while ((nErr == 0) && aValIter.GetNext(fVal, nErr))
2263                     {
2264                         fSum += fVal;
2265                         fSumSqr += fVal*fVal;
2266                         rValCount++;
2267                     }
2268                     SetError(nErr);
2269                 }
2270             }
2271         }
2272         break;
2273         case svMatrix :
2274         {
2275             ScMatrixRef pMat = PopMatrix();
2276             if (pMat)
2277             {
2278                 SCSIZE nCount = pMat->GetElementCount();
2279                 if (pMat->IsNumeric())
2280                 {
2281                     for ( SCSIZE i = 0; i < nCount; i++ )
2282                     {
2283                         fVal= pMat->GetDouble(i);
2284                         fSum += fVal;
2285                         fSumSqr += fVal * fVal;
2286                         rValCount++;
2287                     }
2288                 }
2289                 else
2290                 {
2291                     for (SCSIZE i = 0; i < nCount; i++)
2292                         if (!pMat->IsString(i))
2293                         {
2294                             fVal= pMat->GetDouble(i);
2295                             fSum += fVal;
2296                             fSumSqr += fVal * fVal;
2297                             rValCount++;
2298                         }
2299                 }
2300             }
2301         }
2302         break;
2303         default : SetError(errIllegalParameter); break;
2304     }
2305     if (rValCount <= 1.0)
2306         PushError( errDivisionByZero);
2307     else
2308     {
2309         mue = fSum/rValCount;
2310         if (nParamCount != 3)
2311         {
2312             sigma = (fSumSqr - fSum*fSum/rValCount)/(rValCount-1.0);
2313             PushDouble(0.5 - gauss((mue-x)/sqrt(sigma/rValCount)));
2314         }
2315         else
2316             PushDouble(0.5 - gauss((mue-x)*sqrt(rValCount)/sigma));
2317     }
2318 }
CalculateTest(sal_Bool _bTemplin,const SCSIZE nC1,const SCSIZE nC2,const SCSIZE nR1,const SCSIZE nR2,const ScMatrixRef & pMat1,const ScMatrixRef & pMat2,double & fT,double & fF)2319 bool ScInterpreter::CalculateTest(sal_Bool _bTemplin
2320                                   ,const SCSIZE nC1, const SCSIZE nC2,const SCSIZE nR1,const SCSIZE nR2
2321                                   ,const ScMatrixRef& pMat1,const ScMatrixRef& pMat2
2322                                   ,double& fT,double& fF)
2323 {
2324     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CalculateTest" );
2325     double fCount1  = 0.0;
2326     double fCount2  = 0.0;
2327     double fSum1    = 0.0;
2328     double fSumSqr1 = 0.0;
2329     double fSum2    = 0.0;
2330     double fSumSqr2 = 0.0;
2331     double fVal;
2332     SCSIZE i,j;
2333     for (i = 0; i < nC1; i++)
2334         for (j = 0; j < nR1; j++)
2335         {
2336             if (!pMat1->IsString(i,j))
2337             {
2338                 fVal = pMat1->GetDouble(i,j);
2339                 fSum1    += fVal;
2340                 fSumSqr1 += fVal * fVal;
2341                 fCount1++;
2342             }
2343         }
2344     for (i = 0; i < nC2; i++)
2345         for (j = 0; j < nR2; j++)
2346         {
2347             if (!pMat2->IsString(i,j))
2348             {
2349                 fVal = pMat2->GetDouble(i,j);
2350                 fSum2    += fVal;
2351                 fSumSqr2 += fVal * fVal;
2352                 fCount2++;
2353             }
2354         }
2355     if (fCount1 < 2.0 || fCount2 < 2.0)
2356     {
2357         PushNoValue();
2358         return false;
2359     } // if (fCount1 < 2.0 || fCount2 < 2.0)
2360     if ( _bTemplin )
2361     {
2362         double fS1 = (fSumSqr1-fSum1*fSum1/fCount1)/(fCount1-1.0)/fCount1;
2363         double fS2 = (fSumSqr2-fSum2*fSum2/fCount2)/(fCount2-1.0)/fCount2;
2364         if (fS1 + fS2 == 0.0)
2365         {
2366             PushNoValue();
2367             return false;
2368         }
2369         fT = fabs(fSum1/fCount1 - fSum2/fCount2)/sqrt(fS1+fS2);
2370         double c = fS1/(fS1+fS2);
2371 // s.u. fF = ::rtl::math::approxFloor(1.0/(c*c/(fCount1-1.0)+(1.0-c)*(1.0-c)/(fCount2-1.0)));
2372 //      fF = ::rtl::math::approxFloor((fS1+fS2)*(fS1+fS2)/(fS1*fS1/(fCount1-1.0) + fS2*fS2/(fCount2-1.0)));
2373 
2374     //  GetTDist wird mit GetBetaDist berechnet und kommt auch mit nicht ganzzahligen
2375     //  Freiheitsgraden klar. Dann stimmt das Ergebnis auch mit Excel ueberein (#52406#):
2376         fF = 1.0/(c*c/(fCount1-1.0)+(1.0-c)*(1.0-c)/(fCount2-1.0));
2377     }
2378     else
2379     {
2380         //  laut Bronstein-Semendjajew
2381         double fS1 = (fSumSqr1 - fSum1*fSum1/fCount1) / (fCount1 - 1.0);    // Varianz
2382         double fS2 = (fSumSqr2 - fSum2*fSum2/fCount2) / (fCount2 - 1.0);
2383         fT = fabs( fSum1/fCount1 - fSum2/fCount2 ) /
2384              sqrt( (fCount1-1.0)*fS1 + (fCount2-1.0)*fS2 ) *
2385              sqrt( fCount1*fCount2*(fCount1+fCount2-2)/(fCount1+fCount2) );
2386         fF = fCount1 + fCount2 - 2;
2387     }
2388     return true;
2389 }
ScTTest()2390 void ScInterpreter::ScTTest()
2391 {
2392     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTTest" );
2393     if ( !MustHaveParamCount( GetByte(), 4 ) )
2394         return;
2395     double fTyp = ::rtl::math::approxFloor(GetDouble());
2396     double fAnz = ::rtl::math::approxFloor(GetDouble());
2397     if (fAnz != 1.0 && fAnz != 2.0)
2398     {
2399         PushIllegalArgument();
2400         return;
2401     }
2402 
2403     ScMatrixRef pMat2 = GetMatrix();
2404     ScMatrixRef pMat1 = GetMatrix();
2405     if (!pMat1 || !pMat2)
2406     {
2407         PushIllegalParameter();
2408         return;
2409     }
2410     double fT, fF;
2411     SCSIZE nC1, nC2;
2412     SCSIZE nR1, nR2;
2413     SCSIZE i, j;
2414     pMat1->GetDimensions(nC1, nR1);
2415     pMat2->GetDimensions(nC2, nR2);
2416     if (fTyp == 1.0)
2417     {
2418         if (nC1 != nC2 || nR1 != nR2)
2419         {
2420             PushIllegalArgument();
2421             return;
2422         }
2423         double fCount   = 0.0;
2424         double fSum1    = 0.0;
2425         double fSum2    = 0.0;
2426         double fSumSqrD = 0.0;
2427         double fVal1, fVal2;
2428         for (i = 0; i < nC1; i++)
2429             for (j = 0; j < nR1; j++)
2430             {
2431                 if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
2432                 {
2433                     fVal1 = pMat1->GetDouble(i,j);
2434                     fVal2 = pMat2->GetDouble(i,j);
2435                     fSum1    += fVal1;
2436                     fSum2    += fVal2;
2437                     fSumSqrD += (fVal1 - fVal2)*(fVal1 - fVal2);
2438                     fCount++;
2439                 }
2440             }
2441         if (fCount < 1.0)
2442         {
2443             PushNoValue();
2444             return;
2445         }
2446         fT = sqrt(fCount-1.0) * fabs(fSum1 - fSum2) /
2447              sqrt(fCount * fSumSqrD - (fSum1-fSum2)*(fSum1-fSum2));
2448         fF = fCount - 1.0;
2449     }
2450     else if (fTyp == 2.0)
2451     {
2452         CalculateTest(sal_False,nC1, nC2,nR1, nR2,pMat1,pMat2,fT,fF);
2453     }
2454     else if (fTyp == 3.0)
2455     {
2456         CalculateTest(sal_True,nC1, nC2,nR1, nR2,pMat1,pMat2,fT,fF);
2457     }
2458 
2459     else
2460     {
2461         PushIllegalArgument();
2462         return;
2463     }
2464     if (fAnz == 1.0)
2465         PushDouble(GetTDist(fT, fF));
2466     else
2467         PushDouble(2.0*GetTDist(fT, fF));
2468 }
2469 
ScFTest()2470 void ScInterpreter::ScFTest()
2471 {
2472     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScFTest" );
2473     if ( !MustHaveParamCount( GetByte(), 2 ) )
2474         return;
2475     ScMatrixRef pMat2 = GetMatrix();
2476     ScMatrixRef pMat1 = GetMatrix();
2477     if (!pMat1 || !pMat2)
2478     {
2479         PushIllegalParameter();
2480         return;
2481     }
2482     SCSIZE nC1, nC2;
2483     SCSIZE nR1, nR2;
2484     SCSIZE i, j;
2485     pMat1->GetDimensions(nC1, nR1);
2486     pMat2->GetDimensions(nC2, nR2);
2487     double fCount1  = 0.0;
2488     double fCount2  = 0.0;
2489     double fSum1    = 0.0;
2490     double fSumSqr1 = 0.0;
2491     double fSum2    = 0.0;
2492     double fSumSqr2 = 0.0;
2493     double fVal;
2494     for (i = 0; i < nC1; i++)
2495         for (j = 0; j < nR1; j++)
2496         {
2497             if (!pMat1->IsString(i,j))
2498             {
2499                 fVal = pMat1->GetDouble(i,j);
2500                 fSum1    += fVal;
2501                 fSumSqr1 += fVal * fVal;
2502                 fCount1++;
2503             }
2504         }
2505     for (i = 0; i < nC2; i++)
2506         for (j = 0; j < nR2; j++)
2507         {
2508             if (!pMat2->IsString(i,j))
2509             {
2510                 fVal = pMat2->GetDouble(i,j);
2511                 fSum2    += fVal;
2512                 fSumSqr2 += fVal * fVal;
2513                 fCount2++;
2514             }
2515         }
2516     if (fCount1 < 2.0 || fCount2 < 2.0)
2517     {
2518         PushNoValue();
2519         return;
2520     }
2521     double fS1 = (fSumSqr1-fSum1*fSum1/fCount1)/(fCount1-1.0);
2522     double fS2 = (fSumSqr2-fSum2*fSum2/fCount2)/(fCount2-1.0);
2523     if (fS1 == 0.0 || fS2 == 0.0)
2524     {
2525         PushNoValue();
2526         return;
2527     }
2528     // ODF 1.3: two-tailed, from the right tail of F(n1-1, n2-1) beyond the ratio of the first to the second variance
2529     double fRightTail = GetFDist(fS1/fS2, fCount1-1.0, fCount2-1.0);
2530     PushDouble(2.0*(fRightTail < 0.5 ? fRightTail : 1.0-fRightTail));
2531 /*
2532     double Z = (pow(fF,1.0/3.0)*(1.0-2.0/(9.0*fF2)) - (1.0-2.0/(9.0*fF1))) /
2533                sqrt(2.0/(9.0*fF1) + pow(fF,2.0/3.0)*2.0/(9.0*fF2));
2534     PushDouble(1.0-2.0*gauss(Z));
2535 */
2536 }
2537 
ScChiTest()2538 void ScInterpreter::ScChiTest()
2539 {
2540     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScChiTest" );
2541     if ( !MustHaveParamCount( GetByte(), 2 ) )
2542         return;
2543     ScMatrixRef pMat2 = GetMatrix();
2544     ScMatrixRef pMat1 = GetMatrix();
2545     if (!pMat1 || !pMat2)
2546     {
2547         PushIllegalParameter();
2548         return;
2549     }
2550     SCSIZE nC1, nC2;
2551     SCSIZE nR1, nR2;
2552     pMat1->GetDimensions(nC1, nR1);
2553     pMat2->GetDimensions(nC2, nR2);
2554     if (nR1 != nR2 || nC1 != nC2)
2555     {
2556         PushIllegalArgument();
2557         return;
2558     }
2559     double fChi = 0.0;
2560     for (SCSIZE i = 0; i < nC1; i++)
2561     {
2562         for (SCSIZE j = 0; j < nR1; j++)
2563         {
2564             if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
2565             {
2566                 double fValX = pMat1->GetDouble(i,j);
2567                 double fValE = pMat2->GetDouble(i,j);
2568                 fChi += (fValX - fValE) * (fValX - fValE) / fValE;
2569             }
2570             else
2571             {
2572                 PushIllegalArgument();
2573                 return;
2574             }
2575         }
2576     }
2577     double fDF;
2578     if (nC1 == 1 || nR1 == 1)
2579     {
2580         fDF = (double)(nC1*nR1 - 1);
2581         if (fDF == 0.0)
2582         {
2583             PushNoValue();
2584             return;
2585         }
2586     }
2587     else
2588         fDF = (double)(nC1-1)*(double)(nR1-1);
2589     PushDouble(GetChiDist(fChi, fDF));
2590 /*
2591     double fX, fS, fT, fG;
2592     fX = 1.0;
2593     for (double fi = fDF; fi >= 2.0; fi -= 2.0)
2594         fX *= fChi/fi;
2595     fX *= exp(-fChi/2.0);
2596     if (fmod(fDF, 2.0) != 0.0)
2597         fX *= sqrt(2.0*fChi/F_PI);
2598     fS = 1.0;
2599     fT = 1.0;
2600     fG = fDF;
2601     while (fT >= 1.0E-7)
2602     {
2603         fG += 2.0;
2604         fT *= fChi/fG;
2605         fS += fT;
2606     }
2607     PushDouble(1.0 - fX*fS);
2608 */
2609 }
2610 
ScKurt()2611 void ScInterpreter::ScKurt()
2612 {
2613     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScKurt" );
2614     double fSum,fCount,vSum;
2615     std::vector<double> values;
2616     if ( !CalculateSkew(fSum,fCount,vSum,values) )
2617         return;
2618 
2619     if (fCount == 0.0)
2620     {
2621         PushError( errDivisionByZero);
2622         return;
2623     }
2624 
2625     double fMean = fSum / fCount;
2626 
2627     for (size_t i = 0; i < values.size(); i++)
2628         vSum += (values[i] - fMean) * (values[i] - fMean);
2629 
2630     double fStdDev = sqrt(vSum / (fCount - 1.0));
2631     double dx = 0.0;
2632     double xpower4 = 0.0;
2633 
2634     if (fStdDev == 0.0)
2635     {
2636         PushError( errDivisionByZero);
2637         return;
2638     }
2639 
2640     for (size_t i = 0; i < values.size(); i++)
2641     {
2642         dx = (values[i] - fMean) / fStdDev;
2643         xpower4 = xpower4 + (dx * dx * dx * dx);
2644     }
2645 
2646     double k_d = (fCount - 2.0) * (fCount - 3.0);
2647     double k_l = fCount * (fCount + 1.0) / ((fCount - 1.0) * k_d);
2648     double k_t = 3.0 * (fCount - 1.0) * (fCount - 1.0) / k_d;
2649 
2650     PushDouble(xpower4 * k_l - k_t);
2651 }
2652 
ScHarMean()2653 void ScInterpreter::ScHarMean()
2654 {
2655     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScHarMean" );
2656     short nParamCount = GetByte();
2657     double nVal = 0.0;
2658     double nValCount = 0.0;
2659     ScAddress aAdr;
2660     ScRange aRange;
2661     size_t nRefInList = 0;
2662     while ((nGlobalError == 0) && (nParamCount-- > 0))
2663     {
2664         switch (GetStackType())
2665         {
2666             case formula::svDouble    :
2667             {
2668                 double x = GetDouble();
2669                 if (x > 0.0)
2670                 {
2671                     nVal += 1.0/x;
2672                     nValCount++;
2673                 }
2674                 else
2675                     SetError( errIllegalArgument);
2676                 break;
2677             }
2678             case svSingleRef :
2679             {
2680                 PopSingleRef( aAdr );
2681                 ScBaseCell* pCell = GetCell( aAdr );
2682                 if (HasCellValueData(pCell))
2683                 {
2684                     double x = GetCellValue( aAdr, pCell );
2685                     if (x > 0.0)
2686                     {
2687                         nVal += 1.0/x;
2688                         nValCount++;
2689                     }
2690                     else
2691                         SetError( errIllegalArgument);
2692                 }
2693                 break;
2694             }
2695             case formula::svDoubleRef :
2696             case svRefList :
2697             {
2698                 sal_uInt16 nErr = 0;
2699                 PopDoubleRef( aRange, nParamCount, nRefInList);
2700                 double nCellVal;
2701                 ScValueIterator aValIter(pDok, aRange, glSubTotal);
2702                 if (aValIter.GetFirst(nCellVal, nErr))
2703                 {
2704                     if (nCellVal > 0.0)
2705                     {
2706                         nVal += 1.0/nCellVal;
2707                         nValCount++;
2708                     }
2709                     else
2710                         SetError( errIllegalArgument);
2711                     SetError(nErr);
2712                     while ((nErr == 0) && aValIter.GetNext(nCellVal, nErr))
2713                     {
2714                         if (nCellVal > 0.0)
2715                         {
2716                             nVal += 1.0/nCellVal;
2717                             nValCount++;
2718                         }
2719                         else
2720                             SetError( errIllegalArgument);
2721                     }
2722                     SetError(nErr);
2723                 }
2724             }
2725             break;
2726             case svMatrix :
2727             {
2728                 ScMatrixRef pMat = PopMatrix();
2729                 if (pMat)
2730                 {
2731                     SCSIZE nCount = pMat->GetElementCount();
2732                     if (pMat->IsNumeric())
2733                     {
2734                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
2735                         {
2736                             double x = pMat->GetDouble(nElem);
2737                             if (x > 0.0)
2738                             {
2739                                 nVal += 1.0/x;
2740                                 nValCount++;
2741                             }
2742                             else
2743                                 SetError( errIllegalArgument);
2744                         }
2745                     }
2746                     else
2747                     {
2748                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
2749                             if (!pMat->IsString(nElem))
2750                             {
2751                                 double x = pMat->GetDouble(nElem);
2752                                 if (x > 0.0)
2753                                 {
2754                                     nVal += 1.0/x;
2755                                     nValCount++;
2756                                 }
2757                                 else
2758                                     SetError( errIllegalArgument);
2759                             }
2760                     }
2761                 }
2762             }
2763             break;
2764             default : SetError(errIllegalParameter); break;
2765         }
2766     }
2767     if (nGlobalError == 0)
2768         PushDouble((double)nValCount/nVal);
2769     else
2770         PushError( nGlobalError);
2771 }
2772 
ScGeoMean()2773 void ScInterpreter::ScGeoMean()
2774 {
2775     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScGeoMean" );
2776     short nParamCount = GetByte();
2777     double nVal = 0.0;
2778     double nValCount = 0.0;
2779     ScAddress aAdr;
2780     ScRange aRange;
2781 
2782     size_t nRefInList = 0;
2783     while ((nGlobalError == 0) && (nParamCount-- > 0))
2784     {
2785         switch (GetStackType())
2786         {
2787             case formula::svDouble    :
2788             {
2789                 double x = GetDouble();
2790                 if (x > 0.0)
2791                 {
2792                     nVal += log(x);
2793                     nValCount++;
2794                 }
2795                 else
2796                     SetError( errIllegalArgument);
2797                 break;
2798             }
2799             case svSingleRef :
2800             {
2801                 PopSingleRef( aAdr );
2802                 ScBaseCell* pCell = GetCell( aAdr );
2803                 if (HasCellValueData(pCell))
2804                 {
2805                     double x = GetCellValue( aAdr, pCell );
2806                     if (x > 0.0)
2807                     {
2808                         nVal += log(x);
2809                         nValCount++;
2810                     }
2811                     else
2812                         SetError( errIllegalArgument);
2813                 }
2814                 break;
2815             }
2816             case formula::svDoubleRef :
2817             case svRefList :
2818             {
2819                 sal_uInt16 nErr = 0;
2820                 PopDoubleRef( aRange, nParamCount, nRefInList);
2821                 double nCellVal;
2822                 ScValueIterator aValIter(pDok, aRange, glSubTotal);
2823                 if (aValIter.GetFirst(nCellVal, nErr))
2824                 {
2825                     if (nCellVal > 0.0)
2826                     {
2827                         nVal += log(nCellVal);
2828                         nValCount++;
2829                     }
2830                     else
2831                         SetError( errIllegalArgument);
2832                     SetError(nErr);
2833                     while ((nErr == 0) && aValIter.GetNext(nCellVal, nErr))
2834                     {
2835                         if (nCellVal > 0.0)
2836                         {
2837                             nVal += log(nCellVal);
2838                             nValCount++;
2839                         }
2840                         else
2841                             SetError( errIllegalArgument);
2842                     }
2843                     SetError(nErr);
2844                 }
2845             }
2846             break;
2847             case svMatrix :
2848             {
2849                 ScMatrixRef pMat = PopMatrix();
2850                 if (pMat)
2851                 {
2852                     SCSIZE nCount = pMat->GetElementCount();
2853                     if (pMat->IsNumeric())
2854                     {
2855                         for (SCSIZE ui = 0; ui < nCount; ui++)
2856                         {
2857                             double x = pMat->GetDouble(ui);
2858                             if (x > 0.0)
2859                             {
2860                                 nVal += log(x);
2861                                 nValCount++;
2862                             }
2863                             else
2864                                 SetError( errIllegalArgument);
2865                         }
2866                     }
2867                     else
2868                     {
2869                         for (SCSIZE ui = 0; ui < nCount; ui++)
2870                             if (!pMat->IsString(ui))
2871                             {
2872                                 double x = pMat->GetDouble(ui);
2873                                 if (x > 0.0)
2874                                 {
2875                                     nVal += log(x);
2876                                     nValCount++;
2877                                 }
2878                                 else
2879                                     SetError( errIllegalArgument);
2880                             }
2881                     }
2882                 }
2883             }
2884             break;
2885             default : SetError(errIllegalParameter); break;
2886         }
2887     }
2888     if (nGlobalError == 0)
2889         PushDouble(exp(nVal / nValCount));
2890     else
2891         PushError( nGlobalError);
2892 }
2893 
ScStandard()2894 void ScInterpreter::ScStandard()
2895 {
2896     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScStandard" );
2897     if ( MustHaveParamCount( GetByte(), 3 ) )
2898     {
2899         double sigma = GetDouble();
2900         double mue   = GetDouble();
2901         double x     = GetDouble();
2902         if (sigma < 0.0)
2903             PushError( errIllegalArgument);
2904         else if (sigma == 0.0)
2905             PushError( errDivisionByZero);
2906         else
2907             PushDouble((x-mue)/sigma);
2908     }
2909 }
CalculateSkew(double & fSum,double & fCount,double & vSum,std::vector<double> & values)2910 bool ScInterpreter::CalculateSkew(double& fSum,double& fCount,double& vSum,std::vector<double>& values)
2911 {
2912     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CalculateSkew" );
2913     short nParamCount = GetByte();
2914     if ( !MustHaveParamCountMin( nParamCount, 1 )  )
2915         return false;
2916 
2917     fSum   = 0.0;
2918     fCount = 0.0;
2919     vSum   = 0.0;
2920     double fVal = 0.0;
2921     ScAddress aAdr;
2922     ScRange aRange;
2923     size_t nRefInList = 0;
2924     while (nParamCount-- > 0)
2925     {
2926         switch (GetStackType())
2927         {
2928             case formula::svDouble :
2929             {
2930                 fVal = GetDouble();
2931                 fSum += fVal;
2932                 values.push_back(fVal);
2933                 fCount++;
2934             }
2935                 break;
2936             case svSingleRef :
2937             {
2938                 PopSingleRef( aAdr );
2939                 ScBaseCell* pCell = GetCell( aAdr );
2940                 if (HasCellValueData(pCell))
2941                 {
2942                     fVal = GetCellValue( aAdr, pCell );
2943                     fSum += fVal;
2944                     values.push_back(fVal);
2945                     fCount++;
2946                 }
2947             }
2948             break;
2949             case formula::svDoubleRef :
2950             case svRefList :
2951             {
2952                 PopDoubleRef( aRange, nParamCount, nRefInList);
2953                 sal_uInt16 nErr = 0;
2954                 ScValueIterator aValIter(pDok, aRange);
2955                 if (aValIter.GetFirst(fVal, nErr))
2956                 {
2957                     fSum += fVal;
2958                     values.push_back(fVal);
2959                     fCount++;
2960                     SetError(nErr);
2961                     while ((nErr == 0) && aValIter.GetNext(fVal, nErr))
2962                     {
2963                         fSum += fVal;
2964                         values.push_back(fVal);
2965                         fCount++;
2966                     }
2967                     SetError(nErr);
2968                 }
2969             }
2970             break;
2971             case svMatrix :
2972             {
2973                 ScMatrixRef pMat = PopMatrix();
2974                 if (pMat)
2975                 {
2976                     SCSIZE nCount = pMat->GetElementCount();
2977                     if (pMat->IsNumeric())
2978                     {
2979                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
2980                         {
2981                             fVal = pMat->GetDouble(nElem);
2982                             fSum += fVal;
2983                             values.push_back(fVal);
2984                             fCount++;
2985                         }
2986                     }
2987                     else
2988                     {
2989                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
2990                             if (!pMat->IsString(nElem))
2991                             {
2992                                 fVal = pMat->GetDouble(nElem);
2993                                 fSum += fVal;
2994                                 values.push_back(fVal);
2995                                 fCount++;
2996                             }
2997                     }
2998                 }
2999             }
3000             break;
3001             default :
3002                 SetError(errIllegalParameter);
3003             break;
3004         }
3005     }
3006 
3007     if (nGlobalError)
3008     {
3009         PushError( nGlobalError);
3010         return false;
3011     } // if (nGlobalError)
3012     return true;
3013 }
3014 
ScSkew()3015 void ScInterpreter::ScSkew()
3016 {
3017     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSkew" );
3018     double fSum,fCount,vSum;
3019     std::vector<double> values;
3020     if ( !CalculateSkew(fSum,fCount,vSum,values) )
3021         return;
3022 
3023     double fMean = fSum / fCount;
3024 
3025     for (size_t i = 0; i < values.size(); i++)
3026         vSum += (values[i] - fMean) * (values[i] - fMean);
3027 
3028     double fStdDev = sqrt(vSum / (fCount - 1.0));
3029     double dx = 0.0;
3030     double xcube = 0.0;
3031 
3032     if (fStdDev == 0)
3033     {
3034         PushIllegalArgument();
3035         return;
3036     }
3037 
3038     for (size_t i = 0; i < values.size(); i++)
3039     {
3040         dx = (values[i] - fMean) / fStdDev;
3041         xcube = xcube + (dx * dx * dx);
3042     }
3043 
3044     PushDouble(((xcube * fCount) / (fCount - 1.0)) / (fCount - 2.0));
3045 }
3046 
ScSkewP()3047 void ScInterpreter::ScSkewP()
3048 {
3049     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSkewP" );
3050     double fSum,fCount,vSum;
3051     std::vector<double> values;
3052     if ( !CalculateSkew(fSum,fCount,vSum,values) )
3053         return;
3054 
3055     // Population skewness requires at least one numeric observation.
3056     if (fCount < 1.0)
3057     {
3058         PushIllegalArgument();
3059         return;
3060     }
3061 
3062     double fMean = fSum / fCount;
3063 
3064     for (size_t i = 0; i < values.size(); i++)
3065         vSum += (values[i] - fMean) * (values[i] - fMean);
3066 
3067     // Population standard deviation: sigma = sqrt( sum((xi-mean)^2) / N )
3068     double fSigma = sqrt(vSum / fCount);
3069 
3070     // Zero variance (including a single observation) is undefined.
3071     if (fSigma == 0.0)
3072     {
3073         PushIllegalArgument();
3074         return;
3075     }
3076 
3077     double xcube = 0.0;
3078     for (size_t i = 0; i < values.size(); i++)
3079     {
3080         double dx = (values[i] - fMean) / fSigma;
3081         xcube = xcube + (dx * dx * dx);
3082     }
3083 
3084     // Population skewness: (1/N) * sum( ((xi-mean)/sigma)^3 )
3085     PushDouble(xcube / fCount);
3086 }
3087 
GetMedian(vector<double> & rArray)3088 double ScInterpreter::GetMedian( vector<double> & rArray )
3089 {
3090     size_t nSize = rArray.size();
3091     if (rArray.empty() || nSize == 0 || nGlobalError)
3092     {
3093         SetError( errNoValue);
3094         return 0.0;
3095     }
3096 
3097     // Upper median.
3098     size_t nMid = nSize / 2;
3099     vector<double>::iterator iMid = rArray.begin() + nMid;
3100     ::std::nth_element( rArray.begin(), iMid, rArray.end());
3101     if (nSize & 1)
3102         return *iMid;   // Lower and upper median are equal.
3103     else
3104     {
3105         double fUp = *iMid;
3106         // Lower median.
3107         iMid = rArray.begin() + nMid - 1;
3108         ::std::nth_element( rArray.begin(), iMid, rArray.end());
3109         return (fUp + *iMid) / 2;
3110     }
3111 }
3112 
ScMedian()3113 void ScInterpreter::ScMedian()
3114 {
3115     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScMedian" );
3116     sal_uInt8 nParamCount = GetByte();
3117     if ( !MustHaveParamCountMin( nParamCount, 1 )  )
3118         return;
3119     vector<double> aArray;
3120     GetNumberSequenceArray( nParamCount, aArray);
3121     PushDouble( GetMedian( aArray));
3122 }
3123 
GetPercentile(vector<double> & rArray,double fPercentile)3124 double ScInterpreter::GetPercentile( vector<double> & rArray, double fPercentile )
3125 {
3126     size_t nSize = rArray.size();
3127     if (rArray.empty() || nSize == 0 || nGlobalError)
3128     {
3129         SetError( errNoValue);
3130         return 0.0;
3131     }
3132 
3133     if (nSize == 1)
3134         return rArray[0];
3135     else
3136     {
3137         size_t nIndex = (size_t)::rtl::math::approxFloor( fPercentile * (nSize-1));
3138         double fDiff = fPercentile * (nSize-1) - ::rtl::math::approxFloor( fPercentile * (nSize-1));
3139         DBG_ASSERT(nIndex < nSize, "GetPercentile: wrong index(1)");
3140         vector<double>::iterator iter = rArray.begin() + nIndex;
3141         ::std::nth_element( rArray.begin(), iter, rArray.end());
3142         if (fDiff == 0.0)
3143             return *iter;
3144         else
3145         {
3146             DBG_ASSERT(nIndex < nSize-1, "GetPercentile: wrong index(2)");
3147             double fVal = *iter;
3148             iter = rArray.begin() + nIndex+1;
3149             ::std::nth_element( rArray.begin(), iter, rArray.end());
3150             return fVal + fDiff * (*iter - fVal);
3151         }
3152     }
3153 }
3154 
ScPercentile()3155 void ScInterpreter::ScPercentile()
3156 {
3157     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPercentile" );
3158     if ( !MustHaveParamCount( GetByte(), 2 ) )
3159         return;
3160     double alpha = GetDouble();
3161     if (alpha < 0.0 || alpha > 1.0)
3162     {
3163         PushIllegalArgument();
3164         return;
3165     }
3166     vector<double> aArray;
3167     GetNumberSequenceArray( 1, aArray);
3168     PushDouble( GetPercentile( aArray, alpha));
3169 }
3170 
ScQuartile()3171 void ScInterpreter::ScQuartile()
3172 {
3173     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScQuartile" );
3174     if ( !MustHaveParamCount( GetByte(), 2 ) )
3175         return;
3176     double fFlag = ::rtl::math::approxFloor(GetDouble());
3177     if (fFlag < 0.0 || fFlag > 4.0)
3178     {
3179         PushIllegalArgument();
3180         return;
3181     }
3182     vector<double> aArray;
3183     GetNumberSequenceArray( 1, aArray);
3184     PushDouble( fFlag == 2.0 ? GetMedian( aArray) : GetPercentile( aArray, 0.25 * fFlag));
3185 }
3186 
ScModalValue()3187 void ScInterpreter::ScModalValue()
3188 {
3189     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScModalValue" );
3190     sal_uInt8 nParamCount = GetByte();
3191     if ( !MustHaveParamCountMin( nParamCount, 1 ) )
3192         return;
3193     vector<double> aSortArray;
3194     GetSortArray(nParamCount, aSortArray);
3195     SCSIZE nSize = aSortArray.size();
3196     if (aSortArray.empty() || nSize == 0 || nGlobalError)
3197         PushNoValue();
3198     else
3199     {
3200         SCSIZE nMaxIndex = 0, nMax = 1, nCount = 1;
3201         double nOldVal = aSortArray[0];
3202         SCSIZE i;
3203 
3204         for ( i = 1; i < nSize; i++)
3205         {
3206             if (aSortArray[i] == nOldVal)
3207                 nCount++;
3208             else
3209             {
3210                 nOldVal = aSortArray[i];
3211                 if (nCount > nMax)
3212                 {
3213                     nMax = nCount;
3214                     nMaxIndex = i-1;
3215                 }
3216                 nCount = 1;
3217             }
3218         }
3219         if (nCount > nMax)
3220         {
3221             nMax = nCount;
3222             nMaxIndex = i-1;
3223         }
3224         if (nMax == 1 && nCount == 1)
3225             PushNoValue();
3226         else if (nMax == 1)
3227             PushDouble(nOldVal);
3228         else
3229             PushDouble(aSortArray[nMaxIndex]);
3230     }
3231 }
3232 
CalculateSmallLarge(sal_Bool bSmall)3233 void ScInterpreter::CalculateSmallLarge(sal_Bool bSmall)
3234 {
3235     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CalculateSmallLarge" );
3236     if ( !MustHaveParamCount( GetByte(), 2 )  )
3237         return;
3238     double f = ::rtl::math::approxFloor(GetDouble());
3239     if (f < 1.0)
3240     {
3241         PushIllegalArgument();
3242         return;
3243     }
3244     SCSIZE k = static_cast<SCSIZE>(f);
3245     vector<double> aSortArray;
3246     /* TODO: using nth_element() is best for one single value, but LARGE/SMALL
3247      * actually are defined to return an array of values if an array of
3248      * positions was passed, in which case, depending on the number of values,
3249      * we may or will need a real sorted array again, see #i32345. */
3250     //GetSortArray(1, aSortArray);
3251     GetNumberSequenceArray(1, aSortArray);
3252     SCSIZE nSize = aSortArray.size();
3253     if (aSortArray.empty() || nSize == 0 || nGlobalError || nSize < k)
3254         PushNoValue();
3255     else
3256     {
3257         // TODO: the sorted case for array: PushDouble( aSortArray[ bSmall ? k-1 : nSize-k ] );
3258         vector<double>::iterator iPos = aSortArray.begin() + (bSmall ? k-1 : nSize-k);
3259         ::std::nth_element( aSortArray.begin(), iPos, aSortArray.end());
3260         PushDouble( *iPos);
3261     }
3262 }
3263 
ScLarge()3264 void ScInterpreter::ScLarge()
3265 {
3266     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScLarge" );
3267     CalculateSmallLarge(sal_False);
3268 }
3269 
ScSmall()3270 void ScInterpreter::ScSmall()
3271 {
3272     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSmall" );
3273     CalculateSmallLarge(sal_True);
3274 }
3275 
ScPercentrank()3276 void ScInterpreter::ScPercentrank()
3277 {
3278     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPercentrank" );
3279     sal_uInt8 nParamCount = GetByte();
3280     if ( !MustHaveParamCount( nParamCount, 2 ) )
3281         return;
3282 #if 0
3283 /*                          wird nicht unterstuetzt
3284     double fPrec;
3285     if (nParamCount == 3)
3286     {
3287         fPrec = ::rtl::math::approxFloor(GetDouble());
3288         if (fPrec < 1.0)
3289         {
3290             PushIllegalArgument();
3291             return;
3292         }
3293     }
3294     else
3295         fPrec = 3.0;
3296 */
3297 #endif
3298     double fNum = GetDouble();
3299     vector<double> aSortArray;
3300     GetSortArray(1, aSortArray);
3301     SCSIZE nSize = aSortArray.size();
3302     if (aSortArray.empty() || nSize == 0 || nGlobalError)
3303         PushNoValue();
3304     else
3305     {
3306         if (fNum < aSortArray[0] || fNum > aSortArray[nSize-1])
3307             PushNoValue();
3308         else if ( nSize == 1 )
3309             PushDouble(1.0);            // fNum == pSortArray[0], see test above
3310         else
3311         {
3312             double fRes;
3313             SCSIZE nOldCount = 0;
3314             double fOldVal = aSortArray[0];
3315             SCSIZE i;
3316             for (i = 1; i < nSize && aSortArray[i] < fNum; i++)
3317             {
3318                 if (aSortArray[i] != fOldVal)
3319                 {
3320                     nOldCount = i;
3321                     fOldVal = aSortArray[i];
3322                 }
3323             }
3324             if (aSortArray[i] != fOldVal)
3325                 nOldCount = i;
3326             if (fNum == aSortArray[i])
3327                 fRes = (double)nOldCount/(double)(nSize-1);
3328             else
3329             {
3330                 //  #75312# nOldCount is the count of smaller entries
3331                 //  fNum is between pSortArray[nOldCount-1] and pSortArray[nOldCount]
3332                 //  use linear interpolation to find a position between the entries
3333 
3334                 if ( nOldCount == 0 )
3335                 {
3336                     DBG_ERROR("should not happen");
3337                     fRes = 0.0;
3338                 }
3339                 else
3340                 {
3341                     double fFract = ( fNum - aSortArray[nOldCount-1] ) /
3342                         ( aSortArray[nOldCount] - aSortArray[nOldCount-1] );
3343                     fRes = ( (double)(nOldCount-1)+fFract )/(double)(nSize-1);
3344                 }
3345             }
3346             PushDouble(fRes);
3347         }
3348     }
3349 }
3350 
ScTrimMean()3351 void ScInterpreter::ScTrimMean()
3352 {
3353     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScTrimMean" );
3354     if ( !MustHaveParamCount( GetByte(), 2 ) )
3355         return;
3356     double alpha = GetDouble();
3357     if (alpha < 0.0 || alpha >= 1.0)
3358     {
3359         PushIllegalArgument();
3360         return;
3361     }
3362     vector<double> aSortArray;
3363     GetSortArray(1, aSortArray);
3364     SCSIZE nSize = aSortArray.size();
3365     if (aSortArray.empty() || nSize == 0 || nGlobalError)
3366         PushNoValue();
3367     else
3368     {
3369         sal_uLong nIndex = (sal_uLong) ::rtl::math::approxFloor(alpha*(double)nSize);
3370         if (nIndex % 2 != 0)
3371             nIndex--;
3372         nIndex /= 2;
3373         DBG_ASSERT(nIndex < nSize, "ScTrimMean: falscher Index");
3374         double fSum = 0.0;
3375         for (SCSIZE i = nIndex; i < nSize-nIndex; i++)
3376             fSum += aSortArray[i];
3377         PushDouble(fSum/(double)(nSize-2*nIndex));
3378     }
3379 }
3380 
GetNumberSequenceArray(sal_uInt8 nParamCount,vector<double> & rArray)3381 void ScInterpreter::GetNumberSequenceArray( sal_uInt8 nParamCount, vector<double>& rArray )
3382 {
3383     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::GetSortArray" );
3384     ScAddress aAdr;
3385     ScRange aRange;
3386     short nParam = nParamCount;
3387     size_t nRefInList = 0;
3388     while (nParam-- > 0)
3389     {
3390         switch (GetStackType())
3391         {
3392             case formula::svDouble :
3393                 rArray.push_back( PopDouble());
3394             break;
3395             case svSingleRef :
3396             {
3397                 PopSingleRef( aAdr );
3398                 ScBaseCell* pCell = GetCell( aAdr );
3399                 if (HasCellValueData(pCell))
3400                     rArray.push_back( GetCellValue( aAdr, pCell));
3401             }
3402             break;
3403             case formula::svDoubleRef :
3404             case svRefList :
3405             {
3406                 PopDoubleRef( aRange, nParam, nRefInList);
3407                 if (nGlobalError)
3408                     break;
3409 
3410                 aRange.Justify();
3411                 SCSIZE nCellCount = aRange.aEnd.Col() - aRange.aStart.Col() + 1;
3412                 nCellCount *= aRange.aEnd.Row() - aRange.aStart.Row() + 1;
3413                 rArray.reserve( rArray.size() + nCellCount);
3414 
3415                 sal_uInt16 nErr = 0;
3416                 double fCellVal;
3417                 ScValueIterator aValIter(pDok, aRange);
3418                 if (aValIter.GetFirst( fCellVal, nErr))
3419                 {
3420                     rArray.push_back( fCellVal);
3421                     SetError(nErr);
3422                     while ((nErr == 0) && aValIter.GetNext( fCellVal, nErr))
3423                         rArray.push_back( fCellVal);
3424                     SetError(nErr);
3425                 }
3426             }
3427             break;
3428             case svMatrix :
3429             {
3430                 ScMatrixRef pMat = PopMatrix();
3431                 if (!pMat)
3432                     break;
3433 
3434                 SCSIZE nCount = pMat->GetElementCount();
3435                 rArray.reserve( rArray.size() + nCount);
3436                 if (pMat->IsNumeric())
3437                 {
3438                     for (SCSIZE i = 0; i < nCount; ++i)
3439                         rArray.push_back( pMat->GetDouble(i));
3440                 }
3441                 else
3442                 {
3443                     for (SCSIZE i = 0; i < nCount; ++i)
3444                         if (!pMat->IsString(i))
3445                             rArray.push_back( pMat->GetDouble(i));
3446                 }
3447             }
3448             break;
3449             default :
3450                 PopError();
3451                 SetError( errIllegalParameter);
3452             break;
3453         }
3454         if (nGlobalError)
3455             break;  // while
3456     }
3457     // nParam > 0 in case of error, clean stack environment and obtain earlier
3458     // error if there was one.
3459     while (nParam-- > 0)
3460         PopError();
3461 }
3462 
GetSortArray(sal_uInt8 nParamCount,vector<double> & rSortArray,vector<long> * pIndexOrder)3463 void ScInterpreter::GetSortArray( sal_uInt8 nParamCount, vector<double>& rSortArray, vector<long>* pIndexOrder )
3464 {
3465     GetNumberSequenceArray( nParamCount, rSortArray);
3466 
3467     if (rSortArray.size() > MAX_ANZ_DOUBLE_FOR_SORT)
3468         SetError( errStackOverflow);
3469     else if (rSortArray.empty())
3470         SetError( errNoValue);
3471 
3472     if (nGlobalError == 0)
3473         QuickSort( rSortArray, pIndexOrder);
3474 }
3475 
lcl_QuickSort(long nLo,long nHi,vector<double> & rSortArray,vector<long> * pIndexOrder)3476 static void lcl_QuickSort( long nLo, long nHi, vector<double>& rSortArray, vector<long>* pIndexOrder )
3477 {
3478     // If pIndexOrder is not NULL, we assume rSortArray.size() == pIndexOrder->size().
3479 
3480     using ::std::swap;
3481 
3482     if (nHi - nLo == 1)
3483     {
3484         if (rSortArray[nLo] > rSortArray[nHi])
3485         {
3486             swap(rSortArray[nLo],  rSortArray[nHi]);
3487             if (pIndexOrder)
3488                 swap(pIndexOrder->at(nLo), pIndexOrder->at(nHi));
3489         }
3490         return;
3491     }
3492 
3493     long ni = nLo;
3494     long nj = nHi;
3495     do
3496     {
3497         double fLo = rSortArray[nLo];
3498         while (ni <= nHi && rSortArray[ni] < fLo) ni++;
3499         while (nj >= nLo && fLo < rSortArray[nj]) nj--;
3500         if (ni <= nj)
3501         {
3502             if (ni != nj)
3503             {
3504                 swap(rSortArray[ni],  rSortArray[nj]);
3505                 if (pIndexOrder)
3506                     swap(pIndexOrder->at(ni), pIndexOrder->at(nj));
3507             }
3508 
3509             ++ni;
3510             --nj;
3511         }
3512     }
3513     while (ni < nj);
3514 
3515     if ((nj - nLo) < (nHi - ni))
3516     {
3517         if (nLo < nj) lcl_QuickSort(nLo, nj, rSortArray, pIndexOrder);
3518         if (ni < nHi) lcl_QuickSort(ni, nHi, rSortArray, pIndexOrder);
3519     }
3520     else
3521     {
3522         if (ni < nHi) lcl_QuickSort(ni, nHi, rSortArray, pIndexOrder);
3523         if (nLo < nj) lcl_QuickSort(nLo, nj, rSortArray, pIndexOrder);
3524     }
3525 }
3526 
QuickSort(vector<double> & rSortArray,vector<long> * pIndexOrder)3527 void ScInterpreter::QuickSort( vector<double>& rSortArray, vector<long>* pIndexOrder )
3528 {
3529     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::QuickSort" );
3530     long n = static_cast<long>(rSortArray.size());
3531 
3532     if (pIndexOrder)
3533     {
3534         pIndexOrder->clear();
3535         pIndexOrder->reserve(n);
3536         for (long i = 0; i < n; ++i)
3537             pIndexOrder->push_back(i);
3538     }
3539 
3540     if (n < 2)
3541         return;
3542 
3543     size_t nValCount = rSortArray.size();
3544     for (size_t i = 0; (i + 4) <= nValCount-1; i += 4)
3545     {
3546         size_t nInd = rand() % (int) (nValCount-1);
3547         ::std::swap( rSortArray[i], rSortArray[nInd]);
3548         if (pIndexOrder)
3549             ::std::swap( pIndexOrder->at(i), pIndexOrder->at(nInd));
3550     }
3551 
3552     lcl_QuickSort(0, n-1, rSortArray, pIndexOrder);
3553 }
3554 
ScRank()3555 void ScInterpreter::ScRank()
3556 {
3557     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRank" );
3558     sal_uInt8 nParamCount = GetByte();
3559     if ( !MustHaveParamCount( nParamCount, 2, 3 ) )
3560         return;
3561     sal_Bool bDescending;
3562     if (nParamCount == 3)
3563         bDescending = GetBool();
3564     else
3565         bDescending = sal_False;
3566     double fCount = 1.0;
3567     sal_Bool bValid = sal_False;
3568     switch (GetStackType())
3569     {
3570         case formula::svDouble    :
3571         {
3572             double x = GetDouble();
3573             double fVal = GetDouble();
3574             if (x == fVal)
3575                 bValid = sal_True;
3576             break;
3577         }
3578         case svSingleRef :
3579         {
3580             ScAddress aAdr;
3581             PopSingleRef( aAdr );
3582             double fVal = GetDouble();
3583             ScBaseCell* pCell = GetCell( aAdr );
3584             if (HasCellValueData(pCell))
3585             {
3586                 double x = GetCellValue( aAdr, pCell );
3587                 if (x == fVal)
3588                     bValid = sal_True;
3589             }
3590             break;
3591         }
3592         case formula::svDoubleRef :
3593         case svRefList :
3594         {
3595             ScRange aRange;
3596             short nParam = 1;
3597             size_t nRefInList = 0;
3598             while (nParam-- > 0)
3599             {
3600                 sal_uInt16 nErr = 0;
3601                 // Preserve stack until all RefList elements are done!
3602                 sal_uInt16 nSaveSP = sp;
3603                 PopDoubleRef( aRange, nParam, nRefInList);
3604                 if (nParam)
3605                     --sp;   // simulate pop
3606                 double fVal = GetDouble();
3607                 if (nParam)
3608                     sp = nSaveSP;
3609                 double nCellVal;
3610                 ScValueIterator aValIter(pDok, aRange, glSubTotal);
3611                 if (aValIter.GetFirst(nCellVal, nErr))
3612                 {
3613                     if (nCellVal == fVal)
3614                         bValid = sal_True;
3615                     else if ((!bDescending && nCellVal > fVal) ||
3616                             (bDescending && nCellVal < fVal) )
3617                         fCount++;
3618                     SetError(nErr);
3619                     while ((nErr == 0) && aValIter.GetNext(nCellVal, nErr))
3620                     {
3621                         if (nCellVal == fVal)
3622                             bValid = sal_True;
3623                         else if ((!bDescending && nCellVal > fVal) ||
3624                                 (bDescending && nCellVal < fVal) )
3625                             fCount++;
3626                     }
3627                 }
3628                 SetError(nErr);
3629             }
3630         }
3631         break;
3632         case svMatrix :
3633         {
3634             ScMatrixRef pMat = PopMatrix();
3635             double fVal = GetDouble();
3636             if (pMat)
3637             {
3638                 SCSIZE nCount = pMat->GetElementCount();
3639                 if (pMat->IsNumeric())
3640                 {
3641                     for (SCSIZE i = 0; i < nCount; i++)
3642                     {
3643                         double x = pMat->GetDouble(i);
3644                         if (x == fVal)
3645                             bValid = sal_True;
3646                         else if ((!bDescending && x > fVal) ||
3647                                     (bDescending && x < fVal) )
3648                             fCount++;
3649                     }
3650                 }
3651                 else
3652                 {
3653                     for (SCSIZE i = 0; i < nCount; i++)
3654                         if (!pMat->IsString(i))
3655                         {
3656                             double x = pMat->GetDouble(i);
3657                             if (x == fVal)
3658                                 bValid = sal_True;
3659                             else if ((!bDescending && x > fVal) ||
3660                                         (bDescending && x < fVal) )
3661                                 fCount++;
3662                         }
3663                 }
3664             }
3665         }
3666         break;
3667         default : SetError(errIllegalParameter); break;
3668     }
3669     if (bValid)
3670         PushDouble(fCount);
3671     else
3672         PushNoValue();
3673 }
3674 
ScAveDev()3675 void ScInterpreter::ScAveDev()
3676 {
3677     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScAveDev" );
3678     sal_uInt8 nParamCount = GetByte();
3679     if ( !MustHaveParamCountMin( nParamCount, 1 ) )
3680         return;
3681     sal_uInt16 SaveSP = sp;
3682     double nMiddle = 0.0;
3683     double rVal = 0.0;
3684     double rValCount = 0.0;
3685     ScAddress aAdr;
3686     ScRange aRange;
3687     short nParam = nParamCount;
3688     size_t nRefInList = 0;
3689     while (nParam-- > 0)
3690     {
3691         switch (GetStackType())
3692         {
3693             case formula::svDouble :
3694                 rVal += GetDouble();
3695                 rValCount++;
3696                 break;
3697             case svSingleRef :
3698             {
3699                 PopSingleRef( aAdr );
3700                 ScBaseCell* pCell = GetCell( aAdr );
3701                 if (HasCellValueData(pCell))
3702                 {
3703                     rVal += GetCellValue( aAdr, pCell );
3704                     rValCount++;
3705                 }
3706             }
3707             break;
3708             case formula::svDoubleRef :
3709             case svRefList :
3710             {
3711                 sal_uInt16 nErr = 0;
3712                 double nCellVal;
3713                 PopDoubleRef( aRange, nParam, nRefInList);
3714                 ScValueIterator aValIter(pDok, aRange);
3715                 if (aValIter.GetFirst(nCellVal, nErr))
3716                 {
3717                     rVal += nCellVal;
3718                     rValCount++;
3719                     SetError(nErr);
3720                     while ((nErr == 0) && aValIter.GetNext(nCellVal, nErr))
3721                     {
3722                         rVal += nCellVal;
3723                         rValCount++;
3724                     }
3725                     SetError(nErr);
3726                 }
3727             }
3728             break;
3729             case svMatrix :
3730             {
3731                 ScMatrixRef pMat = PopMatrix();
3732                 if (pMat)
3733                 {
3734                     SCSIZE nCount = pMat->GetElementCount();
3735                     if (pMat->IsNumeric())
3736                     {
3737                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
3738                         {
3739                             rVal += pMat->GetDouble(nElem);
3740                             rValCount++;
3741                         }
3742                     }
3743                     else
3744                     {
3745                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
3746                             if (!pMat->IsString(nElem))
3747                             {
3748                                 rVal += pMat->GetDouble(nElem);
3749                                 rValCount++;
3750                             }
3751                     }
3752                 }
3753             }
3754             break;
3755             default :
3756                 SetError(errIllegalParameter);
3757             break;
3758         }
3759     }
3760     if (nGlobalError)
3761     {
3762         PushError( nGlobalError);
3763         return;
3764     }
3765     nMiddle = rVal / rValCount;
3766     sp = SaveSP;
3767     rVal = 0.0;
3768     nParam = nParamCount;
3769     nRefInList = 0;
3770     while (nParam-- > 0)
3771     {
3772         switch (GetStackType())
3773         {
3774             case formula::svDouble :
3775                 rVal += fabs(GetDouble() - nMiddle);
3776                 break;
3777             case svSingleRef :
3778             {
3779                 PopSingleRef( aAdr );
3780                 ScBaseCell* pCell = GetCell( aAdr );
3781                 if (HasCellValueData(pCell))
3782                     rVal += fabs(GetCellValue( aAdr, pCell ) - nMiddle);
3783             }
3784             break;
3785             case formula::svDoubleRef :
3786             case svRefList :
3787             {
3788                 sal_uInt16 nErr = 0;
3789                 double nCellVal;
3790                 PopDoubleRef( aRange, nParam, nRefInList);
3791                 ScValueIterator aValIter(pDok, aRange);
3792                 if (aValIter.GetFirst(nCellVal, nErr))
3793                 {
3794                     rVal += (fabs(nCellVal - nMiddle));
3795                     while (aValIter.GetNext(nCellVal, nErr))
3796                          rVal += fabs(nCellVal - nMiddle);
3797                 }
3798             }
3799             break;
3800             case svMatrix :
3801             {
3802                 ScMatrixRef pMat = PopMatrix();
3803                 if (pMat)
3804                 {
3805                     SCSIZE nCount = pMat->GetElementCount();
3806                     if (pMat->IsNumeric())
3807                     {
3808                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
3809                         {
3810                             rVal += fabs(pMat->GetDouble(nElem) - nMiddle);
3811                         }
3812                     }
3813                     else
3814                     {
3815                         for (SCSIZE nElem = 0; nElem < nCount; nElem++)
3816                         {
3817                             if (!pMat->IsString(nElem))
3818                                 rVal += fabs(pMat->GetDouble(nElem) - nMiddle);
3819                         }
3820                     }
3821                 }
3822             }
3823             break;
3824             default : SetError(errIllegalParameter); break;
3825         }
3826     }
3827     PushDouble(rVal / rValCount);
3828 }
3829 
ScDevSq()3830 void ScInterpreter::ScDevSq()
3831 {
3832     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScDevSq" );
3833     double nVal;
3834     double nValCount;
3835     GetStVarParams(nVal, nValCount);
3836     PushDouble(nVal);
3837 }
3838 
ScProbability()3839 void ScInterpreter::ScProbability()
3840 {
3841     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScProbability" );
3842     sal_uInt8 nParamCount = GetByte();
3843     if ( !MustHaveParamCount( nParamCount, 3, 4 ) )
3844         return;
3845     double fUp, fLo;
3846     fUp = GetDouble();
3847     if (nParamCount == 4)
3848         fLo = GetDouble();
3849     else
3850         fLo = fUp;
3851     if (fLo > fUp)
3852     {
3853         double fTemp = fLo;
3854         fLo = fUp;
3855         fUp = fTemp;
3856     }
3857     ScMatrixRef pMatP = GetMatrix();
3858     ScMatrixRef pMatW = GetMatrix();
3859     if (!pMatP || !pMatW)
3860         PushIllegalParameter();
3861     else
3862     {
3863         SCSIZE nC1, nC2;
3864         SCSIZE nR1, nR2;
3865         pMatP->GetDimensions(nC1, nR1);
3866         pMatW->GetDimensions(nC2, nR2);
3867         if (nC1 != nC2 || nR1 != nR2 || nC1 == 0 || nR1 == 0 ||
3868             nC2 == 0 || nR2 == 0)
3869             PushNA();
3870         else
3871         {
3872             double fSum = 0.0;
3873             double fRes = 0.0;
3874             sal_Bool bStop = sal_False;
3875             double fP, fW;
3876             SCSIZE nCount1 = nC1 * nR1;
3877             for ( SCSIZE i = 0; i < nCount1 && !bStop; i++ )
3878             {
3879                 if (pMatP->IsValue(i) && pMatW->IsValue(i))
3880                 {
3881                     fP = pMatP->GetDouble(i);
3882                     fW = pMatW->GetDouble(i);
3883                     if (fP < 0.0 || fP > 1.0)
3884                         bStop = sal_True;
3885                     else
3886                     {
3887                         fSum += fP;
3888                         if (fW >= fLo && fW <= fUp)
3889                             fRes += fP;
3890                     }
3891                 }
3892                 else
3893                     SetError( errIllegalArgument);
3894             }
3895             if (bStop || fabs(fSum -1.0) > 1.0E-7)
3896                 PushNoValue();
3897             else
3898                 PushDouble(fRes);
3899         }
3900     }
3901 }
3902 
ScCorrel()3903 void ScInterpreter::ScCorrel()
3904 {
3905     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCorrel" );
3906     // This is identical to ScPearson()
3907     ScPearson();
3908 }
3909 
ScCovar()3910 void ScInterpreter::ScCovar()
3911 {
3912     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCovar" );
3913     CalculatePearsonCovar(sal_False,sal_False);
3914 }
3915 
ScPearson()3916 void ScInterpreter::ScPearson()
3917 {
3918     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScPearson" );
3919     CalculatePearsonCovar(sal_True,sal_False);
3920 }
CalculatePearsonCovar(sal_Bool _bPearson,sal_Bool _bStexy)3921 void ScInterpreter::CalculatePearsonCovar(sal_Bool _bPearson,sal_Bool _bStexy)
3922 {
3923     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CalculatePearsonCovar" );
3924     if ( !MustHaveParamCount( GetByte(), 2 ) )
3925         return;
3926     ScMatrixRef pMat1 = GetMatrix();
3927     ScMatrixRef pMat2 = GetMatrix();
3928     if (!pMat1 || !pMat2)
3929     {
3930         PushIllegalParameter();
3931         return;
3932     }
3933     SCSIZE nC1, nC2;
3934     SCSIZE nR1, nR2;
3935     pMat1->GetDimensions(nC1, nR1);
3936     pMat2->GetDimensions(nC2, nR2);
3937     if (nR1 != nR2 || nC1 != nC2)
3938     {
3939         PushIllegalArgument();
3940         return;
3941     }
3942     /* #i78250#
3943      * (sum((X-MeanX)(Y-MeanY)))/N equals (SumXY)/N-MeanX*MeanY mathematically,
3944      * but the latter produces wrong results if the absolute values are high,
3945      * for example above 10^8
3946      */
3947     double fCount           = 0.0;
3948     double fSumX            = 0.0;
3949     double fSumY            = 0.0;
3950     double fSumDeltaXDeltaY = 0.0; // sum of (ValX-MeanX)*(ValY-MeanY)
3951     double fSumSqrDeltaX    = 0.0; // sum of (ValX-MeanX)^2
3952     double fSumSqrDeltaY    = 0.0; // sum of (ValY-MeanY)^2
3953     for (SCSIZE i = 0; i < nC1; i++)
3954     {
3955         for (SCSIZE j = 0; j < nR1; j++)
3956         {
3957             if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
3958             {
3959                 double fValX = pMat1->GetDouble(i,j);
3960                 double fValY = pMat2->GetDouble(i,j);
3961                 fSumX += fValX;
3962                 fSumY += fValY;
3963                 fCount++;
3964             }
3965         }
3966     }
3967     if (fCount < (_bStexy ? 3.0 : 1.0)) // fCount==1 is handled by checking denominator later on
3968         PushNoValue();
3969     else
3970     {
3971         const double fMeanX = fSumX / fCount;
3972         const double fMeanY = fSumY / fCount;
3973         for (SCSIZE i = 0; i < nC1; i++)
3974         {
3975             for (SCSIZE j = 0; j < nR1; j++)
3976             {
3977                 if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
3978                 {
3979                     const double fValX = pMat1->GetDouble(i,j);
3980                     const double fValY = pMat2->GetDouble(i,j);
3981                     fSumDeltaXDeltaY += (fValX - fMeanX) * (fValY - fMeanY);
3982                     if ( _bPearson )
3983                     {
3984                         fSumSqrDeltaX    += (fValX - fMeanX) * (fValX - fMeanX);
3985                         fSumSqrDeltaY    += (fValY - fMeanY) * (fValY - fMeanY);
3986                     }
3987                 }
3988             }
3989         } // for (SCSIZE i = 0; i < nC1; i++)
3990         if ( _bPearson )
3991         {
3992             if (fSumSqrDeltaX == 0.0 || ( !_bStexy && fSumSqrDeltaY == 0.0) )
3993                 PushError( errDivisionByZero);
3994             else if ( _bStexy )
3995                 PushDouble( sqrt( (fSumSqrDeltaY - fSumDeltaXDeltaY *
3996                             fSumDeltaXDeltaY / fSumSqrDeltaX) / (fCount-2)));
3997             else
3998                 PushDouble( fSumDeltaXDeltaY / sqrt( fSumSqrDeltaX * fSumSqrDeltaY));
3999         } // if ( _bPearson )
4000         else
4001         {
4002             PushDouble( fSumDeltaXDeltaY / fCount);
4003         }
4004     }
4005 }
4006 
ScRSQ()4007 void ScInterpreter::ScRSQ()
4008 {
4009     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScRSQ" );
4010     // Same as ScPearson()*ScPearson()
4011     ScPearson();
4012     if (!nGlobalError)
4013     {
4014         switch (GetStackType())
4015         {
4016             case formula::svDouble:
4017                 {
4018                     double fVal = PopDouble();
4019                     PushDouble( fVal * fVal);
4020                 }
4021                 break;
4022             default:
4023                 PopError();
4024                 PushNoValue();
4025         }
4026     }
4027 }
4028 
ScSTEXY()4029 void ScInterpreter::ScSTEXY()
4030 {
4031     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSTEXY" );
4032     CalculatePearsonCovar(sal_True,sal_True);
4033 }
CalculateSlopeIntercept(sal_Bool bSlope)4034 void ScInterpreter::CalculateSlopeIntercept(sal_Bool bSlope)
4035 {
4036     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::CalculateSlopeIntercept" );
4037     if ( !MustHaveParamCount( GetByte(), 2 ) )
4038         return;
4039     ScMatrixRef pMat1 = GetMatrix();
4040     ScMatrixRef pMat2 = GetMatrix();
4041     if (!pMat1 || !pMat2)
4042     {
4043         PushIllegalParameter();
4044         return;
4045     }
4046     SCSIZE nC1, nC2;
4047     SCSIZE nR1, nR2;
4048     pMat1->GetDimensions(nC1, nR1);
4049     pMat2->GetDimensions(nC2, nR2);
4050     if (nR1 != nR2 || nC1 != nC2)
4051     {
4052         PushIllegalArgument();
4053         return;
4054     }
4055     // #i78250# numerical stability improved
4056     double fCount           = 0.0;
4057     double fSumX            = 0.0;
4058     double fSumY            = 0.0;
4059     double fSumDeltaXDeltaY = 0.0; // sum of (ValX-MeanX)*(ValY-MeanY)
4060     double fSumSqrDeltaX    = 0.0; // sum of (ValX-MeanX)^2
4061     for (SCSIZE i = 0; i < nC1; i++)
4062     {
4063         for (SCSIZE j = 0; j < nR1; j++)
4064         {
4065             if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
4066             {
4067                 double fValX = pMat1->GetDouble(i,j);
4068                 double fValY = pMat2->GetDouble(i,j);
4069                 fSumX += fValX;
4070                 fSumY += fValY;
4071                 fCount++;
4072             }
4073         }
4074     }
4075     if (fCount < 1.0)
4076         PushNoValue();
4077     else
4078     {
4079         double fMeanX = fSumX / fCount;
4080         double fMeanY = fSumY / fCount;
4081         for (SCSIZE i = 0; i < nC1; i++)
4082         {
4083             for (SCSIZE j = 0; j < nR1; j++)
4084             {
4085                 if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
4086                 {
4087                     double fValX = pMat1->GetDouble(i,j);
4088                     double fValY = pMat2->GetDouble(i,j);
4089                     fSumDeltaXDeltaY += (fValX - fMeanX) * (fValY - fMeanY);
4090                     fSumSqrDeltaX    += (fValX - fMeanX) * (fValX - fMeanX);
4091                 }
4092             }
4093         }
4094         if (fSumSqrDeltaX == 0.0)
4095             PushError( errDivisionByZero);
4096         else
4097         {
4098             if ( bSlope )
4099                 PushDouble( fSumDeltaXDeltaY / fSumSqrDeltaX);
4100             else
4101                 PushDouble( fMeanY - fSumDeltaXDeltaY / fSumSqrDeltaX * fMeanX);
4102         }
4103     }
4104 }
4105 
ScSlope()4106 void ScInterpreter::ScSlope()
4107 {
4108     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSlope" );
4109     CalculateSlopeIntercept(sal_True);
4110 }
4111 
ScIntercept()4112 void ScInterpreter::ScIntercept()
4113 {
4114     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScIntercept" );
4115     CalculateSlopeIntercept(sal_False);
4116 }
4117 
ScForecast()4118 void ScInterpreter::ScForecast()
4119 {
4120     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScForecast" );
4121     if ( !MustHaveParamCount( GetByte(), 3 ) )
4122         return;
4123     ScMatrixRef pMat1 = GetMatrix();
4124     ScMatrixRef pMat2 = GetMatrix();
4125     if (!pMat1 || !pMat2)
4126     {
4127         PushIllegalParameter();
4128         return;
4129     }
4130     SCSIZE nC1, nC2;
4131     SCSIZE nR1, nR2;
4132     pMat1->GetDimensions(nC1, nR1);
4133     pMat2->GetDimensions(nC2, nR2);
4134     if (nR1 != nR2 || nC1 != nC2)
4135     {
4136         PushIllegalArgument();
4137         return;
4138     }
4139     double fVal = GetDouble();
4140     // #i78250# numerical stability improved
4141     double fCount           = 0.0;
4142     double fSumX            = 0.0;
4143     double fSumY            = 0.0;
4144     double fSumDeltaXDeltaY = 0.0; // sum of (ValX-MeanX)*(ValY-MeanY)
4145     double fSumSqrDeltaX    = 0.0; // sum of (ValX-MeanX)^2
4146     for (SCSIZE i = 0; i < nC1; i++)
4147     {
4148         for (SCSIZE j = 0; j < nR1; j++)
4149         {
4150             if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
4151             {
4152                 double fValX = pMat1->GetDouble(i,j);
4153                 double fValY = pMat2->GetDouble(i,j);
4154                 fSumX += fValX;
4155                 fSumY += fValY;
4156                 fCount++;
4157             }
4158         }
4159     }
4160     if (fCount < 1.0)
4161         PushNoValue();
4162     else
4163     {
4164         double fMeanX = fSumX / fCount;
4165         double fMeanY = fSumY / fCount;
4166         for (SCSIZE i = 0; i < nC1; i++)
4167         {
4168             for (SCSIZE j = 0; j < nR1; j++)
4169             {
4170                 if (!pMat1->IsString(i,j) && !pMat2->IsString(i,j))
4171                 {
4172                     double fValX = pMat1->GetDouble(i,j);
4173                     double fValY = pMat2->GetDouble(i,j);
4174                     fSumDeltaXDeltaY += (fValX - fMeanX) * (fValY - fMeanY);
4175                     fSumSqrDeltaX    += (fValX - fMeanX) * (fValX - fMeanX);
4176                 }
4177             }
4178         }
4179         if (fSumSqrDeltaX == 0.0)
4180             PushError( errDivisionByZero);
4181         else
4182             PushDouble( fMeanY + fSumDeltaXDeltaY / fSumSqrDeltaX * (fVal - fMeanX));
4183     }
4184 }
4185