tdf#97977 related : make MODE.SNGL comply with Excel.

MODE.SNGL is to return the first occurrence of the number that has the
most occurences in the array of numbers in case of multiple
numbers having the same amount of (maximum) occurrences.

Change-Id: If76115ddc6b84367cbcb75de611f29076a538476
Reviewed-on: https://gerrit.libreoffice.org/50593
Tested-by: Jenkins <ci@libreoffice.org>
Reviewed-by: Eike Rathke <erack@redhat.com>

4 files changed, 151 insertions, 14 deletions

diff --git a/sc/qa/unit/data/functions/statistical/fods/mode.sngl.fods b/sc/qa/unit/data/functions/statistical/fods/mode.sngl.fods
index e16f7d92d1a1..1dba7a7fcd3f 100644
--- a/sc/qa/unit/data/functions/statistical/fods/mode.sngl.fods
+++ b/sc/qa/unit/data/functions/statistical/fods/mode.sngl.fods
@@ -3925,10 +3925,18 @@
      <table:table-cell table:number-columns-repeated="6"/>
     </table:table-row>
     <table:table-row table:style-name="ro8">
-     <table:table-cell table:style-name="ce12"/>
-     <table:table-cell/>
-     <table:table-cell table:style-name="ce18"/>
-     <table:table-cell table:style-name="ce24"/>
+     <table:table-cell table:style-name="ce12" table:formula="of:=COM.MICROSOFT.MODE.SNGL([.H1:.H12])" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce33" table:formula="of:=[.A24]=[.B24]" 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="ce24" table:formula="of:=FORMULA([.A24])" office:value-type="string" office:string-value="=MODE.SNGL(H1:H12)" calcext:value-type="string">
+      <text:p>=MODE.SNGL(H1:H12)</text:p>
+     </table:table-cell>
      <table:table-cell table:number-columns-repeated="3"/>
      <table:table-cell office:value-type="float" office:value="10" calcext:value-type="float">
       <text:p>10</text:p>
@@ -3942,15 +3950,97 @@
      </table:table-cell>
      <table:table-cell table:number-columns-repeated="6"/>
     </table:table-row>
-    <table:table-row table:style-name="ro8" table:number-rows-repeated="2">
-     <table:table-cell table:style-name="science"/>
-     <table:table-cell/>
-     <table:table-cell table:style-name="ce18"/>
-     <table:table-cell table:style-name="ce24"/>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:style-name="ce12" table:formula="of:=COM.MICROSOFT.MODE.SNGL(4;2;3;1;1;2;3;4;1;2;3;4)" office:value-type="float" office:value="4" calcext:value-type="float">
+      <text:p>4</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="4" calcext:value-type="float">
+      <text:p>4</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce33" table:formula="of:=[.A25]=[.B25]" 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="ce24" table:formula="of:=FORMULA([.A25])" office:value-type="string" office:string-value="=MODE.SNGL(4,2,3,1,1,2,3,4,1,2,3,4)" calcext:value-type="string">
+      <text:p>=MODE.SNGL(4,2,3,1,1,2,3,4,1,2,3,4)</text:p>
+     </table:table-cell>
      <table:table-cell table:number-columns-repeated="14"/>
     </table:table-row>
-    <table:table-row table:style-name="ro8" table:number-rows-repeated="12">
-     <table:table-cell table:style-name="science"/>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:style-name="ce12" table:formula="of:=COM.MICROSOFT.MODE.SNGL(5;1;3;4;5;5;1;[.H1:.H4])" office:value-type="float" office:value="5" calcext:value-type="float">
+      <text:p>5</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="5" calcext:value-type="float">
+      <text:p>5</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce33" table:formula="of:=[.A26]=[.B26]" 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="ce24" table:formula="of:=FORMULA([.A26])" office:value-type="string" office:string-value="=MODE.SNGL(5,1,3,4,5,5,1,H1:H4)" calcext:value-type="string">
+      <text:p>=MODE.SNGL(5,1,3,4,5,5,1,H1:H4)</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="14"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:style-name="ce12" table:formula="of:=COM.MICROSOFT.MODE.SNGL(1;2;3;4;1;2;3;4;1;2;3;4;[.H1:.H12])" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce33" table:formula="of:=[.A27]=[.B27]" 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="ce24" table:formula="of:=FORMULA([.A27])" office:value-type="string" office:string-value="=MODE.SNGL(1,2,3,4,1,2,3,4,1,2,3,4,H1:H12)" calcext:value-type="string">
+      <text:p>=MODE.SNGL(1,2,3,4,1,2,3,4,1,2,3,4,H1:H12)</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="14"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:style-name="ce12" table:formula="of:=COM.MICROSOFT.MODE.SNGL(4;2;3;1;1;2;3;4;1;2;3;4;[.H1:.H12])" office:value-type="float" office:value="4" calcext:value-type="float">
+      <text:p>4</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="4" calcext:value-type="float">
+      <text:p>4</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce33" table:formula="of:=[.A28]=[.B28]" 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="ce24" table:formula="of:=FORMULA([.A28])" office:value-type="string" office:string-value="=MODE.SNGL(4,2,3,1,1,2,3,4,1,2,3,4,H1:H12)" calcext:value-type="string">
+      <text:p>=MODE.SNGL(4,2,3,1,1,2,3,4,1,2,3,4,H1:H12)</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="14"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:style-name="ce12" table:formula="of:=COM.MICROSOFT.MODE.SNGL([.H1:.H12];4;2;3;1;1;2;3;4;1;2;3;4)" office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1.00000000000000E+000</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="1" calcext:value-type="float">
+      <text:p>1</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce33" table:formula="of:=[.A29]=[.B29]" 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="ce24" table:formula="of:=FORMULA([.A29])" office:value-type="string" office:string-value="=MODE.SNGL(H1:H12,4,2,3,1,1,2,3,4,1,2,3,4)" calcext:value-type="string">
+      <text:p>=MODE.SNGL(H1:H12,4,2,3,1,1,2,3,4,1,2,3,4)</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="14"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro8">
+     <table:table-cell table:style-name="ce12" table:formula="of:=COM.MICROSOFT.MODE.SNGL([.H2];[.H1];[.H3:.H12];4;2;3;1;1;2;3;4;1;2;3;4)" office:value-type="float" office:value="2" calcext:value-type="float">
+      <text:p>2.00000000000000E+000</text:p>
+     </table:table-cell>
+     <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float">
+      <text:p>2</text:p>
+     </table:table-cell>
+     <table:table-cell table:style-name="ce33" table:formula="of:=[.A30]=[.B30]" 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="ce24" table:formula="of:=FORMULA([.A30])" office:value-type="string" office:string-value="=MODE.SNGL(H2,H1,H3:H12,4,2,3,1,1,2,3,4,1,2,3,4)" calcext:value-type="string">
+      <text:p>=MODE.SNGL(H2,H1,H3:H12,4,2,3,1,1,2,3,4,1,2,3,4)</text:p>
+     </table:table-cell>
+     <table:table-cell table:number-columns-repeated="14"/>
+    </table:table-row>
+    <table:table-row table:style-name="ro8" table:number-rows-repeated="8">
      <table:table-cell/>
      <table:table-cell table:style-name="ce19"/>
      <table:table-cell table:style-name="ce24"/>
