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