You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openoffice.apache.org by ar...@apache.org on 2012/09/06 02:30:42 UTC
svn commit: r1381445 - in /incubator/ooo/trunk/main: formula/inc/formula/
formula/source/core/resource/ sc/inc/ sc/source/core/inc/
sc/source/core/tool/ sc/source/ui/src/ sc/util/
Author: arist
Date: Thu Sep 6 00:30:41 2012
New Revision: 1381445
URL: http://svn.apache.org/viewvc?rev=1381445&view=rev
Log:
calcishmakkica: #i101466# implement AVERAGEIF; slightly reworked patch by <makkica>
From: Eike Rathke <er...@apache.org>
Original Author: Marina Plakalovic <ma...@openoffice.org>
Original Author: Eike Rathke [er] <ei...@oracle.com>
Original Committer: Eike Rathke [er] <ei...@oracle.com>
# HG changeset patch
# User Eike Rathke [er] <ei...@oracle.com>
# Date 1284055400 -7200
# Node ID 237cb91dd986ff11eb100cc631206cda102e91f7
# Parent 1c32319bb02fdf173de6a609fca9f2e9c9f4c73a
Modified:
incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc
incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx
incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src
incubator/ooo/trunk/main/sc/inc/helpids.h
incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx
incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx
incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx
incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx
incubator/ooo/trunk/main/sc/source/core/tool/scmatrix.cxx
incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src
incubator/ooo/trunk/main/sc/util/hidother.src
Modified: incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc (original)
+++ incubator/ooo/trunk/main/formula/inc/formula/compiler.hrc Thu Sep 6 00:30:41 2012
@@ -388,9 +388,10 @@
#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
Modified: incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx (original)
+++ incubator/ooo/trunk/main/formula/inc/formula/opcode.hxx Thu Sep 6 00:30:41 2012
@@ -274,6 +274,7 @@ enum OpCodeEnum
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,
Modified: incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src (original)
+++ incubator/ooo/trunk/main/formula/source/core/resource/core_resource.src Thu Sep 6 00:30:41 2012
@@ -240,6 +240,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGL
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" ; };
@@ -568,6 +569,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGL
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" ; };
@@ -1459,6 +1461,10 @@ Resource RID_STRLIST_FUNCTION_NAMES
{
Text [ en-US ] = "SUMIF" ;
};
+ String SC_OPCODE_AVERAGE_IF
+ {
+ Text [ en-US ] = "AVERAGEIF" ;
+ };
String SC_OPCODE_LOOKUP
{
Text [ en-US ] = "LOOKUP" ;
Modified: incubator/ooo/trunk/main/sc/inc/helpids.h
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/inc/helpids.h?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/inc/helpids.h (original)
+++ incubator/ooo/trunk/main/sc/inc/helpids.h Thu Sep 6 00:30:41 2012
@@ -495,6 +495,7 @@
#define HID_FUNC_QUADRATESUMME "SC_HID_FUNC_QUADRATESUMME"
#define HID_FUNC_PRODUKT "SC_HID_FUNC_PRODUKT"
#define HID_FUNC_SUMMEWENN "SC_HID_FUNC_SUMMEWENN"
+#define HID_FUNC_AVERAGEIF "SC_HID_FUNC_AVERAGEIF"
#define HID_FUNC_ZAEHLENWENN "SC_HID_FUNC_ZAEHLENWENN"
#define HID_FUNC_WURZEL "SC_HID_FUNC_WURZEL"
#define HID_FUNC_ZUFALLSZAHL "SC_HID_FUNC_ZUFALLSZAHL"
Modified: incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/inc/interpre.hxx Thu Sep 6 00:30:41 2012
@@ -86,15 +86,22 @@ public:
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
@@ -470,8 +477,10 @@ void ScColumn();
void ScRow();
void ScTable();
void ScMatch();
+double IterateParametersIf( ScIterFuncIf );
void ScCountIf();
void ScSumIf();
+void ScAverageIf();
void ScCountEmptyCells();
void ScLookup();
void ScHLookup();
Modified: incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/tool/interpr1.cxx Thu Sep 6 00:30:41 2012
@@ -4409,201 +4409,8 @@ void ScInterpreter::ScCountEmptyCells()
}
-void ScInterpreter::ScCountIf()
-{
- RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScCountIf" );
- if ( MustHaveParamCount( GetByte(), 2 ) )
- {
- String rString;
- double fVal = 0.0;
- sal_Bool bIsString = sal_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 = sal_False;
- break;
- case CELLTYPE_FORMULA :
- if( ((ScFormulaCell*)pCell)->IsValue() )
- {
- fVal = GetCellValue( aAdr, pCell );
- bIsString = sal_False;
- }
- else
- GetCellString(rString, pCell);
- break;
- case CELLTYPE_STRING :
- case CELLTYPE_EDIT :
- GetCellString(rString, pCell);
- break;
- default:
- fVal = 0.0;
- bIsString = sal_False;
- }
- }
- break;
- case svMatrix :
- {
- ScMatValType nType = GetDoubleOrStringFromMatrix( fVal,
- rString);
- bIsString = ScMatrix::IsNonValueType( nType);
- }
- break;
- case svString:
- rString = GetString();
- break;
- default:
- {
- fVal = GetDouble();
- bIsString = sal_False;
- }
- }
- double fSum = 0.0;
- 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 svDoubleRef :
- case svRefList :
- {
- ScRange aRange;
- PopDoubleRef( aRange, nParam, nRefInList);
- aRange.GetVars( 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 )
- {
- PushIllegalParameter();
- return;
- }
- if (nCol1 > nCol2)
- {
- PushIllegalParameter();
- return;
- }
- if (nGlobalError == 0)
- {
- ScQueryParam rParam;
- rParam.nRow1 = nRow1;
- rParam.nRow2 = nRow2;
-
- ScQueryEntry& rEntry = rParam.GetEntry(0);
- rEntry.bDoQuery = sal_True;
- if (!bIsString)
- {
- rEntry.bQueryByString = sal_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 );
- }
- rParam.nCol1 = nCol1;
- rParam.nCol2 = nCol2;
- rEntry.nField = nCol1;
- if (pQueryMatrix)
- {
- // Never case-sensitive.
- ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
- ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
- if (nGlobalError || !pResultMatrix)
- {
- PushIllegalParameter();
- return;
- }
-
- SCSIZE nSize = pResultMatrix->GetElementCount();
- for (SCSIZE nIndex = 0; nIndex < nSize; ++nIndex)
- {
- if (pResultMatrix->IsValue( nIndex) &&
- pResultMatrix->GetDouble( nIndex))
- ++fSum;
- }
- }
- else
- {
- ScQueryCellIterator aCellIter(pDok, nTab1, rParam, sal_False);
- // Entry.nField im Iterator bei Spaltenwechsel weiterschalten
- aCellIter.SetAdvanceQueryParamEntryField( sal_True );
- if ( aCellIter.GetFirst() )
- {
- do
- {
- fSum++;
- } while ( aCellIter.GetNext() );
- }
- }
- }
- else
- {
- PushIllegalParameter();
- return;
- }
- }
- PushDouble(fSum);
- }
-}
-
-
-void ScInterpreter::ScSumIf()
+double ScInterpreter::IterateParametersIf( ScIterFuncIf eFunc )
{
- RTL_LOGFILE_CONTEXT_AUTHOR( aLogger, "sc", "er", "ScInterpreter::ScSumIf" );
sal_uInt8 nParamCount = GetByte();
if ( MustHaveParamCount( nParamCount, 2, 3 ) )
{
@@ -4621,90 +4428,87 @@ void ScInterpreter::ScSumIf()
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;
+ SCCOL nColJunk = 0;
+ SCROW nRowJunk = 0;
+ SCTAB nTabJunk = 0;
+ PopDoubleRef( nCol3, nRow3, nTab3, nColJunk, nRowJunk, nTabJunk );
+ if ( nTabJunk != nTab3 )
+ {
+ SetError( errIllegalParameter);
+ }
}
- }
- break;
+ break;
case svSingleRef :
PopSingleRef( nCol3, nRow3, nTab3 );
- break;
+ break;
case svMatrix:
pSumExtraMatrix = PopMatrix();
//! nCol3, nRow3, nTab3 remain 0
- break;
+ break;
default:
- PushIllegalParameter();
- return ;
+ SetError( errIllegalParameter);
}
}
String rString;
double fVal = 0.0;
- sal_Bool bIsString = sal_True;
+ 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 = sal_False;
- break;
- case CELLTYPE_FORMULA :
- if( ((ScFormulaCell*)pCell)->IsValue() )
- {
+ ScAddress aAdr;
+ if ( !PopDoubleRefOrSingleRef( aAdr ) )
+ return 0;
+
+ ScBaseCell* pCell = GetCell( aAdr );
+ switch ( GetCellType( pCell ) )
+ {
+ case CELLTYPE_VALUE :
fVal = GetCellValue( aAdr, pCell );
- bIsString = sal_False;
- }
- else
+ 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;
- case CELLTYPE_STRING :
- case CELLTYPE_EDIT :
- GetCellString(rString, pCell);
- break;
- default:
- fVal = 0.0;
- bIsString = sal_False;
+ break;
+ default:
+ fVal = 0.0;
+ bIsString = false;
+ }
}
- }
- break;
+ break;
case svString:
rString = GetString();
- break;
+ break;
case svMatrix :
- {
- ScMatValType nType = GetDoubleOrStringFromMatrix( fVal,
- rString);
- bIsString = ScMatrix::IsNonValueType( nType);
- }
- break;
+ {
+ ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
+ bIsString = ScMatrix::IsNonValueType( nType);
+ }
+ break;
default:
- {
- fVal = GetDouble();
- bIsString = sal_False;
- }
+ {
+ fVal = GetDouble();
+ bIsString = false;
+ }
}
double fSum = 0.0;
double fMem = 0.0;
- sal_Bool bNull = sal_True;
+ double fRes = 0.0;
+ double fCount = 0.0;
+ bool bNull = true;
short nParam = 1;
size_t nRefInList = 0;
while (nParam-- > 0)
@@ -4721,8 +4525,7 @@ void ScInterpreter::ScSumIf()
case svRefList :
if (bSumExtraRange)
{
- PushIllegalParameter();
- return;
+ SetError( errIllegalParameter);
}
else
{
@@ -4745,8 +4548,7 @@ void ScInterpreter::ScSumIf()
pQueryMatrix = PopMatrix();
if (!pQueryMatrix)
{
- PushIllegalParameter();
- return;
+ SetError( errIllegalParameter);
}
nCol1 = 0;
nRow1 = 0;
@@ -4759,13 +4561,11 @@ void ScInterpreter::ScSumIf()
}
break;
default:
- PushIllegalParameter();
- return ;
+ SetError( errIllegalParameter);
}
if ( nTab1 != nTab2 )
{
- PushIllegalArgument();
- return;
+ SetError( errIllegalParameter);
}
if (bSumExtraRange)
@@ -4820,10 +4620,10 @@ void ScInterpreter::ScSumIf()
rParam.nRow2 = nRow2;
ScQueryEntry& rEntry = rParam.GetEntry(0);
- rEntry.bDoQuery = sal_True;
+ rEntry.bDoQuery = true;
if (!bIsString)
{
- rEntry.bQueryByString = sal_False;
+ rEntry.bQueryByString = false;
rEntry.nVal = fVal;
rEntry.eOp = SC_EQUAL;
}
@@ -4851,8 +4651,7 @@ void ScInterpreter::ScSumIf()
ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
if (nGlobalError || !pResultMatrix)
{
- PushIllegalParameter();
- return;
+ SetError( errIllegalParameter);
}
if (pSumExtraMatrix)
@@ -4869,9 +4668,10 @@ void ScInterpreter::ScSumIf()
if (pSumExtraMatrix->IsValue( nC, nR))
{
fVal = pSumExtraMatrix->GetDouble( nC, nR);
+ ++fCount;
if ( bNull && fVal != 0.0 )
{
- bNull = sal_False;
+ bNull = false;
fMem = fVal;
}
else
@@ -4895,9 +4695,10 @@ void ScInterpreter::ScSumIf()
if ( HasCellValueData(pCell) )
{
fVal = GetCellValue( aAdr, pCell );
+ ++fCount;
if ( bNull && fVal != 0.0 )
{
- bNull = sal_False;
+ bNull = false;
fMem = fVal;
}
else
@@ -4910,9 +4711,9 @@ void ScInterpreter::ScSumIf()
}
else
{
- ScQueryCellIterator aCellIter(pDok, nTab1, rParam, sal_False);
+ ScQueryCellIterator aCellIter(pDok, nTab1, rParam, false);
// Increment Entry.nField in iterator when switching to next column.
- aCellIter.SetAdvanceQueryParamEntryField( sal_True );
+ aCellIter.SetAdvanceQueryParamEntryField( true );
if ( aCellIter.GetFirst() )
{
if (pSumExtraMatrix)
@@ -4924,9 +4725,10 @@ void ScInterpreter::ScSumIf()
if (pSumExtraMatrix->IsValue( nC, nR))
{
fVal = pSumExtraMatrix->GetDouble( nC, nR);
+ ++fCount;
if ( bNull && fVal != 0.0 )
{
- bNull = sal_False;
+ bNull = false;
fMem = fVal;
}
else
@@ -4944,9 +4746,10 @@ void ScInterpreter::ScSumIf()
if ( HasCellValueData(pCell) )
{
fVal = GetCellValue( aAdr, pCell );
+ ++fCount;
if ( bNull && fVal != 0.0 )
{
- bNull = sal_False;
+ bNull = false;
fMem = fVal;
}
else
@@ -4959,11 +4762,219 @@ void ScInterpreter::ScSumIf()
}
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" );
+ if ( MustHaveParamCount( GetByte(), 2 ) )
+ {
+ String rString;
+ double fVal = 0.0;
+ sal_Bool bIsString = sal_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 = sal_False;
+ break;
+ case CELLTYPE_FORMULA :
+ if( ((ScFormulaCell*)pCell)->IsValue() )
+ {
+ fVal = GetCellValue( aAdr, pCell );
+ bIsString = sal_False;
+ }
+ else
+ GetCellString(rString, pCell);
+ break;
+ case CELLTYPE_STRING :
+ case CELLTYPE_EDIT :
+ GetCellString(rString, pCell);
+ break;
+ default:
+ fVal = 0.0;
+ bIsString = sal_False;
+ }
+ }
+ break;
+ case svMatrix :
+ {
+ ScMatValType nType = GetDoubleOrStringFromMatrix( fVal, rString);
+ bIsString = ScMatrix::IsNonValueType( nType);
+ }
+ break;
+ case svString:
+ rString = GetString();
+ break;
+ default:
+ {
+ fVal = GetDouble();
+ bIsString = sal_False;
+ }
+ }
+ double fCount = 0.0;
+ 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 svDoubleRef :
+ case svRefList :
+ {
+ ScRange aRange;
+ PopDoubleRef( aRange, nParam, nRefInList);
+ aRange.GetVars( 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 )
+ {
+ PushIllegalParameter();
+ return;
+ }
+ if (nCol1 > nCol2)
+ {
+ PushIllegalParameter();
+ return;
+ }
+ if (nGlobalError == 0)
+ {
+ ScQueryParam rParam;
+ rParam.nRow1 = nRow1;
+ rParam.nRow2 = nRow2;
+
+ ScQueryEntry& rEntry = rParam.GetEntry(0);
+ rEntry.bDoQuery = sal_True;
+ if (!bIsString)
+ {
+ rEntry.bQueryByString = sal_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 );
+ }
+ rParam.nCol1 = nCol1;
+ rParam.nCol2 = nCol2;
+ rEntry.nField = nCol1;
+ if (pQueryMatrix)
+ {
+ // Never case-sensitive.
+ ScCompareOptions aOptions( pDok, rEntry, rParam.bRegExp);
+ ScMatrixRef pResultMatrix = QueryMat( pQueryMatrix, aOptions);
+ if (nGlobalError || !pResultMatrix)
+ {
+ PushIllegalParameter();
+ return;
+ }
+
+ SCSIZE nSize = pResultMatrix->GetElementCount();
+ for (SCSIZE nIndex = 0; nIndex < nSize; ++nIndex)
+ {
+ if (pResultMatrix->IsValue( nIndex) &&
+ pResultMatrix->GetDouble( nIndex))
+ ++fCount;
+ }
+ }
+ else
+ {
+ ScQueryCellIterator aCellIter(pDok, nTab1, rParam, sal_False);
+ // Entry.nField im Iterator bei Spaltenwechsel weiterschalten
+ aCellIter.SetAdvanceQueryParamEntryField( sal_True );
+ if ( aCellIter.GetFirst() )
+ {
+ do
+ {
+ fCount++;
+ } while ( aCellIter.GetNext() );
+ }
+ }
+ }
+ else
+ {
PushIllegalParameter();
return;
}
}
- PushDouble( ::rtl::math::approxAdd( fSum, fMem ) );
+ PushDouble(fCount);
}
}
Modified: incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/tool/interpr4.cxx Thu Sep 6 00:30:41 2012
@@ -3621,6 +3621,7 @@ StackVar ScInterpreter::Interpret()
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;
Modified: incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/tool/parclass.cxx Thu Sep 6 00:30:41 2012
@@ -173,6 +173,7 @@ const ScParameterClassification::RawData
{ 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 }},
Modified: incubator/ooo/trunk/main/sc/source/core/tool/scmatrix.cxx
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/core/tool/scmatrix.cxx?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/core/tool/scmatrix.cxx (original)
+++ incubator/ooo/trunk/main/sc/source/core/tool/scmatrix.cxx Thu Sep 6 00:30:41 2012
@@ -852,4 +852,3 @@ double ScMatrix::Or()
}
return bOr;
}
-
Modified: incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src (original)
+++ incubator/ooo/trunk/main/sc/source/ui/src/scfuncs.src Thu Sep 6 00:30:41 2012
@@ -2656,7 +2656,7 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
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
@@ -2864,6 +2864,47 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS1
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
{
Modified: incubator/ooo/trunk/main/sc/util/hidother.src
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/main/sc/util/hidother.src?rev=1381445&r1=1381444&r2=1381445&view=diff
==============================================================================
--- incubator/ooo/trunk/main/sc/util/hidother.src (original)
+++ incubator/ooo/trunk/main/sc/util/hidother.src Thu Sep 6 00:30:41 2012
@@ -144,9 +144,9 @@ hidspecial HID_FUNC_ISTTEXT { HelpID =
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; };
@@ -154,16 +154,17 @@ hidspecial HID_FUNC_NICHT { HelpID = H
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; };