1. mst
  2. ooo340

Commits

Eike Rathke [er]  committed 237cb91

calcishmakkica: #i101466# implement AVERAGEIF; slightly reworked patch by <makkica>

  • Participants
  • Parent commits 1c32319
  • Branches default

Comments (0)

Files changed (11)

File formula/inc/formula/compiler.hrc

View file
  • Ignore whitespace
 #define SC_OPCODE_NUMBERVALUE       392
 #define SC_OPCODE_CHISQ_DIST        393
 #define SC_OPCODE_CHISQ_INV         394
-#define SC_OPCODE_STOP_2_PAR        395
+#define SC_OPCODE_AVERAGE_IF        395
+#define SC_OPCODE_STOP_2_PAR        396
 
-#define SC_OPCODE_LAST_OPCODE_ID    394      /* last OpCode */
+#define SC_OPCODE_LAST_OPCODE_ID    395      /* last OpCode */
 
 /*** Interna ***/
 #define SC_OPCODE_INTERNAL_BEGIN   9999

File formula/inc/formula/opcode.hxx

View file
  • Ignore whitespace
 		ocCountEmptyCells	= SC_OPCODE_COUNT_EMPTY_CELLS,
 		ocCountIf			= SC_OPCODE_COUNT_IF,
 		ocSumIf				= SC_OPCODE_SUM_IF,
+		ocAverageIf			= SC_OPCODE_AVERAGE_IF,
 		ocLookup			= SC_OPCODE_LOOKUP,
 		ocVLookup			= SC_OPCODE_V_LOOKUP,
 		ocHLookup			= SC_OPCODE_H_LOOKUP,

File formula/source/core/resource/core_resource.src

View file
  • Ignore whitespace
 	String SC_OPCODE_COUNT_EMPTY_CELLS { Text = "COUNTBLANK" ; };
 	String SC_OPCODE_COUNT_IF { Text = "COUNTIF" ; };
 	String SC_OPCODE_SUM_IF { Text = "SUMIF" ; };
+	String SC_OPCODE_AVERAGE_IF { Text = "AVERAGEIF" ; };
 	String SC_OPCODE_LOOKUP { Text = "LOOKUP" ; };
 	String SC_OPCODE_V_LOOKUP { Text = "VLOOKUP" ; };
 	String SC_OPCODE_H_LOOKUP { Text = "HLOOKUP" ; };
 	String SC_OPCODE_COUNT_EMPTY_CELLS { Text = "COUNTBLANK" ; };
 	String SC_OPCODE_COUNT_IF { Text = "COUNTIF" ; };
 	String SC_OPCODE_SUM_IF { Text = "SUMIF" ; };
+	String SC_OPCODE_AVERAGE_IF { Text = "AVERAGEIF" ; };
 	String SC_OPCODE_LOOKUP { Text = "LOOKUP" ; };
 	String SC_OPCODE_V_LOOKUP { Text = "VLOOKUP" ; };
 	String SC_OPCODE_H_LOOKUP { Text = "HLOOKUP" ; };
 	{
 		Text [ en-US ] = "SUMIF" ;
 	};
+	String SC_OPCODE_AVERAGE_IF
+	{
+		Text [ en-US ] = "AVERAGEIF" ;
+	};
 	String SC_OPCODE_LOOKUP
 	{
 		Text [ en-US ] = "LOOKUP" ;

File sc/inc/scfuncs.hrc

View file
  • Ignore whitespace
 #define HID_FUNC_RAD				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+34)
 #define HID_FUNC_EXP				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+35)
 #define HID_FUNC_LOG				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+36)
-#define HID_FUNC_LN					(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+37)
+#define HID_FUNC_LN				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+37)
 #define HID_FUNC_LOG10				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+38)
 #define HID_FUNC_FAKULTAET			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+39)
 #define HID_FUNC_REST				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+40)
 #define HID_FUNC_VORZEICHEN			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+41)
-#define HID_FUNC_TEILERGEBNIS		(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+42)
+#define HID_FUNC_TEILERGEBNIS			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+42)
 #define HID_FUNC_GANZZAHL			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+43)
 #define HID_FUNC_KUERZEN			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+44)
 #define HID_FUNC_RUNDEN				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+45)
 #define HID_FUNC_GERADE				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+48)
 #define HID_FUNC_UNGERADE			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+49)
 #define HID_FUNC_OBERGRENZE			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+50)
-#define HID_FUNC_UNTERGRENZE		(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+51)
+#define HID_FUNC_UNTERGRENZE			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+51)
 #define HID_FUNC_GGT				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+52)
 #define HID_FUNC_KGV				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+53)
 #define HID_FUNC_UMRECHNEN			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+54)
-#define HID_FUNC_EUROCONVERT        (HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+55)
+#define HID_FUNC_EUROCONVERT        		(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+55)
+#define HID_FUNC_AVERAGEIF        		(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATH*ID_FUNCTION_OFFSET)+56)
 
 #define HID_FUNC_MTRANS				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+1)
 #define HID_FUNC_MMULT				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+2)
 #define HID_FUNC_MDET				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+3)
 #define HID_FUNC_MINV				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+4)
-#define HID_FUNC_EINHEITSMATRIX		(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+5)
-#define HID_FUNC_SUMMENPRODUKT		(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+6)
+#define HID_FUNC_EINHEITSMATRIX			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+5)
+#define HID_FUNC_SUMMENPRODUKT			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+6)
 #define HID_FUNC_SUMMEX2MY2			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+7)
 #define HID_FUNC_SUMMEX2PY2			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+8)
 #define HID_FUNC_SUMMEXMY2			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+9)
-#define HID_FUNC_HAEUFIGKEIT		(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+10)
+#define HID_FUNC_HAEUFIGKEIT			(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+10)
 #define HID_FUNC_RGP				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+11)
 #define HID_FUNC_RKP				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+12)
 #define HID_FUNC_TREND				(HID_SC_FUNC_DUMMY+(ID_FUNCTION_GRP_MATRIX*ID_FUNCTION_OFFSET)+13)

File sc/source/core/inc/interpre.hxx

View file
  • Ignore whitespace
     formula::FormulaToken* pPointer[ MAXSTACK ];
 };
 
-enum ScIterFunc {
-    ifSUM,                              // Aufsummieren
-    ifSUMSQ,                            // Quadratsummen
-    ifPRODUCT,                          // Multiplizieren
-    ifAVERAGE,                          // Durchschnitt
-    ifCOUNT,                            // Anzahl Werte
-    ifCOUNT2,                           // Anzahl Werte (nichtleer)
-    ifMIN,                              // Minimum
-    ifMAX                               // Maximum
+enum ScIterFunc
+{
+    ifSUM,     // Sum
+    ifSUMSQ,   // Sum squares
+    ifPRODUCT, // Product
+    ifAVERAGE, // Average
+    ifCOUNT,   // Count
+    ifCOUNT2,  // Count non-empty
+    ifMIN,     // Minimum
+    ifMAX      // Maximum
+};
+
+enum ScIterFuncIf
+{
+    ifSUMIF,    // Conditional sum
+    ifAVERAGEIF // Conditional average
 };
 
 struct FormulaTokenRef_less
 void ScRow();
 void ScTable();
 void ScMatch();
+double IterateParametersIf( ScIterFuncIf );
 void ScCountIf();
 void ScSumIf();
+void ScAverageIf();
 void ScCountEmptyCells();
 void ScLookup();
 void ScHLookup();

File sc/source/core/tool/interpr1.cxx

View file
  • Ignore whitespace
 }
 
 
