sc/source/core/tool/interpr6.cxx

   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 <mtvfunctions.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 namespace {
 207
 208 class NumericCellAccumulator
 209 {
 210     KahanSum maSum;
 211     FormulaError mnError;
 212
 213 public:
 214     NumericCellAccumulator() : maSum(0.0), mnError(FormulaError::NONE) {}
 215
 216     void operator() (const sc::CellStoreType::value_type& rNode, size_t nOffset, size_t nDataSize)
 217     {
 218         switch (rNode.type)
 219         {
 220             case sc::element_type_numeric:
 221             {
 222                 if (nDataSize == 0)
 223                     return;
 224
 225                 const double *p = &sc::numeric_block::at(*rNode.data, nOffset);
 226                 maSum += sc::op::sumArray(p, nDataSize);
 227                 break;
 228             }
 229
 230             case sc::element_type_formula:
 231             {
 232                 sc::formula_block::const_iterator it = sc::formula_block::begin(*rNode.data);
 233                 std::advance(it, nOffset);
 234                 sc::formula_block::const_iterator itEnd = it;
 235                 std::advance(itEnd, nDataSize);
 236                 for (; it != itEnd; ++it)
 237                 {
 238                     double fVal = 0.0;
 239                     FormulaError nErr = FormulaError::NONE;
 240                     ScFormulaCell& rCell = *(*it);
 241                     if (!rCell.GetErrorOrValue(nErr, fVal))
 242                         // The cell has neither error nor value.  Perhaps string result.
 243                         continue;
 244
 245                     if (nErr != FormulaError::NONE)
 246                     {
 247                         // Cell has error - skip all the rest
 248                         mnError = nErr;
 249                         return;
 250                     }
 251
 252                     maSum += fVal;
 253                 }
 254             }
 255             break;
 256             default:
 257                 ;
 258         }
 259     }
 260
 261     FormulaError getError() const { return mnError; }
 262     const KahanSum& getResult() const { return maSum; }
 263 };
 264
 265 class NumericCellCounter
 266 {
 267     size_t mnCount;
 268 public:
 269     NumericCellCounter() : mnCount(0) {}
 270
 271     void operator() (const sc::CellStoreType::value_type& rNode, size_t nOffset, size_t nDataSize)
 272     {
 273         switch (rNode.type)
 274         {
 275             case sc::element_type_numeric:
 276                 mnCount += nDataSize;
 277             break;
 278             case sc::element_type_formula:
 279             {
 280                 sc::formula_block::const_iterator it = sc::formula_block::begin(*rNode.data);
 281                 std::advance(it, nOffset);
 282                 sc::formula_block::const_iterator itEnd = it;
 283                 std::advance(itEnd, nDataSize);
 284                 for (; it != itEnd; ++it)
 285                 {
 286                     ScFormulaCell& rCell = **it;
 287                     if (rCell.IsValueNoError())
 288                         ++mnCount;
 289                 }
 290             }
 291             break;
 292             default:
 293                 ;
 294         }
 295     }
 296
 297     size_t getCount() const { return mnCount; }
 298 };
 299
 300 class FuncCount : public sc::ColumnSpanSet::ColumnAction
 301 {
 302     const ScInterpreterContext& mrContext;
 303     sc::ColumnBlockConstPosition maPos;
 304     ScColumn* mpCol;
 305     size_t mnCount;
 306     sal_uInt32 mnNumFmt;
 307
 308 public:
 309     FuncCount(const ScInterpreterContext& rContext) : mrContext(rContext), mpCol(nullptr), mnCount(0), mnNumFmt(0) {}
 310
 311     virtual void startColumn(ScColumn* pCol) override
 312     {
 313         mpCol = pCol;
 314         mpCol->InitBlockPosition(maPos);
 315     }
 316
 317     virtual void execute(SCROW nRow1, SCROW nRow2, bool bVal) override
 318     {
 319         if (!bVal)
 320             return;
 321
 322         NumericCellCounter aFunc;
 323         maPos.miCellPos = sc::ParseBlock(maPos.miCellPos, mpCol->GetCellStore(), aFunc, nRow1, nRow2);
 324         mnCount += aFunc.getCount();
 325         mnNumFmt = mpCol->GetNumberFormat(mrContext, nRow2);
 326     };
 327
 328     size_t getCount() const { return mnCount; }
 329     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
 330 };
 331
 332 class FuncSum : public sc::ColumnSpanSet::ColumnAction
 333 {
 334     const ScInterpreterContext& mrContext;
 335     sc::ColumnBlockConstPosition maPos;
 336     ScColumn* mpCol;
 337     KahanSum mfSum;
 338     FormulaError mnError;
 339     sal_uInt32 mnNumFmt;
 340
 341 public:
 342     FuncSum(const ScInterpreterContext& rContext) : mrContext(rContext), mpCol(nullptr), mfSum(0.0), mnError(FormulaError::NONE), mnNumFmt(0) {}
 343
 344     virtual void startColumn(ScColumn* pCol) override
 345     {
 346         mpCol = pCol;
 347         mpCol->InitBlockPosition(maPos);
 348     }
 349
 350     virtual void execute(SCROW nRow1, SCROW nRow2, bool bVal) override
 351     {
 352         if (!bVal)
 353             return;
 354
 355         if (mnError != FormulaError::NONE)
 356             return;
 357
 358         NumericCellAccumulator aFunc;
 359         maPos.miCellPos = sc::ParseBlock(maPos.miCellPos, mpCol->GetCellStore(), aFunc, nRow1, nRow2);
 360         mnError = aFunc.getError();
 361         if (mnError != FormulaError::NONE)
 362             return;
 363
 364
 365         mfSum += aFunc.getResult();
 366         mnNumFmt = mpCol->GetNumberFormat(mrContext, nRow2);
 367     };
 368
 369     FormulaError getError() const { return mnError; }
 370     const KahanSum& getSum() const { return mfSum; }
 371     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
 372 };
 373
 374 }
 375
 376 static void IterateMatrix(
 377     const ScMatrixRef& pMat, ScIterFunc eFunc, bool bTextAsZero, SubtotalFlags nSubTotalFlags,
 378     sal_uLong& rCount, SvNumFormatType& rFuncFmtType, KahanSum& fRes )
 379 {
 380     if (!pMat)
 381         return;
 382
 383     const bool bIgnoreErrVal = bool(nSubTotalFlags & SubtotalFlags::IgnoreErrVal);
 384     rFuncFmtType = SvNumFormatType::NUMBER;
 385     switch (eFunc)
 386     {
 387         case ifAVERAGE:
 388         case ifSUM:
 389         {
 390             ScMatrix::KahanIterateResult aRes = pMat->Sum(bTextAsZero, bIgnoreErrVal);
 391             fRes += aRes.maAccumulator;
 392             rCount += aRes.mnCount;
 393         }
 394         break;
 395         case ifCOUNT:
 396             rCount += pMat->Count(bTextAsZero, false);  // do not count error values
 397         break;
 398         case ifCOUNT2:
 399             /* TODO: what is this supposed to be with bIgnoreErrVal? */
 400             rCount += pMat->Count(true, true);          // do count error values
 401         break;
 402         case ifPRODUCT:
 403         {
 404             ScMatrix::DoubleIterateResult aRes = pMat->Product(bTextAsZero, bIgnoreErrVal);
 405             fRes *= aRes.maAccumulator;
 406             rCount += aRes.mnCount;
 407         }
 408         break;
 409         case ifSUMSQ:
 410         {
 411             ScMatrix::KahanIterateResult aRes = pMat->SumSquare(bTextAsZero, bIgnoreErrVal);
 412             fRes += aRes.maAccumulator;
 413             rCount += aRes.mnCount;
 414         }
 415         break;
 416         default:
 417             ;
 418     }
 419 }
 420
 421 size_t ScInterpreter::GetRefListArrayMaxSize( short nParamCount )
 422 {
 423     size_t nSize = 0;
 424     if (IsInArrayContext())
 425     {
 426         for (short i=1; i <= nParamCount; ++i)
 427         {
 428             if (GetStackType(i) == svRefList)
 429             {
 430                 const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp - i]);
 431                 if (p && p->IsArrayResult() && p->GetRefList()->size() > nSize)
 432                     nSize = p->GetRefList()->size();
 433             }
 434         }
 435     }
 436     return nSize;
 437 }
 438
 439 static double lcl_IterResult( ScIterFunc eFunc, double fRes, sal_uLong nCount )
 440 {
 441     switch( eFunc )
 442     {
 443         case ifAVERAGE:
 444             fRes = sc::div( fRes, nCount);
 445         break;
 446         case ifCOUNT2:
 447         case ifCOUNT:
 448             fRes = nCount;
 449         break;
 450         case ifPRODUCT:
 451             if ( !nCount )
 452                 fRes = 0.0;
 453         break;
 454         default:
 455             ; // nothing
 456     }
 457     return fRes;
 458 }
 459
 460 void ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
 461 {
 462     short nParamCount = GetByte();
 463     const SCSIZE nMatRows = GetRefListArrayMaxSize( nParamCount);
 464     ScMatrixRef xResMat, xResCount;
 465     const double ResInitVal = (eFunc == ifPRODUCT) ? 1.0 : 0.0;
 466     KahanSum fRes = ResInitVal;
 467     double fVal = 0.0;
 468     sal_uLong nCount = 0;
 469     ScAddress aAdr;
 470     ScRange aRange;
 471     size_t nRefInList = 0;
 472     size_t nRefArrayPos = std::numeric_limits<size_t>::max();
 473     if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 474          ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
 475         nGlobalError = FormulaError::NONE;
 476     while (nParamCount-- > 0)
 477     {
 478         switch (GetStackType())
 479         {
 480             case svString:
 481             {
 482                 if( eFunc == ifCOUNT )
 483                 {
 484                     OUString aStr = PopString().getString();
 485                     if ( bTextAsZero )
 486                         nCount++;
 487                     else
 488                     {
 489                         // Only check if string can be converted to number, no
 490                         // error propagation.
 491                         FormulaError nErr = nGlobalError;
 492                         nGlobalError = FormulaError::NONE;
 493                         ConvertStringToValue( aStr );
 494                         if (nGlobalError == FormulaError::NONE)
 495                             ++nCount;
 496                         nGlobalError = nErr;
 497                     }
 498                 }
 499                 else
 500                 {
 501                     Pop();
 502                     switch ( eFunc )
 503                     {
 504                         case ifAVERAGE:
 505                         case ifSUM:
 506                         case ifSUMSQ:
 507                         case ifPRODUCT:
 508                         {
 509                             if ( bTextAsZero )
 510                             {
 511                                 nCount++;
 512                                 if ( eFunc == ifPRODUCT )
 513                                     fRes = 0.0;
 514                             }
 515                             else
 516                             {
 517                                 while (nParamCount-- > 0)
 518                                     PopError();
 519                                 SetError( FormulaError::NoValue );
 520                             }
 521                         }
 522                         break;
 523                         default:
 524                             nCount++;
 525                     }
 526                 }
 527             }
 528             break;
 529             case svDouble    :
 530                 fVal = GetDouble();
 531                 nCount++;
 532                 switch( eFunc )
 533                 {
 534                     case ifAVERAGE:
 535                     case ifSUM:     fRes += fVal; break;
 536                     case ifSUMSQ:   fRes += fVal * fVal; break;
 537                     case ifPRODUCT: fRes *= fVal; break;
 538                     default: ; // nothing
 539                 }
 540                 nFuncFmtType = SvNumFormatType::NUMBER;
 541                 break;
 542             case svExternalSingleRef:
 543             {
 544                 ScExternalRefCache::TokenRef pToken;
 545                 ScExternalRefCache::CellFormat aFmt;
 546                 PopExternalSingleRef(pToken, &aFmt);
 547                 if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 548                      ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
 549                 {
 550                     nGlobalError = FormulaError::NONE;
 551                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
 552                         ++nCount;
 553                     break;
 554                 }
 555
 556                 if (!pToken)
 557                     break;
 558
 559                 StackVar eType = pToken->GetType();
 560                 if (eFunc == ifCOUNT2)
 561                 {
 562                     if ( eType != svEmptyCell &&
 563                          ( ( pToken->GetOpCode() != ocSubTotal &&
 564                              pToken->GetOpCode() != ocAggregate ) ||
 565                            ( mnSubTotalFlags & SubtotalFlags::IgnoreNestedStAg ) ) )
 566                         nCount++;
 567                     if (nGlobalError != FormulaError::NONE)
 568                         nGlobalError = FormulaError::NONE;
 569                 }
 570                 else if (eType == svDouble)
 571                 {
 572                     nCount++;
 573                     fVal = pToken->GetDouble();
 574                     if (aFmt.mbIsSet)
 575                     {
 576                         nFuncFmtType = aFmt.mnType;
 577                         nFuncFmtIndex = aFmt.mnIndex;
 578                     }
 579                     switch( eFunc )
 580                     {
 581                         case ifAVERAGE:
 582                         case ifSUM:     fRes += fVal; break;
 583                         case ifSUMSQ:   fRes += fVal * fVal; break;
 584                         case ifPRODUCT: fRes *= fVal; break;
 585                         case ifCOUNT:
 586                             if ( nGlobalError != FormulaError::NONE )
 587                             {
 588                                 nGlobalError = FormulaError::NONE;
 589                                 nCount--;
 590                             }
 591                             break;
 592                         default: ; // nothing
 593                     }
 594                 }
 595                 else if (bTextAsZero && eType == svString)
 596                 {
 597                     nCount++;
 598                     if ( eFunc == ifPRODUCT )
 599                         fRes = 0.0;
 600                 }
 601             }
 602             break;
 603             case svSingleRef :
 604             {
 605                 PopSingleRef( aAdr );
 606                 if (nGlobalError == FormulaError::NoRef)
 607                 {
 608                     PushError( FormulaError::NoRef);
 609                     return;
 610                 }
 611
 612                 if ( ( ( mnSubTotalFlags & SubtotalFlags::IgnoreFiltered ) &&
 613                      mrDoc.RowFiltered( aAdr.Row(), aAdr.Tab() ) ) ||
 614                      ( ( mnSubTotalFlags & SubtotalFlags::IgnoreHidden ) &&
 615                        mrDoc.RowHidden( aAdr.Row(), aAdr.Tab() ) ) )
 616                 {
 617                     break;
 618                 }
 619                 if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 620                      ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
 621                 {
 622                     nGlobalError = FormulaError::NONE;
 623                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
 624                         ++nCount;
 625                     break;
 626                 }
 627                 ScRefCellValue aCell(mrDoc, aAdr);
 628                 if (!aCell.isEmpty())
 629                 {
 630                     if( eFunc == ifCOUNT2 )
 631                     {
 632                         CellType eCellType = aCell.getType();
 633                         if ( eCellType != CELLTYPE_NONE )
 634                             nCount++;
 635                         if ( nGlobalError != FormulaError::NONE )
 636                             nGlobalError = FormulaError::NONE;
 637                     }
 638                     else if (aCell.hasNumeric())
 639                     {
 640                         fVal = GetCellValue(aAdr, aCell);
 641                         if (nGlobalError != FormulaError::NONE)
 642                         {
 643                             if (eFunc == ifCOUNT || (mnSubTotalFlags & SubtotalFlags::IgnoreErrVal))
 644                                 nGlobalError = FormulaError::NONE;
 645                             break;
 646                         }
 647                         nCount++;
 648                         CurFmtToFuncFmt();
 649                         switch( eFunc )
 650                         {
 651                             case ifAVERAGE:
 652                             case ifSUM:     fRes += fVal; break;
 653                             case ifSUMSQ:   fRes += fVal * fVal; break;
 654                             case ifPRODUCT: fRes *= fVal; break;
 655                             default: ; // nothing
 656                         }
 657                     }
 658                     else if (bTextAsZero && aCell.hasString())
 659                     {
 660                         nCount++;
 661                         if ( eFunc == ifPRODUCT )
 662                             fRes = 0.0;
 663                     }
 664                 }
 665             }
 666             break;
 667             case svRefList :
 668             {
 669                 const ScRefListToken* p = dynamic_cast<const ScRefListToken*>(pStack[sp-1]);
 670                 if (p && p->IsArrayResult())
 671                 {
 672                     nRefArrayPos = nRefInList;
 673                     // The "one value to all references of an array" seems to
 674                     // be what Excel does if there are other types than just
 675                     // arrays of references.
 676                     if (!xResMat)
 677                     {
 678                         // Create and init all elements with current value.
 679                         assert(nMatRows > 0);
 680                         xResMat = GetNewMat( 1, nMatRows, true);
 681                         xResMat->FillDouble( fRes.get(), 0,0, 0,nMatRows-1);
 682                         if (eFunc != ifSUM)
 683                         {
 684                             xResCount = GetNewMat( 1, nMatRows, true);
 685                             xResCount->FillDouble( nCount, 0,0, 0,nMatRows-1);
 686                         }
 687                     }
 688                     else
 689                     {
 690                         // Current value and values from vector are operands
 691                         // for each vector position.
 692                         if (nCount && xResCount)
 693                         {
 694                             for (SCSIZE i=0; i < nMatRows; ++i)
 695                             {
 696                                 xResCount->PutDouble( xResCount->GetDouble(0,i) + nCount, 0,i);
 697                             }
 698                         }
 699                         if (fRes != ResInitVal)
 700                         {
 701                             for (SCSIZE i=0; i < nMatRows; ++i)
 702                             {
 703                                 double fVecRes = xResMat->GetDouble(0,i);
 704                                 if (eFunc == ifPRODUCT)
 705                                     fVecRes *= fRes.get();
 706                                 else
 707                                     fVecRes += fRes.get();
 708                                 xResMat->PutDouble( fVecRes, 0,i);
 709                             }
 710                         }
 711                     }
 712                     fRes = ResInitVal;
 713                     nCount = 0;
 714                 }
 715             }
 716             [[fallthrough]];
 717             case svDoubleRef :
 718             {
 719                 PopDoubleRef( aRange, nParamCount, nRefInList);
 720                 if (nGlobalError == FormulaError::NoRef)
 721                 {
 722                     PushError( FormulaError::NoRef);
 723                     return;
 724                 }
 725
 726                 if ( nGlobalError != FormulaError::NONE && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ||
 727                      ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
 728                 {
 729                     nGlobalError = FormulaError::NONE;
 730                     if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
 731                         ++nCount;
 732                     if ( eFunc == ifCOUNT2 || eFunc == ifCOUNT )
 733                         break;
 734                 }
 735                 if( eFunc == ifCOUNT2 )
 736                 {
 737                     ScCellIterator aIter( mrDoc, aRange, mnSubTotalFlags );
 738                     for (bool bHas = aIter.first(); bHas; bHas = aIter.next())
 739                     {
 740                         if ( !aIter.isEmpty() )
 741                         {
 742                             ++nCount;
 743                         }
 744                     }
 745
 746                     if ( nGlobalError != FormulaError::NONE )
 747                         nGlobalError = FormulaError::NONE;
 748                 }
 749                 else if (((eFunc == ifSUM && !bCalcAsShown) || eFunc == ifCOUNT )
 750                         && mnSubTotalFlags == SubtotalFlags::NONE)
 751                 {
 752                     // Use fast span set array method.
 753                     // ifSUM with bCalcAsShown has to use the slow bells and
 754                     // whistles ScValueIterator below.
 755                     sc::RangeColumnSpanSet aSet( aRange );
 756
 757                     if ( eFunc == ifSUM )
 758                     {
 759                         FuncSum aAction(mrContext);
 760                         aSet.executeColumnAction( mrDoc, aAction );
 761                         FormulaError nErr = aAction.getError();
 762                         if ( nErr != FormulaError::NONE )
 763                         {
 764                             PushError( nErr );
 765                             return;
 766                         }
 767                         fRes += aAction.getSum();
 768
 769                         // Get the number format of the last iterated cell.
 770                         nFuncFmtIndex = aAction.getNumberFormat();
 771                     }
 772                     else
 773                     {
 774                         FuncCount aAction(mrContext);
 775                         aSet.executeColumnAction(mrDoc, aAction);
 776                         nCount += aAction.getCount();
 777
 778                         // Get the number format of the last iterated cell.
 779                         nFuncFmtIndex = aAction.getNumberFormat();
 780                     }
 781
 782                     nFuncFmtType = mrContext.NFGetType(nFuncFmtIndex);
 783                 }
 784                 else
 785                 {
 786                     ScValueIterator aValIter( mrContext, aRange, mnSubTotalFlags, bTextAsZero );
 787                     FormulaError nErr = FormulaError::NONE;
 788                     if (aValIter.GetFirst(fVal, nErr))
 789                     {
 790                         // placed the loop on the inside for performance reasons:
 791                         aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex );
 792                         switch( eFunc )
 793                         {
 794                             case ifAVERAGE:
 795                             case ifSUM:
 796                                     if ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal )
 797                                     {
 798                                         do
 799                                         {
 800                                             if ( nErr == FormulaError::NONE )
 801                                             {
 802                                                 SetError(nErr);
 803                                                 fRes += fVal;
 804                                                 nCount++;
 805                                             }
 806                                         }
 807                                         while (aValIter.GetNext(fVal, nErr));
 808                                     }
 809                                     else
 810                                     {
 811                                         do
 812                                         {
 813                                             SetError(nErr);
 814                                             fRes += fVal;
 815                                             nCount++;
 816                                         }
 817                                         while (aValIter.GetNext(fVal, nErr));
 818                                     }
 819                                     break;
 820                             case ifSUMSQ:
 821                                     if ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal )
 822                                     {
 823                                         do
 824                                         {
 825                                             if ( nErr == FormulaError::NONE )
 826                                             {
 827                                                 SetError(nErr);
 828                                                 fRes += fVal * fVal;
 829                                                 nCount++;
 830                                             }
 831                                         }
 832                                         while (aValIter.GetNext(fVal, nErr));
 833                                     }
 834                                     else
 835                                     {
 836                                         do
 837                                         {
 838                                             SetError(nErr);
 839                                             fRes += fVal * fVal;
 840                                             nCount++;
 841                                         }
 842                                         while (aValIter.GetNext(fVal, nErr));
 843                                     }
 844                                     break;
 845                             case ifPRODUCT:
 846                                     do
 847                                     {
 848                                         if ( !( nErr != FormulaError::NONE && ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) ) )
 849                                         {
 850                                             SetError(nErr);
 851                                             fRes *= fVal;
 852                                             nCount++;
 853                                         }
 854                                     }
 855                                     while (aValIter.GetNext(fVal, nErr));
 856                                     break;
 857                             case ifCOUNT:
 858                                     do
 859                                     {
 860                                         if ( nErr == FormulaError::NONE )
 861                                             nCount++;
 862                                     }
 863                                     while (aValIter.GetNext(fVal, nErr));
 864                                     break;
 865                             default: ;  // nothing
 866                         }
 867                         SetError( nErr );
 868                     }
 869                 }
 870                 if (nRefArrayPos != std::numeric_limits<size_t>::max())
 871                 {
 872                     // Update vector element with current value.
 873                     if (xResCount)
 874                         xResCount->PutDouble( xResCount->GetDouble(0,nRefArrayPos) + nCount, 0,nRefArrayPos);
 875                     double fVecRes = xResMat->GetDouble(0,nRefArrayPos);
 876                     if (eFunc == ifPRODUCT)
 877                         fVecRes *= fRes.get();
 878                     else
 879                         fVecRes += fRes.get();
 880                     xResMat->PutDouble( fVecRes, 0,nRefArrayPos);
 881                     // Reset.
 882                     fRes = ResInitVal;
 883                     nCount = 0;
 884                     nRefArrayPos = std::numeric_limits<size_t>::max();
 885                 }
 886             }
 887             break;
 888             case svExternalDoubleRef:
 889             {
 890                 ScMatrixRef pMat;
 891                 PopExternalDoubleRef(pMat);
 892                 if ( nGlobalError != FormulaError::NONE && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
 893                     break;
 894
 895                 IterateMatrix( pMat, eFunc, bTextAsZero, mnSubTotalFlags, nCount, nFuncFmtType, fRes );
 896             }
 897             break;
 898             case svMatrix :
 899             {
 900                 ScMatrixRef pMat = PopMatrix();
 901
 902                 IterateMatrix( pMat, eFunc, bTextAsZero, mnSubTotalFlags, nCount, nFuncFmtType, fRes );
 903             }
 904             break;
 905             case svError:
 906             {
 907                 PopError();
 908                 if ( eFunc == ifCOUNT || ( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
 909                 {
 910                     nGlobalError = FormulaError::NONE;
 911                 }
 912                 else if ( eFunc == ifCOUNT2 && !( mnSubTotalFlags & SubtotalFlags::IgnoreErrVal ) )
 913                 {
 914                     nCount++;
 915                     nGlobalError = FormulaError::NONE;
 916                 }
 917             }
 918             break;
 919             default :
 920                 while (nParamCount-- > 0)
 921                     PopError();
 922                 SetError(FormulaError::IllegalParameter);
 923         }
 924     }
 925
 926     // A boolean return type makes no sense on sums et al.
 927     // Counts are always numbers.
 928     if( nFuncFmtType == SvNumFormatType::LOGICAL || eFunc == ifCOUNT || eFunc == ifCOUNT2 )
 929         nFuncFmtType = SvNumFormatType::NUMBER;
 930
 931     if (xResMat)
 932     {
 933         // Include value of last non-references-array type and calculate final result.
 934         for (SCSIZE i=0; i < nMatRows; ++i)
 935         {
 936             sal_uLong nVecCount = (xResCount ? nCount + xResCount->GetDouble(0,i) : nCount);
 937             double fVecRes = xResMat->GetDouble(0,i);
 938             if (eFunc == ifPRODUCT)
 939                 fVecRes *= fRes.get();
 940             else
 941                 fVecRes += fRes.get();
 942             fVecRes = lcl_IterResult( eFunc, fVecRes, nVecCount);
 943             xResMat->PutDouble( fVecRes, 0,i);
 944         }
 945         PushMatrix( xResMat);
 946     }
 947     else
 948     {
 949         PushDouble( lcl_IterResult( eFunc, fRes.get(), nCount));
 950     }
 951 }
 952
 953 void ScInterpreter::ScSumSQ()
 954 {
 955     IterateParameters( ifSUMSQ );
 956 }
 957
 958 void ScInterpreter::ScSum()
 959 {
 960     IterateParameters( ifSUM );
 961 }
 962
 963 void ScInterpreter::ScProduct()
 964 {
 965     IterateParameters( ifPRODUCT );
 966 }
 967
 968 void ScInterpreter::ScAverage( bool bTextAsZero )
 969 {
 970     IterateParameters( ifAVERAGE, bTextAsZero );
 971 }
 972
 973 void ScInterpreter::ScCount()
 974 {
 975     IterateParameters( ifCOUNT );
 976 }
 977
 978 void ScInterpreter::ScCount2()
 979 {
 980     IterateParameters( ifCOUNT2 );
 981 }
 982
 983 /**
 984  * The purpose of RAWSUBTRACT() is exactly to not apply any error correction, approximation etc.
 985  * But use the "raw" IEEE 754 double subtraction.
 986  * So no Kahan summation
 987  */
 988 void ScInterpreter::ScRawSubtract()
 989 {
 990     short nParamCount = GetByte();
 991     if (!MustHaveParamCountMin( nParamCount, 2))
 992         return;
 993
 994     // Reverse stack to process arguments from left to right.
 995     ReverseStack( nParamCount);
 996     // Obtain the minuend.
 997     double fRes = GetDouble();
 998
 999     while (nGlobalError == FormulaError::NONE && --nParamCount > 0)
1000     {
1001         // Simple single values without matrix support.
1002         fRes -= GetDouble();
1003     }
1004     while (nParamCount-- > 0)
1005         PopError();
1006
1007     PushDouble( fRes);
1008 }
1009
1010 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */