tdf#109096 Allow 0 as argument value(s) to GEOMEAN()
Both ODFF1.2 and the mathematical definition allow 0 as value(s).
Excel does not allow 0 as argument value(s), but IMHO this is to be
regarded as an aberration of Excel.
Also added tests for no arguments or negative arguments, which is not allowed by ODFF1.2.
Change-Id: I330206b6e817e0ec092fe19f713b044578da41d1
Reviewed-on: https://gerrit.libreoffice.org/42107
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Jenkins <ci@libreoffice.org>
diff --git a/sc/qa/unit/data/functions/statistical/fods/geomean.fods b/sc/qa/unit/data/functions/statistical/fods/geomean.fods
index 76b0257..2a8fdf3 100644
--- a/sc/qa/unit/data/functions/statistical/fods/geomean.fods
+++ b/sc/qa/unit/data/functions/statistical/fods/geomean.fods
@@ -3547,13 +3547,13 @@
<table:table-cell table:number-columns-repeated="6"/>
</table:table-row>
<table:table-row table:style-name="ro7">
<table:table-cell table:style-name="ce10" table:formula="of:=GEOMEAN([.I1:.I4])" office:value-type="string" office:string-value="" calcext:value-type="error">
<text:p>Err:502</text:p>
<table:table-cell table:style-name="ce10" table:formula="of:=GEOMEAN([.I1:.I4])" office:value-type="float" office:value="0" calcext:value-type="float">
<text:p>0</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>Err:502</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" table:formula="of:=ISERROR([.A2])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
<table:table-cell table:style-name="ce10" office:value-type="float" office:value="0" calcext:value-type="float">
<text:p>0</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce15" table:formula="of:=ROUND([.A2];12)=ROUND([.B2];12)" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
<text:p>TRUE</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce21" table:formula="of:=FORMULA([.A2])" office:value-type="string" office:string-value="=GEOMEAN(I1:I4)" calcext:value-type="string">
@@ -3699,7 +3699,10 @@
<table:table-cell office:value-type="float" office:value="0.5" calcext:value-type="float">
<text:p>0.5</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce10" table:number-columns-repeated="2"/>
<table:table-cell table:style-name="ce10" office:value-type="float" office:value="-9" calcext:value-type="float">
<text:p>-9</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce10"/>
<table:table-cell table:number-columns-repeated="3"/>
<table:table-cell table:style-name="Gnumeric-default"/>
<table:table-cell table:style-name="ce29"/>
@@ -4558,6 +4561,40 @@
<table:table-cell table:style-name="ce21" table:formula="of:=FORMULA([.A37])" office:value-type="string" office:string-value="=GEOMEAN(J2:K5)" calcext:value-type="string">
<text:p>=GEOMEAN(J2:K5)</text:p>
</table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro7">
<table:table-cell table:style-name="ce10" table:formula="of:=GEOMEAN([.J21:.J25])" office:value-type="string" office:string-value="" calcext:value-type="error">
<text:p>#NUM!</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>#NUM!</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce59" table:formula="of:=ISERROR([.A38])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
<text:p>TRUE</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce21" table:formula="of:=FORMULA([.A38])" office:value-type="string" office:string-value="=GEOMEAN(J21:J25)" calcext:value-type="string">
<text:p>=GEOMEAN(J21:J25)</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>at least 1 value must be given</text:p>
</table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro7">
<table:table-cell table:formula="of:=GEOMEAN()" office:value-type="string" office:string-value="" calcext:value-type="error">
<text:p>#NUM!</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>#NUM!</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce60" table:formula="of:=ISERROR([.A39])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
<text:p>TRUE</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce21" table:formula="of:=FORMULA([.A39])" office:value-type="string" office:string-value="=GEOMEAN()" calcext:value-type="string">
<text:p>=GEOMEAN()</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>at least 1 value must be given</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="17"/>
<table:table-cell table:style-name="ce32"/>
<table:table-cell table:style-name="ce34"/>
@@ -4568,9 +4605,37 @@
<table:table-cell table:style-name="ce25" table:number-columns-repeated="2"/>
<table:table-cell table:number-columns-repeated="2"/>
<table:table-cell table:style-name="ce35"/>
<table:table-cell table:number-columns-repeated="2"/>
</table:table-row>
<table:table-row table:style-name="ro7">
<table:table-cell table:formula="of:=GEOMEAN([.J2:.J6])" office:value-type="string" office:string-value="" calcext:value-type="error">
<text:p>Err:502</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>Err:502</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce61" table:formula="of:=ISERROR([.A40])" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
<text:p>TRUE</text:p>
</table:table-cell>
<table:table-cell table:style-name="ce21" table:formula="of:=FORMULA([.A40])" office:value-type="string" office:string-value="=GEOMEAN(J2:J6)" calcext:value-type="string">
<text:p>=GEOMEAN(J2:J6)</text:p>
</table:table-cell>
<table:table-cell office:value-type="string" calcext:value-type="string">
<text:p>negative values are not allowed</text:p>
</table:table-cell>
<table:table-cell table:number-columns-repeated="16"/>
<table:table-cell table:style-name="ce32"/>
<table:table-cell table:style-name="ce34"/>
<table:table-cell table:style-name="ce25"/>
<table:table-cell/>
<table:table-cell table:style-name="ce25"/>
<table:table-cell/>
<table:table-cell table:style-name="ce25" table:number-columns-repeated="2"/>
<table:table-cell table:number-columns-repeated="2"/>
<table:table-cell table:style-name="ce35"/>
<table:table-cell table:number-columns-repeated="3"/>
</table:table-row>
<table:table-row table:style-name="ro7" table:number-rows-repeated="4">
<table:table-row table:style-name="ro7" table:number-rows-repeated="3">
<table:table-cell table:number-columns-repeated="2"/>
<table:table-cell table:style-name="ce17"/>
<table:table-cell table:style-name="ce21"/>
@@ -5186,4 +5251,4 @@
</table:named-expressions>
</office:spreadsheet>
</office:body>
</office:document>
\ No newline at end of file
</office:document>
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index 7b60e39..e6ae4c2 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -3042,6 +3042,14 @@ void ScInterpreter::ScGeoMean()
nVal += log(x);
nValCount++;
}
else if ( x == 0.0 )
{
// value of 0 means that function result will be 0
while ( nParamCount-- > 0 )
PopError();
PushDouble( 0.0 );
return;
}
else
SetError( FormulaError::IllegalArgument);
break;
@@ -3058,6 +3066,14 @@ void ScInterpreter::ScGeoMean()
nVal += log(x);
nValCount++;
}
else if ( x == 0.0 )
{
// value of 0 means that function result will be 0
while ( nParamCount-- > 0 )
PopError();
PushDouble( 0.0 );
return;
}
else
SetError( FormulaError::IllegalArgument);
}
@@ -3077,6 +3093,14 @@ void ScInterpreter::ScGeoMean()
nVal += log(nCellVal);
nValCount++;
}
else if ( nCellVal == 0.0 )
{
// value of 0 means that function result will be 0
while ( nParamCount-- > 0 )
PopError();
PushDouble( 0.0 );
return;
}
else
SetError( FormulaError::IllegalArgument);
SetError(nErr);
@@ -3087,6 +3111,14 @@ void ScInterpreter::ScGeoMean()
nVal += log(nCellVal);
nValCount++;
}
else if ( nCellVal == 0.0 )
{
// value of 0 means that function result will be 0
while ( nParamCount-- > 0 )
PopError();
PushDouble( 0.0 );
return;
}
else
SetError( FormulaError::IllegalArgument);
}
@@ -3112,6 +3144,14 @@ void ScInterpreter::ScGeoMean()
nVal += log(x);
nValCount++;
}
else if ( x == 0.0 )
{
// value of 0 means that function result will be 0
while ( nParamCount-- > 0 )
PopError();
PushDouble( 0.0 );
return;
}
else
SetError( FormulaError::IllegalArgument);
}
@@ -3119,6 +3159,7 @@ void ScInterpreter::ScGeoMean()
else
{
for (SCSIZE ui = 0; ui < nCount; ui++)
{
if (!pMat->IsString(ui))
{
double x = pMat->GetDouble(ui);
@@ -3127,9 +3168,18 @@ void ScInterpreter::ScGeoMean()
nVal += log(x);
nValCount++;
}
else if ( x == 0.0 )
{
// value of 0 means that function result will be 0
while ( nParamCount-- > 0 )
PopError();
PushDouble( 0.0 );
return;
}
else
SetError( FormulaError::IllegalArgument);
}
}
}
}
}