+double ScInterpreter::IterateParametersIf( ScIterFuncIf eFunc )
+{
+    BYTE nParamCount = GetByte();
+    if ( MustHaveParamCount( nParamCount, 2, 3 ) )
+    {
+        SCCOL nCol3 = 0;
+        SCROW nRow3 = 0;
+        SCTAB nTab3 = 0;
+
+        ScMatrixRef pSumExtraMatrix;
+        bool bSumExtraRange = (nParamCount == 3);
+        if (bSumExtraRange)
+        {
+            // Save only the upperleft cell in case of cell range.  The geometry
+            // of the 3rd parameter is taken from the 1st parameter.
+
+            switch ( GetStackType() )
+            {
+                case svDoubleRef :
+                    {
+                        SCCOL nColJunk = 0;
+                        SCROW nRowJunk = 0;
+                        SCTAB nTabJunk = 0;
+                        PopDoubleRef( nCol3, nRow3, nTab3, nColJunk, nRowJunk, nTabJunk );
+                        if ( nTabJunk != nTab3 )
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                    }
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nCol3, nRow3, nTab3 );
+                    break;
+                case svMatrix:
+                    pSumExtraMatrix = PopMatrix();
+                    //! nCol3, nRow3, nTab3 remain 0
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+        }
+        String rString;
+        double fVal = 0.0;
+        BOOL bIsString = TRUE;
+        switch ( GetStackType() )
+        {
+            case svDoubleRef :
+            case svSingleRef :
+                {
+                    ScAddress aAdr;
+                    if ( !PopDoubleRefOrSingleRef( aAdr ) )
+                        return 0;
+
+                    ScBaseCell* pCell = GetCell( aAdr );
+                    switch ( GetCellType( pCell ) )
+                    {
+                        case CELLTYPE_VALUE :
+                            fVal = GetCellValue( aAdr, pCell );
+                            bIsString = FALSE;
+                            break;
+                        case CELLTYPE_FORMULA :
+                            if( ((ScFormulaCell*)pCell)->IsValue() )
+                            {
+                                fVal = GetCellValue( aAdr, pCell );
+                                bIsString = FALSE;
+                            }
+                            else
+                                GetCellString(rString, pCell);
+                            break;
+                        case CELLTYPE_STRING :
+                        case CELLTYPE_EDIT :
+                            GetCellString(rString, pCell);
+                            break;
+                        default:
+                            fVal = 0.0;
+                            bIsString = FALSE;
+                    }
+                }
+                break;
+            case svString:
+                rString = GetString();
+                break;
+            case svMatrix :
+                {
+                    ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
+                    bIsString = ScMatrix::IsNonValueType( nType);
+                }
+                break;
+            default:
+                {
+                    fVal = GetDouble();
+                    bIsString = FALSE;
+                }
+        }
+
+        double fSum = 0.0;
+        double fMem = 0.0;
+        double fRes = 0.0;
+        double fCount = 0.0;
+        BOOL bNull = TRUE;
+        short nParam = 1;
+        size_t nRefInList = 0;
+        while (nParam-- > 0)
+        {
+            SCCOL nCol1;
+            SCROW nRow1;
+            SCTAB nTab1;
+            SCCOL nCol2;
+            SCROW nRow2;
+            SCTAB nTab2;
+            ScMatrixRef pQueryMatrix;
+            switch ( GetStackType() )
+            {
+                case svRefList :
+                    if (bSumExtraRange)
+                    {
+                        SetError( errIllegalParameter);
+                    }
+                    else
+                    {
+                        ScRange aRange;
+                        PopDoubleRef( aRange, nParam, nRefInList);
+                        aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
+                    }
+                    break;
+                case svDoubleRef :
+                    PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
+                    break;
+                case svSingleRef :
+                    PopSingleRef( nCol1, nRow1, nTab1 );
+                    nCol2 = nCol1;
+                    nRow2 = nRow1;
+                    nTab2 = nTab1;
+                    break;
+                case svMatrix:
+                    {
+                        pQueryMatrix = PopMatrix();
+                        if (!pQueryMatrix)
+                        {
+                            SetError( errIllegalParameter);
+                        }
+                        nCol1 = 0;
+                        nRow1 = 0;
+                        nTab1 = 0;
+                        SCSIZE nC, nR;
+                        pQueryMatrix->GetDimensions( nC, nR);
+                        nCol2 = static_cast<SCCOL>(nC - 1);
+                        nRow2 = static_cast<SCROW>(nR - 1);
+                        nTab2 = 0;
+                    }
+                    break;
+                default:
+                    SetError( errIllegalParameter);
+            }
+            if ( nTab1 != nTab2 )
+            {
+                SetError( errIllegalParameter);
+            }
+
+            if (bSumExtraRange)
+            {
+                // Take the range geometry of the 1st parameter and apply it to
+                // the 3rd. If parts of the resulting range would point outside
+                // the sheet, don't complain but silently ignore and simply cut
+                // them away, this is what Xcl does :-/
+
+                // For the cut-away part we also don't need to determine the
+                // criteria match, so shrink the source range accordingly,
+                // instead of the result range.
+                SCCOL nColDelta = nCol2 - nCol1;
+                SCROW nRowDelta = nRow2 - nRow1;
+                SCCOL nMaxCol;
+                SCROW nMaxRow;
+                if (pSumExtraMatrix)
+                {
+                    SCSIZE nC, nR;
+                    pSumExtraMatrix->GetDimensions( nC, nR);
+                    nMaxCol = static_cast<SCCOL>(nC - 1);
+                    nMaxRow = static_cast<SCROW>(nR - 1);
+                }
+                else
+                {
+                    nMaxCol = MAXCOL;
+                    nMaxRow = MAXROW;
+                }
+                if (nCol3 + nColDelta > nMaxCol)
+                {
+                    SCCOL nNewDelta = nMaxCol - nCol3;
+                    nCol2 = nCol1 + nNewDelta;
+                }
+
+                if (nRow3 + nRowDelta > nMaxRow)
+                {
+                    SCROW nNewDelta = nMaxRow - nRow3;
+                    nRow2 = nRow1 + nNewDelta;
+                }
+            }
+            else
+            {
+                nCol3 = nCol1;
+                nRow3 = nRow1;
+                nTab3 = nTab1;
+            }
+
+            if (nGlobalError == 0)
+            {
+                ScQueryParam rParam;
+                rParam.nRow1       = nRow1;
+                rParam.nRow2       = nRow2;
+
+                ScQueryEntry& rEntry = rParam.GetEntry(0);
+                rEntry.bDoQuery = TRUE;
+                if (!bIsString)
+                {
+                    rEntry.bQueryByString = FALSE;
+                    rEntry.nVal = fVal;
+                    rEntry.eOp = SC_EQUAL;
+                }
+                else
+                {
+                    rParam.FillInExcelSyntax(rString, 0);
+                    sal_uInt32 nIndex = 0;
+                    rEntry.bQueryByString =
+                        !(pFormatter->IsNumberFormat(
+                                    *rEntry.pStr, nIndex, rEntry.nVal));
+                    if ( rEntry.bQueryByString )
+                        rParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
+                }
+                ScAddress aAdr;
+                aAdr.SetTab( nTab3 );
+                rParam.nCol1  = nCol1;
+                rParam.nCol2  = nCol2;
+                rEntry.nField = nCol1;
+                SCsCOL nColDiff = nCol3 - nCol1;
+                SCsROW nRowDiff = nRow3 - nRow1;
+                if (pQueryMatrix)
+                {
+                    // Never case-sensitive.
+                    ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
+                    ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
+                    if (nGlobalError || !pResultMatrix)
+                    {
+                        SetError( errIllegalParameter);
+                    }
+
+                    if (pSumExtraMatrix)
+                    {
+                        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+                        {
+                            for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+                            {
+                                if (pResultMatrix->IsValue( nCol, nRow) && 
+                                        pResultMatrix->GetDouble( nCol, nRow))
+                                {
+                                    SCSIZE nC = nCol + nColDiff;
+                                    SCSIZE nR = nRow + nRowDiff;
+                                    if (pSumExtraMatrix->IsValue( nC, nR))
+                                    {
+                                        fVal = pSumExtraMatrix->GetDouble( nC, nR);
+                                        ++fCount;
+                                        if ( bNull && fVal != 0.0 )
+                                        {
+                                            bNull = FALSE;
+                                            fMem = fVal;
+                                        }
+                                        else
+                                            fSum += fVal;
+                                    }
+                                }
+                            }
+                        }
+                    }
+                    else
+                    {
+                        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
+                        {
+                            for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
+                            {
+                                if (pResultMatrix->GetDouble( nCol, nRow))
+                                {
+                                    aAdr.SetCol( nCol + nColDiff);
+                                    aAdr.SetRow( nRow + nRowDiff);
+                                    ScBaseCell* pCell = GetCell( aAdr );
+                                    if ( HasCellValueData(pCell) )
+                                    {
+                                        fVal = GetCellValue( aAdr, pCell );
+                                        ++fCount;
+                                        if ( bNull && fVal != 0.0 )
+                                        {
+                                            bNull = FALSE;
+                                            fMem = fVal;
+                                        }
+                                        else
+                                            fSum += fVal;
+                                    }
+                                }
+                            }
+                        }
+                    }
+                }
+                else
+                {
+                    ScQueryCellIterator aCellIter(pDok, nTab1, rParam, FALSE);
+                    // Increment Entry.nField in iterator when switching to next column.
+                    aCellIter.SetAdvanceQueryParamEntryField( TRUE );
+                    if ( aCellIter.GetFirst() )
+                    {
+                        if (pSumExtraMatrix)
+                        {
+                            do
+                            {
+                                SCSIZE nC = aCellIter.GetCol() + nColDiff;
+                                SCSIZE nR = aCellIter.GetRow() + nRowDiff;
+                                if (pSumExtraMatrix->IsValue( nC, nR))
+                                {
+                                    fVal = pSumExtraMatrix->GetDouble( nC, nR);
+                                    ++fCount;
+                                    if ( bNull && fVal != 0.0 )
+                                    {
+                                        bNull = FALSE;
+                                        fMem = fVal;
+                                    }
+                                    else
+                                        fSum += fVal;
+                                }
+                            } while ( aCellIter.GetNext() );
+                        }
+                        else
+                        {
+                            do
+                            {
+                                aAdr.SetCol( aCellIter.GetCol() + nColDiff);
+                                aAdr.SetRow( aCellIter.GetRow() + nRowDiff);
+                                ScBaseCell* pCell = GetCell( aAdr );
+                                if ( HasCellValueData(pCell) )
+                                {
+                                    fVal = GetCellValue( aAdr, pCell );
+                                    ++fCount;
+                                    if ( bNull && fVal != 0.0 )
+                                    {
+                                        bNull = FALSE;
+                                        fMem = fVal;
+                                    }
+                                    else
+                                        fSum += fVal;
+                                }
+                            } while ( aCellIter.GetNext() );
+                        }
+                    }
+                }
+            }
+            else
+            {
+                SetError( errIllegalParameter);
+            }
+        }
+
+        switch( eFunc )
+        {
+            case ifSUMIF:     fRes = ::rtl::math::approxAdd( fSum, fMem ); break;
+            case ifAVERAGEIF: fRes = div( ::rtl::math::approxAdd( fSum, fMem ), fCount); break;
+        }
+        return fRes;
+    }
+    return 0;
+}
+
+void ScInterpreter::ScSumIf()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSumIf" );
+    PushDouble( IterateParametersIf( ifSUMIF));
+}
+
+void ScInterpreter::ScAverageIf()
+{
+    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "makkica", "ScInterpreter::ScAverageIf" );
+    PushDouble( IterateParametersIf( ifAVERAGEIF));
+}
+
 void ScInterpreter::ScCountIf()
 {
     RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCountIf" );
             break;
             case svMatrix :
             {
-                ScMatValType nType = GetDoubleOrStringFromMatrix( fVal,
-                        rString);
+                ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
                 bIsString = ScMatrix::IsNonValueType( nType);
             }
             break;
                 bIsString = FALSE;
             }
         }
