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>

2 files changed, 124 insertions, 9 deletions

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);
                             }
+                        }
                     }
                 }
             }