tdf#97977 make MODE.MULT fully compatible with Excel.

Also, improve efficiency of function.

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

5 files changed, 81 insertions, 106 deletions

diff --git a/sc/qa/unit/data/functions/statistical/fods/mode.mult.fods b/sc/qa/unit/data/functions/statistical/fods/mode.mult.fods
index e26bd6673d3a..23a631162d52 100644
--- a/sc/qa/unit/data/functions/statistical/fods/mode.mult.fods
+++ b/sc/qa/unit/data/functions/statistical/fods/mode.mult.fods
@@ -3416,8 +3416,8 @@
       <text:p>1</text:p>
      </table:table-cell>
      <table:table-cell/>
-     <table:table-cell table:style-name="ce12" office:value-type="float" office:value="1" calcext:value-type="float">
-      <text:p>1</text:p>
+     <table:table-cell table:style-name="ce12" 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="ce30" table:number-columns-repeated="2"/>
     </table:table-row>
@@ -3443,8 +3443,8 @@
      </table:table-cell>
      <table:table-cell table:style-name="ce30" table:number-columns-repeated="2"/>
      <table:table-cell table:number-columns-repeated="4"/>
-     <table:table-cell table:style-name="ce12" office:value-type="float" office:value="2" calcext:value-type="float">
-      <text:p>2</text:p>
+     <table:table-cell table:style-name="ce12" 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="ce30" table:number-columns-repeated="2"/>
     </table:table-row>
@@ -3669,11 +3669,11 @@
      <table:table-cell table:style-name="ce30" table:number-columns-repeated="2"/>
     </table:table-row>
     <table:table-row table:style-name="ro8">
-     <table:table-cell table:number-matrix-columns-spanned="1" table:number-matrix-rows-spanned="3" table:formula="of:=COM.MICROSOFT.MODE.MULT([.P2:.P13])" office:value-type="float" office:value="1" calcext:value-type="float">
-      <text:p>1</text:p>
+     <table:table-cell table:number-matrix-columns-spanned="1" table:number-matrix-rows-spanned="3" table:formula="of:=COM.MICROSOFT.MODE.MULT([.P2:.P13])" office:value-type="float" office:value="2" calcext:value-type="float">
+      <text:p>2</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 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="ce18" table:formula="of:=[.A13]=[.B13]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
       <text:p>TRUE</text:p>
@@ -3688,11 +3688,11 @@
      <table:table-cell table:style-name="ce30" table:number-columns-repeated="2"/>
     </table:table-row>
     <table:table-row table:style-name="ro8">
-     <table:table-cell office:value-type="float" office:value="2" calcext:value-type="float">
-      <text:p>2</text:p>
+     <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 office:value-type="float" office:value="2" calcext:value-type="float">
-      <text:p>2</text:p>
+     <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="ce18" table:formula="of:=[.A14]=[.B14]" office:value-type="boolean" office:boolean-value="true" calcext:value-type="boolean">
       <text:p>TRUE</text:p>
@@ -3780,7 +3780,7 @@
       <text:p>{=MODE.MULT(J15:L17)}</text:p>
      </table:table-cell>
      <table:table-cell table:style-name="ce12" office:value-type="string" calcext:value-type="string">
-      <text:p>Calc returns the result array as a sorted array and Excel does not. </text:p>
+      <text:p> </text:p>
      </table:table-cell>
      <table:table-cell table:number-columns-repeated="2"/>
      <table:table-cell office:value-type="float" office:value="3" calcext:value-type="float">
@@ -4210,4 +4210,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 71bf46a80c50..e7d7f5e2aff8 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -932,8 +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 ScModalValue_MS( bool bSingle );
     void ScAveDev();
     void ScAggregate();
     void ScDevSq();
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index b66a27706a68..a33470ec9c30 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -3539,50 +3539,89 @@ void ScInterpreter::ScModalValue()
     }
 }
 
-void ScInterpreter::ScModalValue_MS()
+void ScInterpreter::ScModalValue_MS( bool bSingle )
 {
     sal_uInt8 nParamCount = GetByte();
     if ( !MustHaveParamCountMin( nParamCount, 1 ) )
         return;
     vector<double> aArray;
     GetNumberSequenceArray( nParamCount, aArray, false );
-    SCSIZE nSize = aArray.size();
+    vector< double > aSortArray( aArray );
+    QuickSort( aSortArray, nullptr );
+    SCSIZE nSize = aSortArray.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++ )
+        SCSIZE nMax = 1, nCount = 1;
+        double nOldVal = aSortArray[ 0 ];
+        vector< double > aResultArray( 1 );
+        SCSIZE i;
+        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 ( (i < (nSize - 1)) && (nOldVal == aArray[ i ]) )
-                i++;
-            if ( ( nSize - i ) > nMax )
+            if ( aSortArray[ i ] == nOldVal )
+                nCount++;
+            else
             {
-                nOldVal = aArray[ i ];
+                if ( nCount >= nMax && nCount > 1 )
+                {
+                    if ( nCount > nMax )
+                    {
+                        nMax = nCount;
+                        if ( aResultArray.size() != 1 )
+                            vector< double >( 1 ).swap( aResultArray );
+                        aResultArray[ 0 ] = nOldVal;
+                    }
+                    else
+                        aResultArray.emplace_back( nOldVal );
+                }
+                nOldVal = aSortArray[ i ];
                 nCount = 1;
             }
