sc/qa/unit/data/functions/statistical/fods/geomean.fods | 83 ++++++++++++++--
sc/source/core/tool/interpr3.cxx | 50 +++++++++
2 files changed, 124 insertions(+), 9 deletions(-)
New commits:
commit 65ea925d173db0f319a8ca78855587d089e5270d
Author: Winfried Donkers <winfrieddonk...@libreoffice.org>
Date: Fri Sep 8 16:44:22 2017 +0200
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 <er...@redhat.com>
Tested-by: Jenkins <c...@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 76b0257f9cd7..2a8fdf3fdf1e 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:table-cell office:value-type="string" calcext:value-type="string">
- <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 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 7b60e39dc6f4..e6ae4c2649d8 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);
}
+ }
}
}
}
_______________________________________________
Libreoffice-commits mailing list
libreoffice-comm...@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
