sc/qa/unit/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(-)
New commits: commit c71e8df90b916f32a1d7d3a0849d0cdf14924475 Author: Winfried Donkers <[email protected]> Date: Thu Mar 1 17:34:23 2018 +0100 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 <[email protected]> Reviewed-by: Eike Rathke <[email protected]> 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; _______________________________________________ Libreoffice-commits mailing list [email protected] https://lists.freedesktop.org/mailman/listinfo/libreoffice-commits