@@ -4204,4 +4294,4 @@
    </table:named-expressions>
   </office:spreadsheet>
  </office:body>
-</office:document>
\ No newline at end of file
+</office:document>
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 3a3450654ff2..8f68245f99d7 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -932,6 +932,7 @@ private:
     void GetSortArray( sal_uInt8 nParamCount, ::std::vector<double>& rSortArray, ::std::vector<long>* pIndexOrder, bool bConvertTextInArray, bool bAllowEmptyArray );
     static void QuickSort(::std::vector<double>& rSortArray, ::std::vector<long>* pIndexOrder);
     void ScModalValue();
+    void ScModalValue_MS();
     void ScModalValue_Multi();
     void ScAveDev();
     void ScAggregate();
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index edbb01b4e0fa..db91105fcf10 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -3510,7 +3510,6 @@ void ScInterpreter::ScModalValue()
         SCSIZE nMaxIndex = 0, nMax = 1, nCount = 1;
         double nOldVal = aSortArray[0];
         SCSIZE i;
-
         for ( i = 1; i < nSize; i++)
         {
             if (aSortArray[i] == nOldVal)
@@ -3540,6 +3539,53 @@ void ScInterpreter::ScModalValue()
     }
 }
 
+void ScInterpreter::ScModalValue_MS()
+{
+    sal_uInt8 nParamCount = GetByte();
+    if ( !MustHaveParamCountMin( nParamCount, 1 ) )
+        return;
+    vector<double> aArray;
+    GetNumberSequenceArray( nParamCount, aArray, false );
+    SCSIZE nSize = aArray.size();
+    if ( nSize == 0 || nGlobalError != FormulaError::NONE )
+        PushNoValue();
+    else
+    {
+        SCSIZE nMaxIndex = 0, nMax = 1, nCount = 1, i, j;
+        double nOldVal = aArray[ 0 ];
+
+        for ( i = 1; i < nSize ; i++ )
+        {
+            for ( j = i; j < nSize; j++ )
+            {
+                if ( aArray[ j ] == nOldVal )
+                    nCount++;
+            }
+            if ( nCount > nMax )
+            {
+                nMax = nCount;
+                nMaxIndex = i - 1;
+                nCount = 1;
+            }
+            while ( nOldVal == aArray[ i ] && i < nSize - 1 )
+                i++;
+            if ( ( nSize - i ) > nMax )
+            {
+                nOldVal = aArray[ i ];
+                nCount = 1;
+            }
+            else
+                break;
+        }
+        if ( nMax == 1 && nCount == 1 )
+            PushNoValue();
+        else if ( nMax == 1 )
+            PushDouble( nOldVal );
+        else
+            PushDouble( aArray[ nMaxIndex ] );
+    }
+}
+
 void ScInterpreter::CalculateSmallLarge(bool bSmall)
 {
     if ( !MustHaveParamCount( GetByte(), 2 )  )
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 727976c14655..f3e855f04828 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4327,7 +4327,7 @@ StackVar ScInterpreter::Interpret()
                 case ocSkew             : ScSkew();                     break;
                 case ocSkewp            : ScSkewp();                    break;
                 case ocModalValue       : ScModalValue();               break;
-                case ocModalValue_MS    : ScModalValue();               break;
+                case ocModalValue_MS    : ScModalValue_MS();            break;
                 case ocModalValue_Multi : ScModalValue_Multi();         break;
                 case ocMedian           : ScMedian();                   break;
                 case ocGeoMean          : ScGeoMean();                  break;