From c71e8df90b916f32a1d7d3a0849d0cdf14924475 Mon Sep 17 00:00:00 2001 From: Winfried Donkers Date: Thu, 1 Mar 2018 17:34:23 +0100 Subject: 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 Reviewed-by: Eike Rathke --- .../data/functions/statistical/fods/mode.sngl.fods | 114 ++++++++++++++++++--- sc/source/core/inc/interpre.hxx | 1 + sc/source/core/tool/interpr3.cxx | 48 ++++++++- sc/source/core/tool/interpr4.cxx | 2 +- 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 @@ - - - - + + 1 + + + 1 + + + TRUE + + + =MODE.SNGL(H1:H12) + 10 @@ -3942,15 +3950,97 @@ - - - - - + + + 4 + + + 4 + + + TRUE + + + =MODE.SNGL(4,2,3,1,1,2,3,4,1,2,3,4) + - - + + + 5 + + + 5 + + + TRUE + + + =MODE.SNGL(5,1,3,4,5,5,1,H1:H4) + + + + + + 1 + + + 1 + + + TRUE + + + =MODE.SNGL(1,2,3,4,1,2,3,4,1,2,3,4,H1:H12) + + + + + + 4 + + + 4 + + + TRUE + + + =MODE.SNGL(4,2,3,1,1,2,3,4,1,2,3,4,H1:H12) + + + + + + 1.00000000000000E+000 + + + 1 + + + TRUE + + + =MODE.SNGL(H1:H12,4,2,3,1,1,2,3,4,1,2,3,4) + + + + + + 2.00000000000000E+000 + + + 2 + + + TRUE + + + =MODE.SNGL(H2,H1,H3:H12,4,2,3,1,1,2,3,4,1,2,3,4) + + + + @@ -4204,4 +4294,4 @@ - \ No newline at end of file + 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& rSortArray, ::std::vector* pIndexOrder, bool bConvertTextInArray, bool bAllowEmptyArray ); static void QuickSort(::std::vector& rSortArray, ::std::vector* 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 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; -- cgit