-        double fSum = 0.0;
+        double fCount = 0.0;
         short nParam = 1;
         size_t nRefInList = 0;
         while (nParam-- > 0)
                     {
                         if (pResultMatrix->IsValue( nIndex) && 
                                 pResultMatrix->GetDouble( nIndex))
-                            ++fSum;
+                            ++fCount;
                     }
                 }
                 else
                     {
                         do
                         {
-                            fSum++;
+                            fCount++;
                         } while ( aCellIter.GetNext() );
                     }
                 }
                 return;
             }
         }
-        PushDouble(fSum);
-    }
-}
-
-
-void ScInterpreter::ScSumIf()
-{
-    RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSumIf" );
-    BYTE nParamCount = GetByte();
-    if ( MustHaveParamCount( nParamCount, 2, 3 ) )
-    {
-        SCCOL nCol3 = 0;
-        SCROW nRow3 = 0;
-        SCTAB nTab3 = 0;
-
-        ScMatrixRef pSumExtraMatrix;
-        bool bSumExtraRange = (nParamCount == 3);
-        if (bSumExtraRange)
-        {
-            // Save only the upperleft cell in case of cell range.  The geometry
-            // of the 3rd parameter is taken from the 1st parameter.
-
-            switch ( GetStackType() )
-            {
-                case svDoubleRef :
-                {
-                    SCCOL nColJunk = 0;
-                    SCROW nRowJunk = 0;
-                    SCTAB nTabJunk = 0;
-                    PopDoubleRef( nCol3, nRow3, nTab3, nColJunk, nRowJunk, nTabJunk );
-                    if ( nTabJunk != nTab3 )
-                    {
-                        PushIllegalParameter();
-                        return;
-                    }
-                }
-                break;
-                case svSingleRef :
-                    PopSingleRef( nCol3, nRow3, nTab3 );
-                break;
-                case svMatrix:
-                    pSumExtraMatrix = PopMatrix();
-                    //! nCol3, nRow3, nTab3 remain 0
-                break;
-                default:
-                    PushIllegalParameter();
-                    return ;
-            }
-        }
-        String rString;
-        double fVal = 0.0;
-        BOOL bIsString = TRUE;
-        switch ( GetStackType() )
-        {
-            case svDoubleRef :
-            case svSingleRef :
-            {
-                ScAddress aAdr;
-                if ( !PopDoubleRefOrSingleRef( aAdr ) )
-                {
-                    PushInt(0);
-                    return ;
-                }
-                ScBaseCell* pCell = GetCell( aAdr );
-                switch ( GetCellType( pCell ) )
-                {
-                    case CELLTYPE_VALUE :
-                        fVal = GetCellValue( aAdr, pCell );
-                        bIsString = FALSE;
-                        break;
-                    case CELLTYPE_FORMULA :
-                        if( ((ScFormulaCell*)pCell)->IsValue() )
-                        {
-                            fVal = GetCellValue( aAdr, pCell );
-                            bIsString = FALSE;
-                        }
-                        else
-                            GetCellString(rString, pCell);
-                        break;
-                    case CELLTYPE_STRING :
-                    case CELLTYPE_EDIT :
-                        GetCellString(rString, pCell);
-                        break;
-                    default:
-                        fVal = 0.0;
-                        bIsString = FALSE;
-                }
-            }
-            break;
-            case svString:
-                rString = GetString();
-            break;
-            case svMatrix :
-            {
-                ScMatValType nType = GetDoubleOrStringFromMatrix( fVal,
-                        rString);
-                bIsString = ScMatrix::IsNonValueType( nType);
-            }
-            break;
-            default:
-            {
-                fVal = GetDouble();
-                bIsString = FALSE;
-            }
-        }
-
-        double fSum = 0.0;
-        double fMem = 0.0;
-        BOOL bNull = TRUE;
-        short nParam = 1;
-        size_t nRefInList = 0;
-        while (nParam-- > 0)
-        {
-            SCCOL nCol1;
-            SCROW nRow1;
-            SCTAB nTab1;
-            SCCOL nCol2;
-            SCROW nRow2;
-            SCTAB nTab2;
-            ScMatrixRef pQueryMatrix;
-            switch ( GetStackType() )
-            {
-                case svRefList :
-                    if (bSumExtraRange)
-                    {
-                        PushIllegalParameter();
-                        return;
-                    }
-                    else
-                    {
-                        ScRange aRange;
-                        PopDoubleRef( aRange, nParam, nRefInList);
-                        aRange.GetVars( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
-                    }
-                    break;
-                case svDoubleRef :
-                    PopDoubleRef( nCol1, nRow1, nTab1, nCol2, nRow2, nTab2 );
-                    break;
-                case svSingleRef :
-                    PopSingleRef( nCol1, nRow1, nTab1 );
-                    nCol2 = nCol1;
-                    nRow2 = nRow1;
-                    nTab2 = nTab1;
-                    break;
-                case svMatrix:
-                    {
-                        pQueryMatrix = PopMatrix();
-                        if (!pQueryMatrix)
-                        {
-                            PushIllegalParameter();
-                            return;
-                        }
-                        nCol1 = 0;
-                        nRow1 = 0;
-                        nTab1 = 0;
-                        SCSIZE nC, nR;
-                        pQueryMatrix->GetDimensions( nC, nR);
-                        nCol2 = static_cast<SCCOL>(nC - 1);
-                        nRow2 = static_cast<SCROW>(nR - 1);
-                        nTab2 = 0;
-                    }
-                    break;
-                default:
-                    PushIllegalParameter();
-                    return ;
-            }
-            if ( nTab1 != nTab2 )
-            {
-                PushIllegalArgument();
-                return;
-            }
-
-            if (bSumExtraRange)
-            {
-                // Take the range geometry of the 1st parameter and apply it to
-                // the 3rd. If parts of the resulting range would point outside
-                // the sheet, don't complain but silently ignore and simply cut
-                // them away, this is what Xcl does :-/
-
-                // For the cut-away part we also don't need to determine the
-                // criteria match, so shrink the source range accordingly,
-                // instead of the result range.
-                SCCOL nColDelta = nCol2 - nCol1;
-                SCROW nRowDelta = nRow2 - nRow1;
-                SCCOL nMaxCol;
-                SCROW nMaxRow;
-                if (pSumExtraMatrix)
-                {
-                    SCSIZE nC, nR;
-                    pSumExtraMatrix->GetDimensions( nC, nR);
-                    nMaxCol = static_cast<SCCOL>(nC - 1);
-                    nMaxRow = static_cast<SCROW>(nR - 1);
-                }
-                else
-                {
-                    nMaxCol = MAXCOL;
-                    nMaxRow = MAXROW;
-                }
-                if (nCol3 + nColDelta > nMaxCol)
-                {
-                    SCCOL nNewDelta = nMaxCol - nCol3;
-                    nCol2 = nCol1 + nNewDelta;
-                }
-
-                if (nRow3 + nRowDelta > nMaxRow)
-                {
-                    SCROW nNewDelta = nMaxRow - nRow3;
-                    nRow2 = nRow1 + nNewDelta;
-                }
-            }
-            else
-            {
-                nCol3 = nCol1;
-                nRow3 = nRow1;
-                nTab3 = nTab1;
-            }
-
-            if (nGlobalError == 0)
-            {
-                ScQueryParam rParam;
-                rParam.nRow1       = nRow1;
-                rParam.nRow2       = nRow2;
-
-                ScQueryEntry& rEntry = rParam.GetEntry(0);
-                rEntry.bDoQuery = TRUE;
-                if (!bIsString)
-                {
-                    rEntry.bQueryByString = FALSE;
-                    rEntry.nVal = fVal;
-                    rEntry.eOp = SC_EQUAL;
-                }
-                else
-                {
-                    rParam.FillInExcelSyntax(rString, 0);
-                    sal_uInt32 nIndex = 0;
-                    rEntry.bQueryByString =
-                        !(pFormatter->IsNumberFormat(
-                                    *rEntry.pStr, nIndex, rEntry.nVal));
-                    if ( rEntry.bQueryByString )
-                        rParam.bRegExp = MayBeRegExp( *rEntry.pStr, pDok );
-                }
-                ScAddress aAdr;
-                aAdr.SetTab( nTab3 );
-                rParam.nCol1  = nCol1;
-                rParam.nCol2  = nCol2;
-                rEntry.nField = nCol1;
-                SCsCOL nColDiff = nCol3 - nCol1;
-                SCsROW nRowDiff = nRow3 - nRow1;
-                if (pQueryMatrix)
-                {
-                    // Never case-sensitive.
-                    ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
-                    ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
-                    if (nGlobalError || !pResultMatrix)
-                    {
-                        PushIllegalParameter();
-                        return;
-                    }
-
-                    if (pSumExtraMatrix)
-                    {
-                        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
-                        {
-                            for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
-                            {
-                                if (pResultMatrix->IsValue( nCol, nRow) && 
-                                        pResultMatrix->GetDouble( nCol, nRow))
-                                {
-                                    SCSIZE nC = nCol + nColDiff;
-                                    SCSIZE nR = nRow + nRowDiff;
-                                    if (pSumExtraMatrix->IsValue( nC, nR))
-                                    {
-                                        fVal = pSumExtraMatrix->GetDouble( nC, nR);
-                                        if ( bNull && fVal != 0.0 )
-                                        {
-                                            bNull = FALSE;
-                                            fMem = fVal;
-                                        }
-                                        else
-                                            fSum += fVal;
-                                    }
-                                }
-                            }
-                        }
-                    }
-                    else
-                    {
-                        for (SCCOL nCol = nCol1; nCol <= nCol2; ++nCol)
-                        {
-                            for (SCROW nRow = nRow1; nRow <= nRow2; ++nRow)
-                            {
-                                if (pResultMatrix->GetDouble( nCol, nRow))
-                                {
-                                    aAdr.SetCol( nCol + nColDiff);
-                                    aAdr.SetRow( nRow + nRowDiff);
-                                    ScBaseCell* pCell = GetCell( aAdr );
-                                    if ( HasCellValueData(pCell) )
-                                    {
-                                        fVal = GetCellValue( aAdr, pCell );
-                                        if ( bNull && fVal != 0.0 )
-                                        {
-                                            bNull = FALSE;
-                                            fMem = fVal;
-                                        }
-                                        else
-                                            fSum += fVal;
-                                    }
-                                }
-                            }
-                        }
-                    }
-                }
-                else
-                {
-                    ScQueryCellIterator aCellIter(pDok, nTab1, rParam, FALSE);
-                    // Increment Entry.nField in iterator when switching to next column.
-                    aCellIter.SetAdvanceQueryParamEntryField( TRUE );
-                    if ( aCellIter.GetFirst() )
-                    {
-                        if (pSumExtraMatrix)
-                        {
-                            do
-                            {
-                                SCSIZE nC = aCellIter.GetCol() + nColDiff;
-                                SCSIZE nR = aCellIter.GetRow() + nRowDiff;
-                                if (pSumExtraMatrix->IsValue( nC, nR))
-                                {
-                                    fVal = pSumExtraMatrix->GetDouble( nC, nR);
-                                    if ( bNull && fVal != 0.0 )
-                                    {
-                                        bNull = FALSE;
-                                        fMem = fVal;
-                                    }
-                                    else
-                                        fSum += fVal;
-                                }
-                            } while ( aCellIter.GetNext() );
-                        }
-                        else
-                        {
-                            do
-                            {
-                                aAdr.SetCol( aCellIter.GetCol() + nColDiff);
-                                aAdr.SetRow( aCellIter.GetRow() + nRowDiff);
-                                ScBaseCell* pCell = GetCell( aAdr );
-                                if ( HasCellValueData(pCell) )
-                                {
-                                    fVal = GetCellValue( aAdr, pCell );
-                                    if ( bNull && fVal != 0.0 )
-                                    {
-                                        bNull = FALSE;
-                                        fMem = fVal;
-                                    }
-                                    else
-                                        fSum += fVal;
-                                }
-                            } while ( aCellIter.GetNext() );
-                        }
-                    }
-                }
-            }
-            else
-            {
-                PushIllegalParameter();
-                return;
-            }
-        }
-        PushDouble( ::rtl::math::approxAdd( fSum, fMem ) );
+        PushDouble(fCount);
     }
 }
 

File sc/source/core/tool/interpr4.cxx

View file
  • Ignore whitespace
                 case ocCountEmptyCells  : ScCountEmptyCells();          break;
                 case ocCountIf          : ScCountIf();                  break;
                 case ocSumIf            : ScSumIf();                    break;
+                case ocAverageIf        : ScAverageIf();                break;
                 case ocLookup           : ScLookup();                   break;
                 case ocVLookup          : ScVLookup();                  break;
                 case ocHLookup          : ScHLookup();                  break;

File sc/source/core/tool/parclass.cxx

View file
  • Ignore whitespace
     { ocSubTotal,        {{ Value, Reference                                     }, true }},
     { ocSum,             {{ Reference                                            }, true }},
     { ocSumIf,           {{ Reference, Value, Reference                          }, false }},
+    { ocAverageIf,       {{ Reference, Value, Reference                          }, false }},
     { ocSumProduct,      {{ ForceArray                                           }, true }},
     { ocSumSQ,           {{ Reference                                            }, true }},
     { ocSumX2MY2,        {{ ForceArray, ForceArray                               }, false }},

File sc/source/core/tool/scmatrix.cxx

View file
  • Ignore whitespace
 	}
 	return bOr;
 }
