xref: /core/sc/source/core/tool/interpr6.cxx (revision 18ff25ff) 
1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
2 /*
3  * This file is part of the LibreOffice project.
4  *
5  * This Source Code Form is subject to the terms of the Mozilla Public
6  * License, v. 2.0. If a copy of the MPL was not distributed with this
7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
8  *
9  * This file incorporates work covered by the following license notice:
10  *
11  *   Licensed to the Apache Software Foundation (ASF) under one or more
12  *   contributor license agreements. See the NOTICE file distributed
13  *   with this work for additional information regarding copyright
14  *   ownership. The ASF licenses this file to you under the Apache
15  *   License, Version 2.0 (the "License"); you may not use this file
16  *   except in compliance with the License. You may obtain a copy of
17  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
18  */
19 
20 #include <interpre.hxx>
21 #include <columnspanset.hxx>
22 #include <column.hxx>
23 #include <document.hxx>
24 #include <cellvalue.hxx>
25 #include <dociter.hxx>
26 #include <mtvcellfunc.hxx>
27 #include <scmatrix.hxx>
28 
29 #include <arraysumfunctor.hxx>
30 
31 #include <formula/token.hxx>
32 
33 using namespace formula;
34 
35 double const fHalfMachEps = 0.5 * ::std::numeric_limits<double>::epsilon();
36 
37 // The idea how this group of gamma functions is calculated, is
38 // based on the Cephes library
39 // online http://www.moshier.net/#Cephes [called 2008-02]
40 
41 /** You must ensure fA>0.0 && fX>0.0
42     valid results only if fX > fA+1.0
43     uses continued fraction with odd items */
44 double ScInterpreter::GetGammaContFraction( double fA, double fX )
45 {
46 
47     double const fBigInv = ::std::numeric_limits<double>::epsilon();
48     double const fBig = 1.0/fBigInv;
49     double fCount = 0.0;
50     double fY = 1.0 - fA;
51     double fDenom = fX + 2.0-fA;
52     double fPkm1 = fX + 1.0;
53     double fPkm2 = 1.0;
54     double fQkm1 = fDenom * fX;
55     double fQkm2 = fX;
56     double fApprox = fPkm1/fQkm1;
57     bool bFinished = false;
58     do
59     {
60         fCount = fCount +1.0;
61         fY = fY+ 1.0;
62         const double fNum = fY * fCount;
63         fDenom = fDenom +2.0;
64         double fPk = fPkm1 * fDenom  -  fPkm2 * fNum;
65         const double fQk = fQkm1 * fDenom  -  fQkm2 * fNum;
66         if (fQk != 0.0)
67         {
68             const double fR = fPk/fQk;
69             bFinished = (fabs( (fApprox - fR)/fR ) <= fHalfMachEps);
70             fApprox = fR;
71         }
72         fPkm2 = fPkm1;
73         fPkm1 = fPk;
74         fQkm2 = fQkm1;
75         fQkm1 = fQk;
76         if (fabs(fPk) > fBig)
77         {
78             // reduce a fraction does not change the value
79             fPkm2 = fPkm2 * fBigInv;
80             fPkm1 = fPkm1 * fBigInv;
81             fQkm2 = fQkm2 * fBigInv;
82             fQkm1 = fQkm1 * fBigInv;
83         }
84     } while (!bFinished && fCount<10000);
85     // most iterations, if fX==fAlpha+1.0; approx sqrt(fAlpha) iterations then
86     if (!bFinished)
87     {
88         SetError(FormulaError::NoConvergence);
89     }
90     return fApprox;
91 }
92 
93 /** You must ensure fA>0.0 && fX>0.0
94     valid results only if fX <= fA+1.0
95     uses power series */
96 double ScInterpreter::GetGammaSeries( double fA, double fX )
97 {
98     double fDenomfactor = fA;
99     double fSummand = 1.0/fA;
100     double fSum = fSummand;
101     int nCount=1;
102     do
103     {
104         fDenomfactor = fDenomfactor + 1.0;
105         fSummand = fSummand * fX/fDenomfactor;
106         fSum = fSum + fSummand;
107         nCount = nCount+1;
108     } while ( fSummand/fSum > fHalfMachEps && nCount<=10000);
109     // large amount of iterations will be carried out for huge fAlpha, even
110     // if fX <= fAlpha+1.0
111     if (nCount>10000)
112     {
113         SetError(FormulaError::NoConvergence);
114     }
115     return fSum;
116 }
117 
118 /** You must ensure fA>0.0 && fX>0.0) */
119 double ScInterpreter::GetLowRegIGamma( double fA, double fX )
120 {
121     double fLnFactor = fA * log(fX) - fX - GetLogGamma(fA);
122     double fFactor = exp(fLnFactor);    // Do we need more accuracy than exp(ln()) has?
123     if (fX>fA+1.0)  // includes fX>1.0; 1-GetUpRegIGamma, continued fraction
124         return 1.0 - fFactor * GetGammaContFraction(fA,fX);
125     else            // fX<=1.0 || fX<=fA+1.0, series
126         return fFactor * GetGammaSeries(fA,fX);
127 }
128 
129 /** You must ensure fA>0.0 && fX>0.0) */
130 double ScInterpreter::GetUpRegIGamma( double fA, double fX )
131 {
132 
133     double fLnFactor= fA*log(fX)-fX-GetLogGamma(fA);
134     double fFactor = exp(fLnFactor); //Do I need more accuracy than exp(ln()) has?;
135     if (fX>fA+1.0) // includes fX>1.0
136             return fFactor * GetGammaContFraction(fA,fX);
137     else //fX<=1 || fX<=fA+1, 1-GetLowRegIGamma, series
138             return 1.0 -fFactor * GetGammaSeries(fA,fX);
139 }
140 
141 /** Gamma distribution, probability density function.
142     fLambda is "scale" parameter
143     You must ensure fAlpha>0.0 and fLambda>0.0 */
144 double ScInterpreter::GetGammaDistPDF( double fX, double fAlpha, double fLambda )
145 {
146     if (fX < 0.0)
147         return 0.0;     // see ODFF
148     else if (fX == 0)
149         // in this case 0^0 isn't zero
150     {
151         if (fAlpha < 1.0)
152         {
153             SetError(FormulaError::DivisionByZero);  // should be #DIV/0
154             return HUGE_VAL;
155         }
156         else if (fAlpha == 1)
157         {
158             return (1.0 / fLambda);
159         }
160         else
161         {
162             return 0.0;
163         }
164     }
165     else
166     {
167         double fXr = fX / fLambda;
168         // use exp(ln()) only for large arguments because of less accuracy
169         if (fXr > 1.0)
170         {
171             const double fLogDblMax = log( ::std::numeric_limits<double>::max());
172             if (log(fXr) * (fAlpha-1.0) < fLogDblMax && fAlpha < fMaxGammaArgument)
173             {
174                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / GetGamma(fAlpha);
175             }
176             else
177             {
178                 return exp( (fAlpha-1.0) * log(fXr) - fXr - log(fLambda) - GetLogGamma(fAlpha));
179             }
180         }
181         else    // fXr near to zero
182         {
183             if (fAlpha<fMaxGammaArgument)
184             {
185                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / GetGamma(fAlpha);
186             }
187             else
188             {
189                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / exp( GetLogGamma(fAlpha));
190             }
191         }
192     }
193 }
194 
195 /** Gamma distribution, cumulative distribution function.
196     fLambda is "scale" parameter
197     You must ensure fAlpha>0.0 and fLambda>0.0 */
198 double ScInterpreter::GetGammaDist( double fX, double fAlpha, double fLambda )
199 {
200     if (fX <= 0.0)
201         return 0.0;
202     else
203         return GetLowRegIGamma( fAlpha, fX / fLambda);
204 }
205 
206 class NumericCellAccumulator
207 {
208     double mfFirst;
209     double mfRest;
210     FormulaError mnError;
211 
212 public:
213     NumericCellAccumulator() : mfFirst(0.0), mfRest(0.0), mnError(FormulaError::NONE) {}
214 
215     void operator() (const sc::CellStoreType::value_type& rNode, size_t nOffset, size_t nDataSize)
216     {
217         switch (rNode.type)
218         {
219             case sc::element_type_numeric:
220             {
221                 const double *p = &sc::numeric_block::at(*rNode.data, nOffset);
222                 size_t i = 0;
223 
224                 // Store the first non-zero value in mfFirst (for some reason).
225                 if (!mfFirst)
226                 {
227                     for (i = 0; i < nDataSize; ++i)
228                     {
229                         if (!mfFirst)
230                             mfFirst = p[i];
231                         else
232                             break;
233                     }
234                 }
235                 p += i;
236                 nDataSize -= i;
237                 if (nDataSize == 0)
238                     return;
239 
240                 sc::ArraySumFunctor functor(p, nDataSize);
241 
242                 mfRest += functor();
243                 break;
244             }
245 
246             case sc::element_type_formula:
247             {
248                 sc::formula_block::const_iterator it = sc::formula_block::begin(*rNode.data);
249                 std::advance(it, nOffset);
250                 sc::formula_block::const_iterator itEnd = it;
251                 std::advance(itEnd, nDataSize);
252                 for (; it != itEnd; ++it)
253                 {
254                     double fVal = 0.0;
255                     FormulaError nErr = FormulaError::NONE;
256                     ScFormulaCell& rCell = *(*it);
257                     if (!rCell.GetErrorOrValue(nErr, fVal))
258                         // The cell has neither error nor value.  Perhaps string result.
259                         continue;
260 
261                     if (nErr != FormulaError::NONE)
262                     {
263                         // Cell has error - skip all the rest
264                         mnError = nErr;
265                         return;
266                     }
267 
268                     if ( !mfFirst )
269                         mfFirst = fVal;
270                     else
271                         mfRest += fVal;
272                 }
273             }
274             break;
275             default:
276                 ;
277         }
278     }
279 
280     FormulaError getError() const { return mnError; }
281     double getFirst() const { return mfFirst; }
282     double getRest() const { return mfRest; }
283 };
284 
285 class NumericCellCounter
286 {
287     size_t mnCount;
288 public:
289     NumericCellCounter() : mnCount(0) {}
290 
291     void operator() (const sc::CellStoreType::value_type& rNode, size_t nOffset, size_t nDataSize)
292     {
293         switch (rNode.type)
294         {
295             case sc::element_type_numeric:
296                 mnCount += nDataSize;
297             break;
298             case sc::element_type_formula:
299             {
300                 sc::formula_block::const_iterator it = sc::formula_block::begin(*rNode.data);
301                 std::advance(it, nOffset);
302                 sc::formula_block::const_iterator itEnd = it;
303                 std::advance(itEnd, nDataSize);
304                 for (; it != itEnd; ++it)
305                 {
306                     ScFormulaCell& rCell = **it;
307                     if (rCell.IsValueNoError())
308                         ++mnCount;
309                 }
310             }
311             break;
312             default:
313                 ;
314         }
315     }
316 
317     size_t getCount() const { return mnCount; }
318 };
319 
320 class FuncCount : public sc::ColumnSpanSet::ColumnAction
321 {
322     const ScInterpreterContext& mrContext;
323     sc::ColumnBlockConstPosition maPos;
324     ScColumn* mpCol;
325     size_t mnCount;
326     sal_uInt32 mnNumFmt;
327 
328 public:
329     FuncCount(const ScInterpreterContext& rContext) : mrContext(rContext), mpCol(nullptr), mnCount(0), mnNumFmt(0) {}
330 
331     virtual void startColumn(ScColumn* pCol) override
332     {
333         mpCol = pCol;
334         mpCol->InitBlockPosition(maPos);
335     }
336 
337     virtual void execute(SCROW nRow1, SCROW nRow2, bool bVal) override
338     {
339         if (!bVal)
340             return;
341 
342         NumericCellCounter aFunc;
343         maPos.miCellPos = sc::ParseBlock(maPos.miCellPos, mpCol->GetCellStore(), aFunc, nRow1, nRow2);
344         mnCount += aFunc.getCount();
345         mnNumFmt = mpCol->GetNumberFormat(mrContext, nRow2);
346     };
347 
348     size_t getCount() const { return mnCount; }
349     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
350 };
351 
352 class FuncSum : public sc::ColumnSpanSet::ColumnAction
353 {
354     const ScInterpreterContext& mrContext;
355     sc::ColumnBlockConstPosition maPos;
356     ScColumn* mpCol;
357     double mfSum;
358     FormulaError mnError;
359     sal_uInt32 mnNumFmt;
360 
361 public:
362     FuncSum(const ScInterpreterContext& rContext) : mrContext(rContext), mpCol(nullptr), mfSum(0.0), mnError(FormulaError::NONE), mnNumFmt(0) {}
363 
364     virtual void startColumn(ScColumn* pCol) override
365     {
366         mpCol = pCol;
367         mpCol->InitBlockPosition(maPos);
368     }
369 
370     virtual void execute(SCROW, SCROW, bool) override {}
371 
372     virtual void executeSum(SCROW nRow1, SCROW nRow2, bool bVal, double& fMem ) override
373     {
374         if (!bVal)
375             return;
376 
377         if (mnError != FormulaError::NONE)
378             return;
379 
380         NumericCellAccumulator aFunc;
381         maPos.miCellPos = sc::ParseBlock(maPos.miCellPos, mpCol->GetCellStore(), aFunc, nRow1, nRow2);
382         mnError = aFunc.getError();
383         if (mnError != FormulaError::NONE)
384             return;
385 
386         if ( fMem )
387             mfSum += aFunc.getFirst() + aFunc.getRest();
388         else
389         {
390             fMem = aFunc.getFirst();
391             mfSum += aFunc.getRest();
392         }
393 
394         mnNumFmt = mpCol->GetNumberFormat(mrContext, nRow2);
395     };
396 
397     FormulaError getError() const { return mnError; }
398     double getSum() const { return mfSum; }
399     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
400 };
401 
402 static void IterateMatrix(
403     const ScMatrixRef& pMat, ScIterFunc eFunc, bool bTextAsZero,
404     sal_uLong& rCount, SvNumFormatType& rFuncFmtType, double& fRes, double& fMem )
405 {
406     if (!pMat)
407         return;
408 
409     // TODO fdo73148 take mnSubTotalFlags into account
410     rFuncFmtType = SvNumFormatType::NUMBER;
411     switch (eFunc)
412     {
413         case ifAVERAGE:
414         case ifSUM:
415         {
416             ScMatrix::IterateResult aRes = pMat->Sum(bTextAsZero);
417             // If the first value is a NaN, it probably means it was an empty cell,
418             // and should be treated as zero.
419             if ( !rtl::math::isFinite(aRes.mfFirst) )
420             {
421                 sal_uInt32 nErr = reinterpret_cast< sal_math_Double * >(&aRes.mfFirst)->nan_parts.fraction_lo;
422                 if (nErr & 0xffff0000)
423                 {
424                     aRes.mfFirst = 0;
425                 }
426             }
427             if ( fMem )
428                 fRes += aRes.mfFirst + aRes.mfRest;
429             else
430             {
431                 fMem = aRes.mfFirst;
432                 fRes += aRes.mfRest;
433             }
434             rCount += aRes.mnCount;
435         }
436         break;
437         case ifCOUNT:
438             rCount += pMat->Count(bTextAsZero, false);  // do not count error values
439         break;
440         case ifCOUNT2:
441             rCount += pMat->Count(true, true);          // do count error values
442         break;
443         case ifPRODUCT:
444         {
445             ScMatrix::IterateResult aRes = pMat->Product(bTextAsZero);
446             fRes *= aRes.mfFirst;
447             fRes *= aRes.mfRest;
448             rCount += aRes.mnCount;
449         }
450         break;
451         case ifSUMSQ:
452         {
453             ScMatrix::IterateResult aRes = pMat->SumSquare(bTextAsZero);
454             fRes += aRes.mfFirst;
455             fRes += aRes.mfRest;
456             rCount += aRes.mnCount;
457         }
458         break;
459         default:
460             ;
461     }
462 }
463 
464 size_t ScInterpreter::GetRefListArrayMaxSize( short nParamCount )
465 {
466     size_t nSize = 0;
467     if (IsInArrayContext())
468     {
469         for (short i=1; i <= nParamCount; ++i)
470         {
471             if (GetStackType(i) == svRefList)
472             {
473                 const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp - i]);
474                 if (p && p->IsArrayResult() && p->GetRefList()->size() > nSize)
475                     nSize = p->GetRefList()->size();
476             }
477         }
478     }
479     return nSize;
480 }
481 
482 static double lcl_IterResult( ScIterFunc eFunc, double fRes, double fMem, sal_uLong nCount )
483 {
484     switch( eFunc )
485     {
486         case ifSUM:
487             fRes = ::rtl::math::approxAdd( fRes, fMem );
488         break;
489         case ifAVERAGE:
490             fRes = sc::div( ::rtl::math::approxAdd( fRes, fMem ), nCount);
491         break;
492         case ifCOUNT2:
493         case ifCOUNT:
494             fRes = nCount;
495         break;
496         case ifPRODUCT:
497             if ( !nCount )
498                 fRes = 0.0;
499         break;
500         default:
501             ; // nothing
502     }
503     return fRes;
504 }
505 
506 void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
507 {
508     short nParamCount = GetByte();
509     const SCSIZE nMatRows = GetRefListArrayMaxSize( nParamCount);
510     ScMatrixRef xResMat, xResCount;
511     auto ResInitVal = [eFunc]()
512     {
513         return (eFunc == ifPRODUCT) ? 1.0 : 0.0;
514     };
515     double fRes = ResInitVal();
516     double fVal = 0.0;
517     double fMem = 0.0;  // first numeric value != 0.0
518     sal_uLong nCount = 0;
519     ScAddress aAdr;
520     ScRange aRange;
521     size_t nRefInList = 0;
522     size_t nRefArrayPos = std::numeric_limits<size_t>::max();
523     if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
524          ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
525         nGlobalError = FormulaError::NONE;
526     while (nParamCount-- > 0)
527     {
528         switch (GetStackType())
529         {
530             case svString:
531             {
532                 if( eFunc == ifCOUNT )
533                 {
534                     OUString aStr = PopString().getString();
535                     if ( bTextAsZero )
536                         nCount++;
537                     else
538                     {
539                         // Only check if string can be converted to number, no
540                         // error propagation.
541                         FormulaError nErr = nGlobalError;
542                         nGlobalError = FormulaError::NONE;
543                         ConvertStringToValue( aStr );
544                         if (nGlobalError == FormulaError::NONE)
545                             ++nCount;
546                         nGlobalError = nErr;
547                     }
548                 }
549                 else
550                 {
551                     Pop();
552                     switch ( eFunc )
553                     {
554                         case ifAVERAGE:
555                         case ifSUM:
556                         case ifSUMSQ:
557                         case ifPRODUCT:
558                         {
559                             if ( bTextAsZero )
560                             {
561                                 nCount++;
562                                 if ( eFunc == ifPRODUCT )
563                                     fRes = 0.0;
564                             }
565                             else
566                             {
567                                 while (nParamCount-- > 0)
568                                     Pop();
569                                 SetError( FormulaError::NoValue );
570                             }
571                         }
572                         break;
573                         default:
574                             nCount++;
575                     }
576                 }
577             }
578             break;
579             case svDouble    :
580                 fVal = GetDouble();
581                 nCount++;
582                 switch( eFunc )
583                 {
584                     case ifAVERAGE:
585                     case ifSUM:
586                         if ( fMem )
587                             fRes += fVal;
588                         else
589                             fMem = fVal;
590                         break;
591                     case ifSUMSQ:   fRes += fVal * fVal; break;
592                     case ifPRODUCT: fRes *= fVal; break;
593                     default: ; // nothing
594                 }
595                 nFuncFmtType = SvNumFormatType::NUMBER;
596                 break;
597             case svExternalSingleRef:
598             {
599                 ScExternalRefCache::TokenRef pToken;
600                 ScExternalRefCache::CellFormat aFmt;
601                 PopExternalSingleRef(pToken, &aFmt);
602                 if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
603                      ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
604                 {
605                     nGlobalError = FormulaError::NONE;
606                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
607                         ++nCount;
608                     break;
609                 }
610 
611                 if (!pToken)
612                     break;
613 
614                 StackVar eType = pToken->GetType();
615                 if (eFunc == ifCOUNT2)
616                 {
617                     if ( eType != svEmptyCell &&
618                          ( ( pToken->GetOpCode() != ocSubTotal &&
619                              pToken->GetOpCode() != ocAggregate ) ||
620                            ( mnSubTotalFlags & SubtotalFlags::IgnoreNestedStAg ) ) )
621                         nCount++;
622                     if (nGlobalError != FormulaError::NONE)
623                         nGlobalError = FormulaError::NONE;
624                 }
625                 else if (eType == svDouble)
626                 {
627                     nCount++;
628                     fVal = pToken->GetDouble();
629                     if (aFmt.mbIsSet)
630                     {
631                         nFuncFmtType = aFmt.mnType;
632                         nFuncFmtIndex = aFmt.mnIndex;
633                     }
634                     switch( eFunc )
635                     {
636                         case ifAVERAGE:
637                         case ifSUM:
638                             if ( fMem )
639                                 fRes += fVal;
640                             else
641                                 fMem = fVal;
642                             break;
643                         case ifSUMSQ:   fRes += fVal * fVal; break;
644                         case ifPRODUCT: fRes *= fVal; break;
645                         case ifCOUNT:
646                             if ( nGlobalError != FormulaError::NONE )
647                             {
648                                 nGlobalError = FormulaError::NONE;
649                                 nCount--;
650                             }
651                             break;
652                         default: ; // nothing
653                     }
654                 }
655                 else if (bTextAsZero && eType == svString)
656                 {
657                     nCount++;
658                     if ( eFunc == ifPRODUCT )
659                         fRes = 0.0;
660                 }
661             }
662             break;
663             case svSingleRef :
664             {
665                 PopSingleRef( aAdr );
666                 if (nGlobalError == FormulaError::NoRef)
667                 {
668                     PushError( FormulaError::NoRef);
669                     return;
670                 }
671 
672                 if ( ( ( mnSubTotalFlags & SubtotalFlags::IgnoreFiltered ) &&
673                      pDok->RowFiltered( aAdr.Row(), aAdr.Tab() ) ) ||
674                      ( ( mnSubTotalFlags & SubtotalFlags::IgnoreHidden ) &&
675                        pDok->RowHidden( aAdr.Row(), aAdr.Tab() ) ) )
676                 {
677                     break;
678                 }
679                 if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
680                      ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
681                 {
682                     nGlobalError = FormulaError::NONE;
683                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
684                         ++nCount;
685                     break;
686                 }
687                 ScRefCellValue aCell(*pDok, aAdr);
688                 if (!aCell.isEmpty())
689                 {
690                     if( eFunc == ifCOUNT2 )
691                     {
692                         CellType eCellType = aCell.meType;
693                         if ( eCellType != CELLTYPE_NONE )
694                             nCount++;
695                         if ( nGlobalError != FormulaError::NONE )
696                             nGlobalError = FormulaError::NONE;
697                     }
698                     else if (aCell.hasNumeric())
699                     {
700                         nCount++;
701                         fVal = GetCellValue(aAdr, aCell);
702                         CurFmtToFuncFmt();
703                         switch( eFunc )
704                         {
705                             case ifAVERAGE:
706                             case ifSUM:
707                                 if ( fMem )
708                                     fRes += fVal;
709                                 else
710                                     fMem = fVal;
711                                 break;
712                             case ifSUMSQ:   fRes += fVal * fVal; break;
713                             case ifPRODUCT: fRes *= fVal; break;
714                             case ifCOUNT:
715                                 if ( nGlobalError != FormulaError::NONE )
716                                 {
717                                     nGlobalError = FormulaError::NONE;
718                                     nCount--;
719                                 }
720                                 break;
721                             default: ; // nothing
722                         }
723                     }
724                     else if (bTextAsZero && aCell.hasString())
725                     {
726                         nCount++;
727                         if ( eFunc == ifPRODUCT )
728                             fRes = 0.0;
729                     }
730                 }
731             }
732             break;
733             case svRefList :
734             {
735                 const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp-1]);
736                 if (p && p->IsArrayResult())
737                 {
738                     nRefArrayPos = nRefInList;
739                     if ((eFunc == ifSUM || eFunc == ifAVERAGE) && fMem != 0.0)
740                     {
741                         fRes = rtl::math::approxAdd( fRes, fMem);
742                         fMem = 0.0;
743                     }
744                     // The "one value to all references of an array" seems to
745                     // be what Excel does if there are other types than just
746                     // arrays of references.
747                     if (!xResMat)
748                     {
749                         // Create and init all elements with current value.
750                         assert(nMatRows > 0);
751                         xResMat = GetNewMat( 1, nMatRows, true);
752                         xResMat->FillDouble( fRes, 0,0, 0,nMatRows-1);
753                         if (eFunc != ifSUM)
754                         {
755                             xResCount = GetNewMat( 1, nMatRows, true);
756                             xResCount->FillDouble( nCount, 0,0, 0,nMatRows-1);
757                         }
758                     }
759                     else
760                     {
761                         // Current value and values from vector are operands
762                         // for each vector position.
763                         if (nCount && xResCount)
764                         {
765                             for (SCSIZE i=0; i < nMatRows; ++i)
766                             {
767                                 xResCount->PutDouble( xResCount->GetDouble(0,i) + nCount, 0,i);
768                             }
769                         }
770                         if (fRes != ResInitVal())
771                         {
772                             for (SCSIZE i=0; i < nMatRows; ++i)
773                             {
774                                 double fVecRes = xResMat->GetDouble(0,i);
775                                 if (eFunc == ifPRODUCT)
776                                     fVecRes *= fRes;
777                                 else
778                                     fVecRes += fRes;
779                                 xResMat->PutDouble( fVecRes, 0,i);
780                             }
781                         }
782                     }
783                     fRes = ResInitVal();
784                     nCount = 0;
785                 }
786             }
787             SAL_FALLTHROUGH;
788             case svDoubleRef :
789             {
790                 PopDoubleRef( aRange, nParamCount, nRefInList);
791                 if (nGlobalError == FormulaError::NoRef)
792                 {
793                     PushError( FormulaError::NoRef);
794                     return;
795                 }
796 
797                 if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
798                      ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
799                 {
800                     nGlobalError = FormulaError::NONE;
801                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
802                         ++nCount;
803                     if ( eFunc == ifCOUNT2 || eFunc == ifCOUNT )
804                         break;
805                 }
806                 if( eFunc == ifCOUNT2 )
807                 {
808                     ScCellIterator aIter( pDok, aRange, mnSubTotalFlags );
809                     for (bool bHas = aIter.first(); bHas; bHas = aIter.next())
810                     {
811                         if ( !aIter.isEmpty() )
812                         {
813                             ++nCount;
814                         }
815                     }
816 
817                     if ( nGlobalError != FormulaError::NONE )
818                         nGlobalError = FormulaError::NONE;
819                 }
820                 else if ( ( eFunc == ifSUM || eFunc == ifCOUNT ) && mnSubTotalFlags == SubtotalFlags::NONE )
821                 {
822                     sc::RangeColumnSpanSet aSet( aRange );
823 
824                     if ( eFunc == ifSUM )
825                     {
826                         FuncSum aAction(mrContext);
827                         aSet.executeColumnAction( *pDok, aAction, fMem );
828                         FormulaError nErr = aAction.getError();
829                         if ( nErr != FormulaError::NONE )
830                         {
831                             PushError( nErr );
832                             return;
833                         }
834                         fRes += aAction.getSum();
835 
836                         // Get the number format of the last iterated cell.
837                         nFuncFmtIndex = aAction.getNumberFormat();
838                     }
839                     else
840                     {
841                         FuncCount aAction(mrContext);
842                         aSet.executeColumnAction(*pDok, aAction);
843                         nCount += aAction.getCount();
844 
845                         // Get the number format of the last iterated cell.
846                         nFuncFmtIndex = aAction.getNumberFormat();
847                     }
848 
849                     nFuncFmtType = mrContext.GetFormatTable()->GetType( nFuncFmtIndex );
850                 }
851                 else
852                 {
853                     ScValueIterator aValIter( pDok, aRange, mnSubTotalFlags, bTextAsZero );
854                     aValIter.SetInterpreterContext( &mrContext );
855                     FormulaError nErr = FormulaError::NONE;
856                     if (aValIter.GetFirst(fVal, nErr))
857                     {
858                         // placed the loop on the inside for performance reasons:
859                         aValIter.GetCurNumFmtInfo( mrContext, nFuncFmtType, nFuncFmtIndex );
860                         switch( eFunc )
861                         {
862                             case ifAVERAGE:
863                             case ifSUM:
864                                     if ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal )
865                                     {
866                                         do
867                                         {
868                                             if ( nErr == FormulaError::NONE )
869                                             {
870                                                 SetError(nErr);
871                                                 if ( fMem )
872                                                     fRes += fVal;
873                                                 else
874                                                     fMem = fVal;
875                                                 nCount++;
876                                             }
877                                         }
878                                         while (aValIter.GetNext(fVal, nErr));
879                                     }
880                                     else
881                                     {
882                                         do
883                                         {
884                                             SetError(nErr);
885                                             if ( fMem )
886                                                 fRes += fVal;
887                                             else
888                                                 fMem = fVal;
889                                             nCount++;
890                                         }
891                                         while (aValIter.GetNext(fVal, nErr));
892                                     }
893                                     break;
894                             case ifSUMSQ:
895                                     if ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal )
896                                     {
897                                         do
898                                         {
899                                             if ( nErr == FormulaError::NONE )
900                                             {
901                                                 SetError(nErr);
902                                                 fRes += fVal * fVal;
903                                                 nCount++;
904                                             }
905                                         }
906                                         while (aValIter.GetNext(fVal, nErr));
907                                     }
908                                     else
909                                     {
910                                         do
911                                         {
912                                             SetError(nErr);
913                                             fRes += fVal * fVal;
914                                             nCount++;
915                                         }
916                                         while (aValIter.GetNext(fVal, nErr));
917                                     }
918                                     break;
919                             case ifPRODUCT:
920                                     do
921                                     {
922                                         if ( !( nErr != FormulaError::NONE && ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
923                                         {
924                                             SetError(nErr);
925                                             fRes *= fVal;
926                                             nCount++;
927                                         }
928                                     }
929                                     while (aValIter.GetNext(fVal, nErr));
930                                     break;
931                             case ifCOUNT:
932                                     do
933                                     {
934                                         if ( nErr == FormulaError::NONE )
935                                             nCount++;
936                                     }
937                                     while (aValIter.GetNext(fVal, nErr));
938                                     break;
939                             default: ;  // nothing
940                         }
941                         SetError( nErr );
942                     }
943                 }
944                 if (nRefArrayPos != std::numeric_limits<size_t>::max())
945                 {
946                     // Update vector element with current value.
947                     if ((eFunc == ifSUM || eFunc == ifAVERAGE) && fMem != 0.0)
948                     {
949                         fRes = rtl::math::approxAdd( fRes, fMem);
950                         fMem = 0.0;
951                     }
952                     if (xResCount)
953                         xResCount->PutDouble( xResCount->GetDouble(0,nRefArrayPos) + nCount, 0,nRefArrayPos);
954                     double fVecRes = xResMat->GetDouble(0,nRefArrayPos);
955                     if (eFunc == ifPRODUCT)
956                         fVecRes *= fRes;
957                     else
958                         fVecRes += fRes;
959                     xResMat->PutDouble( fVecRes, 0,nRefArrayPos);
960                     // Reset.
961                     fRes = ResInitVal();
962                     nCount = 0;
963                     nRefArrayPos = std::numeric_limits<size_t>::max();
964                 }
965             }
966             break;
967             case svExternalDoubleRef:
968             {
969                 ScMatrixRef pMat;
970                 PopExternalDoubleRef(pMat);
971                 if ( nGlobalError != FormulaError::NONE && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
972                     break;
973 
974                 IterateMatrix( pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem );
975             }
976             break;
977             case svMatrix :
978             {
979                 ScMatrixRef pMat = PopMatrix();
980 
981                 IterateMatrix( pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem );
982             }
983             break;
984             case svError:
985             {
986                 PopError();
987                 if ( eFunc == ifCOUNT || ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
988                 {
989                     nGlobalError = FormulaError::NONE;
990                 }
991                 else if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
992                 {
993                     nCount++;
994                     nGlobalError = FormulaError::NONE;
995                 }
996             }
997             break;
998             default :
999                 while (nParamCount-- > 0)
1000                     PopError();
1001                 SetError(FormulaError::IllegalParameter);
1002         }
1003     }
1004 
1005     // A boolean return type makes no sense on sums et al.
1006     // Counts are always numbers.
1007     if( nFuncFmtType == SvNumFormatType::LOGICAL || eFunc == ifCOUNT || eFunc == ifCOUNT2 )
1008         nFuncFmtType = SvNumFormatType::NUMBER;
1009 
1010     if (xResMat)
1011     {
1012         // Include value of last non-references-array type and calculate final result.
1013         for (SCSIZE i=0; i < nMatRows; ++i)
1014         {
1015             sal_uLong nVecCount = (xResCount ? nCount + xResCount->GetDouble(0,i) : nCount);
1016             double fVecRes = xResMat->GetDouble(0,i);
1017             if (eFunc == ifPRODUCT)
1018                 fVecRes *= fRes;
1019             else
1020                 fVecRes += fRes;
1021             fVecRes = lcl_IterResult( eFunc, fVecRes, fMem, nVecCount);
1022             xResMat->PutDouble( fVecRes, 0,i);
1023         }
1024         PushMatrix( xResMat);
1025     }
1026     else
1027     {
1028         PushDouble( lcl_IterResult( eFunc, fRes, fMem, nCount));
1029     }
1030 }
1031 
1032 void ScInterpreter::ScSumSQ()
1033 {
1034     IterateParameters( ifSUMSQ );
1035 }
1036 
1037 void ScInterpreter::ScSum()
1038 {
1039     IterateParameters( ifSUM );
1040 }
1041 
1042 void ScInterpreter::ScProduct()
1043 {
1044     IterateParameters( ifPRODUCT );
1045 }
1046 
1047 void ScInterpreter::ScAverage( bool bTextAsZero )
1048 {
1049     IterateParameters( ifAVERAGE, bTextAsZero );
1050 }
1051 
1052 void ScInterpreter::ScCount()
1053 {
1054     IterateParameters( ifCOUNT );
1055 }
1056 
1057 void ScInterpreter::ScCount2()
1058 {
1059     IterateParameters( ifCOUNT2 );
1060 }
1061 
1062 void ScInterpreter::ScRawSubtract()
1063 {
1064     short nParamCount = GetByte();
1065     if (!MustHaveParamCountMin( nParamCount, 2))
1066         return;
1067 
1068     // Fish the 1st parameter from the stack and push it on top.
1069     const FormulaToken* p = pStack[ sp - nParamCount ];
1070     PushWithoutError( *p );
1071     // Obtain the minuend.
1072     double fRes = GetDouble();
1073 
1074     while (nGlobalError == FormulaError::NONE && nParamCount > 1)
1075     {
1076         // Simple single values without matrix support.
1077         fRes -= GetDouble();
1078         --nParamCount;
1079     }
1080     while (nParamCount-- > 0)
1081         PopError();
1082 
1083     PushDouble( fRes);
1084 }
1085 
1086 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
1087