-            else
-                break;
+        }
+        if ( nCount >= nMax && nCount > 1 )
+        {
+            if ( nCount > nMax )
+                vector< double >().swap( aResultArray );
+            aResultArray.emplace_back( nOldVal );
         }
         if ( nMax == 1 && nCount == 1 )
             PushNoValue();
         else if ( nMax == 1 )
-            PushDouble( nOldVal );
+            PushDouble( nOldVal ); // there is only 1 result, no reordering needed
         else
-            PushDouble( aArray[ nMaxIndex ] );
+        {
+            // sort resultArray according to ordering of aArray
+            vector< vector< double > > aOrder;
+            aOrder.resize( aResultArray.size(), vector< double >( 2 ) );
+            for ( i = 0; i < aResultArray.size(); i++ )
+            {
+                for ( SCSIZE j = 0; j < nSize; j++ )
+                {
+                    if ( aArray[ j ] == aResultArray[ i ] )
+                    {
+                        aOrder[ i ][ 0 ] = aResultArray[ i ];
+                        aOrder[ i ][ 1 ] = j;
+                        break;
+                    }
+                }
+            }
+            sort( aOrder.begin(), aOrder.end(), []( const std::vector< double >& lhs,
+                                                    const std::vector< double >& rhs )
+                                                    { return lhs[ 1 ] < rhs[ 1 ]; } );
+
+            if ( bSingle )
+                PushDouble( aOrder[ 0 ][ 0 ] );
+            else
+            {
+                // put result in correct order in aResultArray
+                for ( i = 0; i < aResultArray.size(); i++ )
+                    aResultArray[ i ] = aOrder[ i ][ 0 ];
+                ScMatrixRef pResMatrix = GetNewMat( 1, aResultArray.size(), true );
+                pResMatrix->PutDoubleVector( aResultArray, 0, 0 );
+                PushMatrix( pResMatrix );
+            }
+        }
     }
 }
 
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index b6f55e7bb7bc..483c1e618065 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4326,8 +4326,8 @@ StackVar ScInterpreter::Interpret()
                 case ocSkew             : ScSkew();                     break;
                 case ocSkewp            : ScSkewp();                    break;
                 case ocModalValue       : ScModalValue();               break;
-                case ocModalValue_MS    : ScModalValue_MS();            break;
-                case ocModalValue_Multi : ScModalValue_Multi();         break;
+                case ocModalValue_MS    : ScModalValue_MS( true );      break;
+                case ocModalValue_Multi : ScModalValue_MS( false );     break;
                 case ocMedian           : ScMedian();                   break;
                 case ocGeoMean          : ScGeoMean();                  break;
                 case ocHarMean          : ScHarMean();                  break;
diff --git a/sc/source/core/tool/interpr5.cxx b/sc/source/core/tool/interpr5.cxx
index 1e61811ab997..739be97c0176 100644
--- a/sc/source/core/tool/interpr5.cxx
+++ b/sc/source/core/tool/interpr5.cxx
@@ -837,69 +837,6 @@ void ScInterpreter::ScMatDet()
     }
 }
 
-void ScInterpreter::ScModalValue_Multi()
-{
-    sal_uInt8 nParamCount = GetByte();
-    if ( !MustHaveParamCountMin( nParamCount, 1 ) )
-        return;
-    vector<double> aSortArray;
-    GetSortArray( nParamCount, aSortArray, nullptr, false, false );
-    SCSIZE nSize = aSortArray.size();
-    if ( aSortArray.empty() || nSize == 0 || nGlobalError != FormulaError::NONE )
-        PushNoValue();
-    else
-    {
-        SCSIZE nMax = 1, nCount = 1;
-        double nOldVal = aSortArray[0];
-        vector<double> aResultArray;
-        aResultArray.resize( 1 );
-        aResultArray[ 0 ] = aSortArray[ 0 ];
-        SCSIZE i;
-
-        for ( i = 1; i < nSize; i++ )
-        {
-            if ( aSortArray[ i ] == nOldVal )
-            {
-                nCount++;
-                if ( nCount > nMax && aResultArray.size() > 1 )
-                {
-                    aResultArray.clear();
-                    aResultArray.resize( 1 );
-                    aResultArray[ 0 ] = nOldVal;
-                }
-            }
-            else
-            {
-                nOldVal = aSortArray[ i ];
-                if ( nCount >= nMax )
-                {
-                    if ( nCount > nMax )
-                        nMax = nCount;
-                    aResultArray.emplace_back();
-                }
-                aResultArray[ aResultArray.size() -1  ] = nOldVal;
-                nCount = 1;
-            }
-        }
-        if ( nCount > nMax )
-            nMax = nCount;
-        else
-        {
-            if ( nCount < nMax )
-                aResultArray.resize( aResultArray.size() - 1 );
-        }
-
-        if ( nMax == 1 && nCount == 1 )
-            PushNoValue();
-        else
-        {
-            ScMatrixRef pResMatrix = GetNewMat( 1, aResultArray.size(), true );
-            pResMatrix->PutDoubleVector( aResultArray, 0, 0 );
-            PushMatrix( pResMatrix );
-        }
-    }
-}
-
 void ScInterpreter::ScMatInv()
 {
     if ( MustHaveParamCount( GetByte(), 1 ) )