-

File sc/source/ui/src/scfuncs.src

View file
  • Ignore whitespace
 			Text [ en-US ] = "Logical value 1, logical value 2;...are 1 to 30 conditions to be tested and each returns either TRUE or FALSE." ;
 		};
 	};
-	 // -=*# Resource for function ABS #*=-
+     // -=*# Resource for function ABS #*=-
 	Resource SC_OPCODE_ABS
 	{
 		String 1 // Description
 			Text [ en-US ] = "The range from which the values are to be totalled." ;
 		};
 	};
+
+	// -=*# Resource for function AVERAGEIF #*=-
+	Resource SC_OPCODE_AVERAGE_IF
+	{
+		String 1 // Description
+		{
+			Text [ en-US ] = "Averages the arguments that meet the conditions." ;
+		};
+		ExtraData =
+		{
+            0;
+			ID_FUNCTION_GRP_MATH;
+			U2S( HID_FUNC_AVERAGEIF );
+			3;	0;	0;	1;
+            0;
+		};
+		String 2 // Name of Parameter 1
+		{
+			Text [ en-US ] = "range" ;
+		};
+		String 3 // Description of Parameter 1
+		{
+			Text [ en-US ] = "The range to be evaluated by the criteria given." ;
+		};
+		String 4 // Name of Parameter 2
+		{
+			Text [ en-US ] = "criteria" ;
+		};
+		String 5 // Description of Parameter 2
+		{
+			Text [ en-US ] = "The cell range in which the search criteria are given." ;
+		};
+		String 6 // Name of Parameter 3
+		{
+			Text [ en-US ] = "average_range" ;
+		};
+		String 7 // Description of Parameter 3
+		{
+			Text [ en-US ] = "The range from which the values are to be averaged." ;
+		};
+	};
 	 // -=*# Resource for function ZÄHLENWENN #*=-
 	Resource SC_OPCODE_COUNT_IF
 	{

File sc/util/hidother.src

View file
  • Ignore whitespace
 hidspecial HID_FUNC_ISTZAHL		 { HelpID = HID_FUNC_ISTZAHL; };
 hidspecial HID_FUNC_ISTFORMEL		 { HelpID = HID_FUNC_ISTFORMEL; };
 hidspecial HID_FUNC_FORMEL		 { HelpID = HID_FUNC_FORMEL; };
-hidspecial HID_FUNC_N		 { HelpID = HID_FUNC_N; };
-hidspecial HID_FUNC_NV		 { HelpID = HID_FUNC_NV; };
-hidspecial HID_FUNC_TYP		 { HelpID = HID_FUNC_TYP; };
+hidspecial HID_FUNC_N		 	 { HelpID = HID_FUNC_N; };
+hidspecial HID_FUNC_NV		 	 { HelpID = HID_FUNC_NV; };
+hidspecial HID_FUNC_TYP		 	 { HelpID = HID_FUNC_TYP; };
 hidspecial HID_FUNC_ZELLE		 { HelpID = HID_FUNC_ZELLE; };
 hidspecial HID_FUNC_AKTUELL		 { HelpID = HID_FUNC_AKTUELL; };
 hidspecial HID_FUNC_FALSCH		 { HelpID = HID_FUNC_FALSCH; };
 hidspecial HID_FUNC_WAHR		 { HelpID = HID_FUNC_WAHR; };
 hidspecial HID_FUNC_WENN		 { HelpID = HID_FUNC_WENN; };
 hidspecial HID_FUNC_ODER		 { HelpID = HID_FUNC_ODER; };
-hidspecial HID_FUNC_UND		 { HelpID = HID_FUNC_UND; };
-hidspecial HID_FUNC_ABS		 { HelpID = HID_FUNC_ABS; };
+hidspecial HID_FUNC_UND		 	 { HelpID = HID_FUNC_UND; };
+hidspecial HID_FUNC_ABS		 	 { HelpID = HID_FUNC_ABS; };
 hidspecial HID_FUNC_POTENZ		 { HelpID = HID_FUNC_POTENZ; };
-hidspecial HID_FUNC_ANZAHLLEEREZELLEN		 { HelpID = HID_FUNC_ANZAHLLEEREZELLEN; };
-hidspecial HID_FUNC_PI		 { HelpID = HID_FUNC_PI; };
+hidspecial HID_FUNC_ANZAHLLEEREZELLEN	 { HelpID = HID_FUNC_ANZAHLLEEREZELLEN; };
+hidspecial HID_FUNC_PI		 	 { HelpID = HID_FUNC_PI; };
 hidspecial HID_FUNC_SUMME		 { HelpID = HID_FUNC_SUMME; };
-hidspecial HID_FUNC_QUADRATESUMME		 { HelpID = HID_FUNC_QUADRATESUMME; };
+hidspecial HID_FUNC_QUADRATESUMME	 { HelpID = HID_FUNC_QUADRATESUMME; };
 hidspecial HID_FUNC_PRODUKT		 { HelpID = HID_FUNC_PRODUKT; };
 hidspecial HID_FUNC_SUMMEWENN		 { HelpID = HID_FUNC_SUMMEWENN; };
 hidspecial HID_FUNC_ZAEHLENWENN		 { HelpID = HID_FUNC_ZAEHLENWENN; };
+hidspecial HID_FUNC_AVERAGEIF		 { HelpID = HID_FUNC_AVERAGEIF; };
 hidspecial HID_FUNC_WURZEL		 { HelpID = HID_FUNC_WURZEL; };
 hidspecial HID_FUNC_ZUFALLSZAHL		 { HelpID = HID_FUNC_ZUFALLSZAHL; };
 hidspecial HID_FUNC_ISTGERADE		 { HelpID = HID_FUNC_ISTGERADE; };