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
  21 #include "interpre.hxx"
  22 #include "columnspanset.hxx"
  23 #include "column.hxx"
  24 #include "document.hxx"
  25 #include "cellvalue.hxx"
  26 #include "dociter.hxx"
  27 #include "mtvcellfunc.hxx"
  28
  29 #include "formula/token.hxx"
  30
  31 using namespace formula;
  32
  33 double const fHalfMachEps = 0.5 * ::std::numeric_limits<double>::epsilon();
  34
  35 // The idea how this group of gamma functions is calculated, is
  36 // based on the Cephes library
  37 // online http://www.moshier.net/#Cephes [called 2008-02]
  38
  39 /** You must ensure fA>0.0 && fX>0.0
  40     valid results only if fX > fA+1.0
  41     uses continued fraction with odd items */
  42 double ScInterpreter::GetGammaContFraction( double fA, double fX )
  43 {
  44
  45     double const fBigInv = ::std::numeric_limits<double>::epsilon();
  46     double const fBig = 1.0/fBigInv;
  47     double fCount = 0.0;
  48     double fNum = 0.0;  // dummy value
  49     double fY = 1.0 - fA;
  50     double fDenom = fX + 2.0-fA;
  51     double fPk = 0.0;   // dummy value
  52     double fPkm1 = fX + 1.0;
  53     double fPkm2 = 1.0;
  54     double fQk = 1.0;   // dummy value
  55     double fQkm1 = fDenom * fX;
  56     double fQkm2 = fX;
  57     double fApprox = fPkm1/fQkm1;
  58     bool bFinished = false;
  59     double fR = 0.0;    // dummy value
  60     do
  61     {
  62         fCount = fCount +1.0;
  63         fY = fY+ 1.0;
  64         fNum = fY * fCount;
  65         fDenom = fDenom +2.0;
  66         fPk = fPkm1 * fDenom  -  fPkm2 * fNum;
  67         fQk = fQkm1 * fDenom  -  fQkm2 * fNum;
  68         if (fQk != 0.0)
  69         {
  70             fR = fPk/fQk;
  71             bFinished = (fabs( (fApprox - fR)/fR ) <= fHalfMachEps);
  72             fApprox = fR;
  73         }
  74         fPkm2 = fPkm1;
  75         fPkm1 = fPk;
  76         fQkm2 = fQkm1;
  77         fQkm1 = fQk;
  78         if (fabs(fPk) > fBig)
  79         {
  80             // reduce a fraction does not change the value
  81             fPkm2 = fPkm2 * fBigInv;
  82             fPkm1 = fPkm1 * fBigInv;
  83             fQkm2 = fQkm2 * fBigInv;
  84             fQkm1 = fQkm1 * fBigInv;
  85         }
  86     } while (!bFinished && fCount<10000);
  87     // most iterations, if fX==fAlpha+1.0; approx sqrt(fAlpha) iterations then
  88     if (!bFinished)
  89     {
  90         SetError(errNoConvergence);
  91     }
  92     return fApprox;
  93 }
  94
  95 /** You must ensure fA>0.0 && fX>0.0
  96     valid results only if fX <= fA+1.0
  97     uses power series */
  98 double ScInterpreter::GetGammaSeries( double fA, double fX )
  99 {
 100     double fDenomfactor = fA;
 101     double fSummand = 1.0/fA;
 102     double fSum = fSummand;
 103     int nCount=1;
 104     do
 105     {
 106         fDenomfactor = fDenomfactor + 1.0;
 107         fSummand = fSummand * fX/fDenomfactor;
 108         fSum = fSum + fSummand;
 109         nCount = nCount+1;
 110     } while ( fSummand/fSum > fHalfMachEps && nCount<=10000);
 111     // large amount of iterations will be carried out for huge fAlpha, even
 112     // if fX <= fAlpha+1.0
 113     if (nCount>10000)
 114     {
 115         SetError(errNoConvergence);
 116     }
 117     return fSum;
 118 }
 119
 120 /** You must ensure fA>0.0 && fX>0.0) */
 121 double ScInterpreter::GetLowRegIGamma( double fA, double fX )
 122 {
 123     double fLnFactor = fA * log(fX) - fX - GetLogGamma(fA);
 124     double fFactor = exp(fLnFactor);    // Do we need more accuracy than exp(ln()) has?
 125     if (fX>fA+1.0)  // includes fX>1.0; 1-GetUpRegIGamma, continued fraction
 126         return 1.0 - fFactor * GetGammaContFraction(fA,fX);
 127     else            // fX<=1.0 || fX<=fA+1.0, series
 128         return fFactor * GetGammaSeries(fA,fX);
 129 }
 130
 131 /** You must ensure fA>0.0 && fX>0.0) */
 132 double ScInterpreter::GetUpRegIGamma( double fA, double fX )
 133 {
 134
 135     double fLnFactor= fA*log(fX)-fX-GetLogGamma(fA);
 136     double fFactor = exp(fLnFactor); //Do I need more accuracy than exp(ln()) has?;
 137     if (fX>fA+1.0) // includes fX>1.0
 138             return fFactor * GetGammaContFraction(fA,fX);
 139     else //fX<=1 || fX<=fA+1, 1-GetLowRegIGamma, series
 140             return 1.0 -fFactor * GetGammaSeries(fA,fX);
 141 }
 142
 143 /** Gamma distribution, probability density function.
 144     fLambda is "scale" parameter
 145     You must ensure fAlpha>0.0 and fLambda>0.0 */
 146 double ScInterpreter::GetGammaDistPDF( double fX, double fAlpha, double fLambda )
 147 {
 148     if (fX < 0.0)
 149         return 0.0;     // see ODFF
 150     else if (fX == 0)
 151         // in this case 0^0 isn't zero
 152     {
 153         if (fAlpha < 1.0)
 154         {
 155             SetError(errDivisionByZero);  // should be #DIV/0
 156             return HUGE_VAL;
 157         }
 158         else if (fAlpha == 1)
 159         {
 160             return (1.0 / fLambda);
 161         }
 162         else
 163         {
 164             return 0.0;
 165         }
 166     }
 167     else
 168     {
 169         double fXr = fX / fLambda;
 170         // use exp(ln()) only for large arguments because of less accuracy
 171         if (fXr > 1.0)
 172         {
 173             const double fLogDblMax = log( ::std::numeric_limits<double>::max());
 174             if (log(fXr) * (fAlpha-1.0) < fLogDblMax && fAlpha < fMaxGammaArgument)
 175             {
 176                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / GetGamma(fAlpha);
 177             }
 178             else
 179             {
 180                 return exp( (fAlpha-1.0) * log(fXr) - fXr - log(fLambda) - GetLogGamma(fAlpha));
 181             }
 182         }
 183         else    // fXr near to zero
 184         {
 185             if (fAlpha<fMaxGammaArgument)
 186             {
 187                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / GetGamma(fAlpha);
 188             }
 189             else
 190             {
 191                 return pow( fXr, fAlpha-1.0) * exp(-fXr) / fLambda / exp( GetLogGamma(fAlpha));
 192             }
 193         }
 194     }
 195 }
 196
 197 /** Gamma distribution, cumulative distribution function.
 198     fLambda is "scale" parameter
 199     You must ensure fAlpha>0.0 and fLambda>0.0 */
 200 double ScInterpreter::GetGammaDist( double fX, double fAlpha, double fLambda )
 201 {
 202     if (fX <= 0.0)
 203         return 0.0;
 204     else
 205         return GetLowRegIGamma( fAlpha, fX / fLambda);
 206 }
 207
 208 namespace {
 209
 210 class NumericCellAccumulator
 211 {
 212     double mfSum;
 213 public:
 214     NumericCellAccumulator() : mfSum(0.0) {}
 215
 216     void operator() (size_t, double fVal)
 217     {
 218         mfSum += fVal;
 219     }
 220
 221     void operator() (size_t, const ScFormulaCell* pCell)
 222     {
 223         ScFormulaCell& rCell = const_cast<ScFormulaCell&>(*pCell);
 224         if (rCell.IsValue())
 225             mfSum += rCell.GetValue();
 226     }
 227
 228     double getSum() const { return mfSum; }
 229 };
 230
 231 class NumericCellCounter
 232 {
 233     size_t mnCount;
 234 public:
 235     NumericCellCounter() : mnCount(0) {}
 236
 237     void operator() (const sc::CellStoreType::value_type& rNode, size_t nOffset, size_t nDataSize)
 238     {
 239         switch (rNode.type)
 240         {
 241             case sc::element_type_numeric:
 242                 mnCount += nDataSize;
 243             break;
 244             case sc::element_type_formula:
 245             {
 246                 sc::formula_block::const_iterator it = sc::formula_block::begin(*rNode.data);
 247                 std::advance(it, nOffset);
 248                 sc::formula_block::const_iterator itEnd = it;
 249                 std::advance(itEnd, nDataSize);
 250                 for (; it != itEnd; ++it)
 251                 {
 252                     ScFormulaCell& rCell = const_cast<ScFormulaCell&>(**it);
 253                     if (rCell.IsValueNoError())
 254                         ++mnCount;
 255                 }
 256             }
 257             break;
 258             default:
 259                 ;
 260         }
 261     }
 262
 263     size_t getCount() const { return mnCount; }
 264 };
 265
 266 class FuncCount : public sc::ColumnSpanSet::ColumnAction
 267 {
 268     sc::ColumnBlockConstPosition maPos;
 269     ScColumn* mpCol;
 270     size_t mnCount;
 271     sal_uInt32 mnNumFmt;
 272
 273 public:
 274     FuncCount() : mpCol(0), mnCount(0), mnNumFmt(0) {}
 275
 276     virtual void startColumn(ScColumn* pCol)
 277     {
 278         mpCol = pCol;
 279         mpCol->InitBlockPosition(maPos);
 280     }
 281
 282     virtual void execute(SCROW nRow1, SCROW nRow2, bool bVal)
 283     {
 284         if (!bVal)
 285             return;
 286
 287         NumericCellCounter aFunc;
 288         maPos.miCellPos = sc::ParseBlock(maPos.miCellPos, mpCol->GetCellStore(), aFunc, nRow1, nRow2);
 289         mnCount += aFunc.getCount();
 290         mnNumFmt = mpCol->GetNumberFormat(nRow2);
 291     };
 292
 293     size_t getCount() const { return mnCount; }
 294     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
 295 };
 296
 297 class FuncSum : public sc::ColumnSpanSet::ColumnAction
 298 {
 299     sc::ColumnBlockConstPosition maPos;
 300     ScColumn* mpCol;
 301     double mfSum;
 302     sal_uInt32 mnNumFmt;
 303
 304 public:
 305     FuncSum() : mpCol(0), mfSum(0.0), mnNumFmt(0) {}
 306
 307     virtual void startColumn(ScColumn* pCol)
 308     {
 309         mpCol = pCol;
 310         mpCol->InitBlockPosition(maPos);
 311     }
 312
 313     virtual void execute(SCROW nRow1, SCROW nRow2, bool bVal)
 314     {
 315         if (!bVal)
 316             return;
 317
 318         NumericCellAccumulator aFunc;
 319         maPos.miCellPos = sc::ParseFormulaNumeric(maPos.miCellPos, mpCol->GetCellStore(), nRow1, nRow2, aFunc);
 320         mfSum += aFunc.getSum();
 321         mnNumFmt = mpCol->GetNumberFormat(nRow2);
 322     };
 323
 324     double getSum() const { return mfSum; }
 325     sal_uInt32 getNumberFormat() const { return mnNumFmt; }
 326 };
 327
 328 void IterateMatrix(
 329     const ScMatrixRef& pMat, ScIterFunc eFunc, bool bTextAsZero,
 330     sal_uLong& rCount, short& rFuncFmtType, double& fRes, double& fMem, bool& bNull)
 331 {
 332     if (!pMat)
 333         return;
 334
 335     rFuncFmtType = NUMBERFORMAT_NUMBER;
 336     switch (eFunc)
 337     {
 338         case ifAVERAGE:
 339         case ifSUM:
 340         {
 341             ScMatrix::IterateResult aRes = pMat->Sum(bTextAsZero);
 342             if (bNull)
 343             {
 344                 bNull = false;
 345                 fMem = aRes.mfFirst;
 346                 fRes += aRes.mfRest;
 347             }
 348             else
 349                 fRes += aRes.mfFirst + aRes.mfRest;
 350             rCount += aRes.mnCount;
 351         }
 352         break;
 353         case ifCOUNT:
 354             rCount += pMat->Count(bTextAsZero);
 355         break;
 356         case ifCOUNT2:
 357             rCount += pMat->Count(true);
 358         break;
 359         case ifPRODUCT:
 360         {
 361             ScMatrix::IterateResult aRes = pMat->Product(bTextAsZero);
 362             fRes *= aRes.mfRest;
 363             rCount += aRes.mnCount;
 364         }
 365         break;
 366         case ifSUMSQ:
 367         {
 368             ScMatrix::IterateResult aRes = pMat->SumSquare(bTextAsZero);
 369             fRes += aRes.mfRest;
 370             rCount += aRes.mnCount;
 371         }
 372         break;
 373         default:
 374             ;
 375     }
 376 }
 377
 378 }
 379
 380 double ScInterpreter::IterateParameters( ScIterFunc eFunc, bool bTextAsZero )
 381 {
 382     short nParamCount = GetByte();
 383     double fRes = ( eFunc == ifPRODUCT ) ? 1.0 : 0.0;
 384     double fVal = 0.0;
 385     double fMem = 0.0; // first numeric value.
 386     bool bNull = true;
 387     sal_uLong nCount = 0;
 388     ScAddress aAdr;
 389     ScRange aRange;
 390     size_t nRefInList = 0;
 391     if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ) )
 392         nGlobalError = 0;
 393     while (nParamCount-- > 0)
 394     {
 395         switch (GetStackType())
 396         {
 397             case svString:
 398             {
 399                 if( eFunc == ifCOUNT )
 400                 {
 401                     OUString aStr = PopString().getString();
 402                     sal_uInt32 nFIndex = 0;                 // damit default Land/Spr.
 403                     if ( bTextAsZero || pFormatter->IsNumberFormat(aStr, nFIndex, fVal))
 404                         nCount++;
 405                 }
 406                 else
 407                 {
 408                     switch ( eFunc )
 409                     {
 410                         case ifAVERAGE:
 411                         case ifSUM:
 412                         case ifSUMSQ:
 413                         case ifPRODUCT:
 414                         {
 415                             if ( bTextAsZero )
 416                             {
 417                                 Pop();
 418                                 nCount++;
 419                                 if ( eFunc == ifPRODUCT )
 420                                     fRes = 0.0;
 421                             }
 422                             else
 423                             {
 424                                 while (nParamCount-- > 0)
 425                                     Pop();
 426                                 SetError( errNoValue );
 427                             }
 428                         }
 429                         break;
 430                         default:
 431                             Pop();
 432                             nCount++;
 433                     }
 434                 }
 435             }
 436             break;
 437             case svDouble    :
 438                 fVal = GetDouble();
 439                 nCount++;
 440                 switch( eFunc )
 441                 {
 442                     case ifAVERAGE:
 443                     case ifSUM:
 444                         if ( bNull && fVal != 0.0 )
 445                         {
 446                             bNull = false;
 447                             fMem = fVal;
 448                         }
 449                         else
 450                             fRes += fVal;
 451                         break;
 452                     case ifSUMSQ:   fRes += fVal * fVal; break;
 453                     case ifPRODUCT: fRes *= fVal; break;
 454                     default: ; // nothing
 455                 }
 456                 nFuncFmtType = NUMBERFORMAT_NUMBER;
 457                 break;
 458             case svExternalSingleRef:
 459             {
 460                 ScExternalRefCache::TokenRef pToken;
 461                 ScExternalRefCache::CellFormat aFmt;
 462                 PopExternalSingleRef(pToken, &aFmt);
 463                 if (nGlobalError && (eFunc == ifCOUNT2 || eFunc == ifCOUNT))
 464                 {
 465                     nGlobalError = 0;
 466                     if ( eFunc == ifCOUNT2 )
 467                         ++nCount;
 468                     break;
 469                 }
 470
 471                 if (!pToken)
 472                     break;
 473
 474                 StackVar eType = pToken->GetType();
 475                 if (eFunc == ifCOUNT2)
 476                 {
 477                     if (eType != formula::svEmptyCell)
 478                         nCount++;
 479                     if (nGlobalError)
 480                         nGlobalError = 0;
 481                 }
 482                 else if (eType == formula::svDouble)
 483                 {
 484                     nCount++;
 485                     fVal = pToken->GetDouble();
 486                     if (aFmt.mbIsSet)
 487                     {
 488                         nFuncFmtType = aFmt.mnType;
 489                         nFuncFmtIndex = aFmt.mnIndex;
 490                     }
 491                     switch( eFunc )
 492                     {
 493                         case ifAVERAGE:
 494                         case ifSUM:
 495                             if ( bNull && fVal != 0.0 )
 496                             {
 497                                 bNull = false;
 498                                 fMem = fVal;
 499                             }
 500                             else
 501                                 fRes += fVal;
 502                             break;
 503                         case ifSUMSQ:   fRes += fVal * fVal; break;
 504                         case ifPRODUCT: fRes *= fVal; break;
 505                         case ifCOUNT:
 506                             if ( nGlobalError )
 507                             {
 508                                 nGlobalError = 0;
 509                                 nCount--;
 510                             }
 511                             break;
 512                         default: ; // nothing
 513                     }
 514                 }
 515                 else if (bTextAsZero && eType == formula::svString)
 516                 {
 517                     nCount++;
 518                     if ( eFunc == ifPRODUCT )
 519                         fRes = 0.0;
 520                 }
 521             }
 522             break;
 523             case svSingleRef :
 524             {
 525                 PopSingleRef( aAdr );
 526                 if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ) )
 527                 {
 528                     nGlobalError = 0;
 529                     if ( eFunc == ifCOUNT2 )
 530                         ++nCount;
 531                     break;
 532                 }
 533                 if (glSubTotal && pDok->RowFiltered( aAdr.Row(), aAdr.Tab()))
 534                 {
 535                     break;
 536                 }
 537                 ScRefCellValue aCell;
 538                 aCell.assign(*pDok, aAdr);
 539                 if (!aCell.isEmpty())
 540                 {
 541                     if( eFunc == ifCOUNT2 )
 542                     {
 543                         CellType eCellType = aCell.meType;
 544                         if (eCellType != CELLTYPE_NONE)
 545                             nCount++;
 546                         if ( nGlobalError )
 547                             nGlobalError = 0;
 548                     }
 549                     else if (aCell.hasNumeric())
 550                     {
 551                         nCount++;
 552                         fVal = GetCellValue(aAdr, aCell);
 553                         CurFmtToFuncFmt();
 554                         switch( eFunc )
 555                         {
 556                             case ifAVERAGE:
 557                             case ifSUM:
 558                                 if ( bNull && fVal != 0.0 )
 559                                 {
 560                                     bNull = false;
 561                                     fMem = fVal;
 562                                 }
 563                                 else
 564                                     fRes += fVal;
 565                                 break;
 566                             case ifSUMSQ:   fRes += fVal * fVal; break;
 567                             case ifPRODUCT: fRes *= fVal; break;
 568                             case ifCOUNT:
 569                                 if ( nGlobalError )
 570                                 {
 571                                     nGlobalError = 0;
 572                                     nCount--;
 573                                 }
 574                                 break;
 575                             default: ; // nothing
 576                         }
 577                     }
 578                     else if (bTextAsZero && aCell.hasString())
 579                     {
 580                         nCount++;
 581                         if ( eFunc == ifPRODUCT )
 582                             fRes = 0.0;
 583                     }
 584                 }
 585             }
 586             break;
 587             case svDoubleRef :
 588             case svRefList :
 589             {
 590                 PopDoubleRef( aRange, nParamCount, nRefInList);
 591                 if ( nGlobalError && ( eFunc == ifCOUNT2 || eFunc == ifCOUNT ) )
 592                 {
 593                     nGlobalError = 0;
 594                     if ( eFunc == ifCOUNT2 )
 595                         ++nCount;
 596                     break;
 597                 }
 598                 if( eFunc == ifCOUNT2 )
 599                 {
 600                     ScCellIterator aIter( pDok, aRange, glSubTotal );
 601                     for (bool bHas = aIter.first(); bHas; bHas = aIter.next())
 602                     {
 603                         if (!aIter.hasEmptyData())
 604                             ++nCount;
 605                     }
 606
 607                     if ( nGlobalError )
 608                         nGlobalError = 0;
 609                 }
 610                 else
 611                 {
 612                     ScValueIterator aValIter( pDok, aRange, glSubTotal, bTextAsZero );
 613                     sal_uInt16 nErr = 0;
 614                     if (aValIter.GetFirst(fVal, nErr))
 615                     {
 616                         // placed the loop on the inside for performance reasons:
 617                         aValIter.GetCurNumFmtInfo( nFuncFmtType, nFuncFmtIndex );
 618                         switch( eFunc )
 619                         {
 620                             case ifAVERAGE:
 621                             case ifSUM:
 622                                     do
 623                                     {
 624                                         SetError(nErr);
 625                                         if ( bNull && fVal != 0.0 )
 626                                         {
 627                                             bNull = false;
 628                                             fMem = fVal;
 629                                         }
 630                                         else
 631                                             fRes += fVal;
 632                                         nCount++;
 633                                     }
 634                                     while (aValIter.GetNext(fVal, nErr));
 635                                     break;
 636                             case ifSUMSQ:
 637                                     do
 638                                     {
 639                                         SetError(nErr);
 640                                         fRes += fVal * fVal;
 641                                         nCount++;
 642                                     }
 643                                     while (aValIter.GetNext(fVal, nErr));
 644                                     break;
 645                             case ifPRODUCT:
 646                                     do
 647                                     {
 648                                         SetError(nErr);
 649                                         fRes *= fVal;
 650                                         nCount++;
 651                                     }
 652                                     while (aValIter.GetNext(fVal, nErr));
 653                                     break;
 654                             case ifCOUNT:
 655                                     do
 656                                     {
 657                                         if ( !nErr )
 658                                             nCount++;
 659                                     }
 660                                     while (aValIter.GetNext(fVal, nErr));
 661                                     break;
 662                             default: ;  // nothing
 663                         }
 664                         SetError( nErr );
 665                     }
 666                 }
 667             }
 668             break;
 669             case svExternalDoubleRef:
 670             {
 671                 ScMatrixRef pMat;
 672                 PopExternalDoubleRef(pMat);
 673                 if (nGlobalError)
 674                     break;
 675
 676                 IterateMatrix(pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem, bNull);
 677             }
 678             break;
 679             case svMatrix :
 680             {
 681                 ScMatrixRef pMat = PopMatrix();
 682                 IterateMatrix(pMat, eFunc, bTextAsZero, nCount, nFuncFmtType, fRes, fMem, bNull);
 683             }
 684             break;
 685             case svError:
 686             {
 687                 PopError();
 688                 if ( eFunc == ifCOUNT )
 689                 {
 690                     nGlobalError = 0;
 691                 }
 692                 else if ( eFunc == ifCOUNT2 )
 693                 {
 694                     nCount++;
 695                     nGlobalError = 0;
 696                 }
 697             }
 698             break;
 699             default :
 700                 while (nParamCount-- > 0)
 701                     PopError();
 702                 SetError(errIllegalParameter);
 703         }
 704     }
 705     switch( eFunc )
 706     {
 707         case ifSUM:     fRes = ::rtl::math::approxAdd( fRes, fMem ); break;
 708         case ifAVERAGE: fRes = div(::rtl::math::approxAdd( fRes, fMem ), nCount); break;
 709         case ifCOUNT2:
 710         case ifCOUNT:   fRes  = nCount; break;
 711         case ifPRODUCT: if ( !nCount ) fRes = 0.0; break;
 712         default: ; // nothing
 713     }
 714     // Bei Summen etc. macht ein bool-Ergebnis keinen Sinn
 715     // und Anzahl ist immer Number (#38345#)
 716     if( eFunc == ifCOUNT || nFuncFmtType == NUMBERFORMAT_LOGICAL )
 717         nFuncFmtType = NUMBERFORMAT_NUMBER;
 718     return fRes;
 719 }
 720
 721
 722 void ScInterpreter::ScSumSQ()
 723 {
 724     PushDouble( IterateParameters( ifSUMSQ ) );
 725 }
 726
 727
 728 void ScInterpreter::ScSum()
 729 {
 730     short nParamCount = GetByte();
 731     double fRes = 0.0;
 732     double fVal = 0.0;
 733     ScAddress aAdr;
 734     ScRange aRange;
 735     size_t nRefInList = 0;
 736     while (nParamCount-- > 0)
 737     {
 738         switch (GetStackType())
 739         {
 740             case svString:
 741             {
 742                 while (nParamCount-- > 0)
 743                     Pop();
 744                 SetError( errNoValue );
 745             }
 746             break;
 747             case svDouble    :
 748                 fVal = GetDouble();
 749                 fRes += fVal;
 750                 nFuncFmtType = NUMBERFORMAT_NUMBER;
 751                 break;
 752             case svExternalSingleRef:
 753             {
 754                 ScExternalRefCache::TokenRef pToken;
 755                 ScExternalRefCache::CellFormat aFmt;
 756                 PopExternalSingleRef(pToken, &aFmt);
 757
 758                 if (!pToken)
 759                     break;
 760
 761                 StackVar eType = pToken->GetType();
 762                 if (eType == formula::svDouble)
 763                 {
 764                     fVal = pToken->GetDouble();
 765                     if (aFmt.mbIsSet)
 766                     {
 767                         nFuncFmtType = aFmt.mnType;
 768                         nFuncFmtIndex = aFmt.mnIndex;
 769                     }
 770
 771                     fRes += fVal;
 772                 }
 773             }
 774             break;
 775             case svSingleRef :
 776             {
 777                 PopSingleRef( aAdr );
 778
 779                 if (glSubTotal && pDok->RowFiltered( aAdr.Row(), aAdr.Tab()))
 780                 {
 781                     break;
 782                 }
 783                 ScRefCellValue aCell;
 784                 aCell.assign(*pDok, aAdr);
 785                 if (!aCell.isEmpty())
 786                 {
 787                     if (aCell.hasNumeric())
 788                     {
 789                         fVal = GetCellValue(aAdr, aCell);
 790                         CurFmtToFuncFmt();
 791                         fRes += fVal;
 792                     }
 793                 }
 794             }
 795             break;
 796             case svDoubleRef :
 797             case svRefList :
 798             {
 799                 PopDoubleRef( aRange, nParamCount, nRefInList);
 800
 801                 sc::ColumnSpanSet aSet(false);
 802                 aSet.set(aRange, true);
 803                 if (glSubTotal)
 804                     // Skip all filtered rows and subtotal formula cells.
 805                     pDok->MarkSubTotalCells(aSet, aRange, false);
 806
 807                 FuncSum aAction;
 808                 aSet.executeColumnAction(*pDok, aAction);
 809                 fRes += aAction.getSum();
 810
 811                 // Get the number format of the last iterated cell.
 812                 nFuncFmtIndex = aAction.getNumberFormat();
 813                 nFuncFmtType = pDok->GetFormatTable()->GetType(nFuncFmtIndex);
 814             }
 815             break;
 816             case svExternalDoubleRef:
 817             {
 818                 ScMatrixRef pMat;
 819                 PopExternalDoubleRef(pMat);
 820                 if (nGlobalError)
 821                     break;
 822
 823                 sal_uLong nCount = 0;
 824                 double fMem = 0.0;
 825                 bool bNull = true;
 826                 IterateMatrix(pMat, ifSUM, false, nCount, nFuncFmtType, fRes, fMem, bNull);
 827                 fRes += fMem;
 828             }
 829             break;
 830             case svMatrix :
 831             {
 832                 ScMatrixRef pMat = PopMatrix();
 833                 sal_uLong nCount = 0;
 834                 double fMem = 0.0;
 835                 bool bNull = true;
 836                 IterateMatrix(pMat, ifSUM, false, nCount, nFuncFmtType, fRes, fMem, bNull);
 837                 fRes += fMem;
 838             }
 839             break;
 840             case svError:
 841             {
 842                 PopError();
 843             }
 844             break;
 845             default :
 846                 while (nParamCount-- > 0)
 847                     PopError();
 848                 SetError(errIllegalParameter);
 849         }
 850     }
 851
 852     if (nFuncFmtType == NUMBERFORMAT_LOGICAL)
 853         nFuncFmtType = NUMBERFORMAT_NUMBER;
 854
 855     PushDouble(fRes);
 856 }
 857
 858
 859 void ScInterpreter::ScProduct()
 860 {
 861     PushDouble( IterateParameters( ifPRODUCT ) );
 862 }
 863
 864
 865 void ScInterpreter::ScAverage( bool bTextAsZero )
 866 {
 867     PushDouble( IterateParameters( ifAVERAGE, bTextAsZero ) );
 868 }
 869
 870 void ScInterpreter::ScCount()
 871 {
 872     short nParamCount = GetByte();
 873     double fVal = 0.0;
 874     sal_uLong nCount = 0;
 875     ScAddress aAdr;
 876     ScRange aRange;
 877     size_t nRefInList = 0;
 878     if (nGlobalError)
 879         nGlobalError = 0;
 880
 881     while (nParamCount-- > 0)
 882     {
 883         switch (GetRawStackType())
 884         {
 885             case svString:
 886             {
 887                 OUString aStr = PopString().getString();
 888                 sal_uInt32 nFIndex = 0;                 // damit default Land/Spr.
 889                 if (pFormatter->IsNumberFormat(aStr, nFIndex, fVal))
 890                     nCount++;
 891             }
 892             break;
 893             case svDouble    :
 894                 nCount++;
 895                 nFuncFmtType = NUMBERFORMAT_NUMBER;
 896                 break;
 897             case svExternalSingleRef:
 898             {
 899                 ScExternalRefCache::TokenRef pToken;
 900                 ScExternalRefCache::CellFormat aFmt;
 901                 PopExternalSingleRef(pToken, &aFmt);
 902                 if (nGlobalError)
 903                 {
 904                     nGlobalError = 0;
 905                     break;
 906                 }
 907
 908                 if (!pToken)
 909                     break;
 910
 911                 StackVar eType = pToken->GetType();
 912                 if (eType == formula::svDouble)
 913                 {
 914                     nCount++;
 915                     if (aFmt.mbIsSet)
 916                     {
 917                         nFuncFmtType = aFmt.mnType;
 918                         nFuncFmtIndex = aFmt.mnIndex;
 919                     }
 920
 921                     if (nGlobalError)
 922                     {
 923                         nGlobalError = 0;
 924                         nCount--;
 925                     }
 926                 }
 927             }
 928             break;
 929             case svSingleRef :
 930             {
 931                 PopSingleRef( aAdr );
 932                 if (nGlobalError)
 933                 {
 934                     nGlobalError = 0;
 935                     break;
 936                 }
 937                 if (glSubTotal && pDok->RowFiltered( aAdr.Row(), aAdr.Tab()))
 938                 {
 939                     break;
 940                 }
 941                 ScRefCellValue aCell;
 942                 aCell.assign(*pDok, aAdr);
 943                 if (!aCell.isEmpty())
 944                 {
 945                     if (aCell.hasNumeric())
 946                     {
 947                         nCount++;
 948                         CurFmtToFuncFmt();
 949                         if (nGlobalError)
 950                         {
 951                             nGlobalError = 0;
 952                             nCount--;
 953                         }
 954                     }
 955                 }
 956             }
 957             break;
 958             case svDoubleRef :
 959             case svRefList :
 960             {
 961                 PopDoubleRef( aRange, nParamCount, nRefInList);
 962                 if (nGlobalError)
 963                 {
 964                     nGlobalError = 0;
 965                     break;
 966                 }
 967
 968                 sc::ColumnSpanSet aSet(false);
 969                 aSet.set(aRange, true);
 970                 if (glSubTotal)
 971                     // Skip all filtered rows and subtotal formula cells.
 972                     pDok->MarkSubTotalCells(aSet, aRange, false);
 973
 974                 FuncCount aAction;
 975                 aSet.executeColumnAction(*pDok, aAction);
 976                 nCount += aAction.getCount();
 977
 978                 // Get the number format of the last iterated cell.
 979                 nFuncFmtIndex = aAction.getNumberFormat();
 980                 nFuncFmtType = pDok->GetFormatTable()->GetType(nFuncFmtIndex);
 981             }
 982             break;
 983             case svExternalDoubleRef:
 984             {
 985                 ScMatrixRef pMat;
 986                 PopExternalDoubleRef(pMat);
 987                 if (nGlobalError)
 988                     break;
 989
 990                 double fMem = 0.0, fRes = 0.0;
 991                 bool bNull = true;
 992                 IterateMatrix(pMat, ifCOUNT, false, nCount, nFuncFmtType, fRes, fMem, bNull);
 993             }
 994             break;
 995             case svMatrix :
 996             {
 997                 ScMatrixRef pMat = PopMatrix();
 998                 double fMem = 0.0, fRes = 0.0;
 999                 bool bNull = true;
1000                 IterateMatrix(pMat, ifCOUNT, false, nCount, nFuncFmtType, fRes, fMem, bNull);
1001             }
1002             break;
1003             case svError:
1004             {
1005                 PopError();
1006                 nGlobalError = 0;
1007             }
1008             break;
1009             default :
1010                 while (nParamCount-- > 0)
1011                     PopError();
1012                 SetError(errIllegalParameter);
1013         }
1014     }
1015
1016     nFuncFmtType = NUMBERFORMAT_NUMBER;
1017
1018     PushDouble(nCount);
1019 }
1020
1021
1022 void ScInterpreter::ScCount2()
1023 {
1024     PushDouble( IterateParameters( ifCOUNT2 ) );
1025 }
1026
1027 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */