From ccbebd991b6bbce30d10b88f464140338f706c5b Mon Sep 17 00:00:00 2001 From: Winfried Donkers Date: Fri, 4 Oct 2013 17:56:04 +0200 Subject: fdo#70000 add support for COVARIANCE.P and COVARIANCE.S functions These functions have been introduced with Excel 2010, but were not supported yet in calc. COVARIANCE.P (population) replaces the COVAR function, but the COVAR function remains present (in Excel as well as in calc). COVARIANCE.S (sample) is a new function. Change-Id: If5501b4090fb716adfb3d121c7898528fd1b7ad4 Reviewed-on: https://gerrit.libreoffice.org/6135 Reviewed-by: Eike Rathke Tested-by: Eike Rathke --- formula/source/core/resource/core_resource.src | 12 +++++ include/formula/compiler.hrc | 4 +- include/formula/opcode.hxx | 2 + sc/qa/unit/ucalc.cxx | 2 + sc/source/core/inc/interpre.hxx | 5 +- sc/source/core/tool/interpr3.cxx | 21 +++++--- sc/source/core/tool/interpr4.cxx | 4 +- sc/source/filter/excel/xlformula.cxx | 18 +++++++ sc/source/filter/oox/formulabase.cxx | 24 ++++++++++ sc/source/ui/src/scfuncs.src | 66 +++++++++++++++++++++++++- 10 files changed, 147 insertions(+), 11 deletions(-) diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src index 6b1b1a848aaa..d1eb878e87e6 100644 --- a/formula/source/core/resource/core_resource.src +++ b/formula/source/core/resource/core_resource.src @@ -309,6 +309,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF String SC_OPCODE_PROB { Text = "PROB" ; }; String SC_OPCODE_CORREL { Text = "CORREL" ; }; String SC_OPCODE_COVAR { Text = "COVAR" ; }; + String SC_OPCODE_COVARIANCE_P { Text = "COM.MICROSOFT.COVARIANCE.P" ; }; + String SC_OPCODE_COVARIANCE_S { Text = "COM.MICROSOFT.COVARIANCE.S" ; }; String SC_OPCODE_PEARSON { Text = "PEARSON" ; }; String SC_OPCODE_RSQ { Text = "RSQ" ; }; String SC_OPCODE_STEYX { Text = "STEYX" ; }; @@ -657,6 +659,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH String SC_OPCODE_PROB { Text = "PROB" ; }; String SC_OPCODE_CORREL { Text = "CORREL" ; }; String SC_OPCODE_COVAR { Text = "COVAR" ; }; + String SC_OPCODE_COVARIANCE_P { Text = "COVARIANCE.P" ; }; + String SC_OPCODE_COVARIANCE_S { Text = "COVARIANCE.S" ; }; String SC_OPCODE_PEARSON { Text = "PEARSON" ; }; String SC_OPCODE_RSQ { Text = "RSQ" ; }; String SC_OPCODE_STEYX { Text = "STEYX" ; }; @@ -1788,6 +1792,14 @@ Resource RID_STRLIST_FUNCTION_NAMES { Text [ en-US ] = "COVAR" ; }; + String SC_OPCODE_COVARIANCE_P + { + Text [ en-US ] = "COVARIANCE.P" ; + }; + String SC_OPCODE_COVARIANCE_S + { + Text [ en-US ] = "COVARIANCE.S" ; + }; String SC_OPCODE_PEARSON { Text [ en-US ] = "PEARSON" ; diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc index b53b8259ff73..21449b536302 100644 --- a/include/formula/compiler.hrc +++ b/include/formula/compiler.hrc @@ -408,8 +408,10 @@ #define SC_OPCODE_MIDB 410 #define SC_OPCODE_FILTERXML 411 #define SC_OPCODE_WEBSERVICE 412 +#define SC_OPCODE_COVARIANCE_S 413 +#define SC_OPCODE_COVARIANCE_P 414 -#define SC_OPCODE_STOP_2_PAR 413 /* last function with two or more parameters' OpCode + 1 */ +#define SC_OPCODE_STOP_2_PAR 415 /* last function with two or more parameters' OpCode + 1 */ #define SC_OPCODE_STOP_FUNCTION SC_OPCODE_STOP_2_PAR /* last function's OpCode + 1 */ #define SC_OPCODE_LAST_OPCODE_ID (SC_OPCODE_STOP_FUNCTION - 1) /* last OpCode */ diff --git a/include/formula/opcode.hxx b/include/formula/opcode.hxx index 2df6e793942c..ae109c5544d4 100644 --- a/include/formula/opcode.hxx +++ b/include/formula/opcode.hxx @@ -352,6 +352,8 @@ enum OpCodeEnum ocProb = SC_OPCODE_PROB, ocCorrel = SC_OPCODE_CORREL, ocCovar = SC_OPCODE_COVAR, + ocCovarianceP = SC_OPCODE_COVARIANCE_P, + ocCovarianceS = SC_OPCODE_COVARIANCE_S, ocPearson = SC_OPCODE_PEARSON, ocRSQ = SC_OPCODE_RSQ, ocSTEYX = SC_OPCODE_STEYX, diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx index 9bba942f429a..bdcbbd07b464 100644 --- a/sc/qa/unit/ucalc.cxx +++ b/sc/qa/unit/ucalc.cxx @@ -2355,6 +2355,8 @@ void Test::testFunctionLists() "COUNTIF", "COUNTIFS", "COVAR", + "COVARIANCE.P", + "COVARIANCE.S", "CRITBINOM", "DEVSQ", "EXPONDIST", diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx index 29c722bdfea5..304f8415bc6c 100644 --- a/sc/source/core/inc/interpre.hxx +++ b/sc/source/core/inc/interpre.hxx @@ -690,7 +690,7 @@ bool CalculateSkew(double& fSum,double& fCount,double& vSum,std::vector& void CalculateSkewOrSkewp( bool bSkewp ); void CalculateSlopeIntercept(bool bSlope); void CalculateSmallLarge(bool bSmall); -void CalculatePearsonCovar(bool _bPearson,bool _bStexy); +void CalculatePearsonCovar( bool _bPearson, bool _bStexy, bool _bSample ); //fdo#70000 argument _bSample is ignored if _bPearson == true bool CalculateTest( bool _bTemplin ,const SCSIZE nC1, const SCSIZE nC2,const SCSIZE nR1,const SCSIZE nR2 ,const ScMatrixRef& pMat1,const ScMatrixRef& pMat2 @@ -795,7 +795,8 @@ void ScConfidence(); void ScTrimMean(); void ScProbability(); void ScCorrel(); -void ScCovar(); +void ScCovarianceP(); +void ScCovarianceS(); void ScPearson(); void ScRSQ(); void ScSTEXY(); diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx index 36f7fc1f38d9..ee30c4869b9d 100644 --- a/sc/source/core/tool/interpr3.cxx +++ b/sc/source/core/tool/interpr3.cxx @@ -3790,16 +3790,22 @@ void ScInterpreter::ScCorrel() ScPearson(); } -void ScInterpreter::ScCovar() +void ScInterpreter::ScCovarianceP() { - CalculatePearsonCovar(false,false); + CalculatePearsonCovar( false, false, false ); +} + +void ScInterpreter::ScCovarianceS() +{ + CalculatePearsonCovar( false, false, true ); } void ScInterpreter::ScPearson() { - CalculatePearsonCovar(true,false); + CalculatePearsonCovar( true, false, false ); } -void ScInterpreter::CalculatePearsonCovar(bool _bPearson,bool _bStexy) + +void ScInterpreter::CalculatePearsonCovar( bool _bPearson, bool _bStexy, bool _bSample ) { if ( !MustHaveParamCount( GetByte(), 2 ) ) return; @@ -3879,7 +3885,10 @@ void ScInterpreter::CalculatePearsonCovar(bool _bPearson,bool _bStexy) } // if ( _bPearson ) else { - PushDouble( fSumDeltaXDeltaY / fCount); + if ( _bSample ) + PushDouble( fSumDeltaXDeltaY / ( fCount - 1 ) ); + else + PushDouble( fSumDeltaXDeltaY / fCount); } } } @@ -3907,7 +3916,7 @@ void ScInterpreter::ScRSQ() void ScInterpreter::ScSTEXY() { - CalculatePearsonCovar(true,true); + CalculatePearsonCovar( true, true, false ); } void ScInterpreter::CalculateSlopeIntercept(bool bSlope) { diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx index 4e836026a4c2..af6424c01289 100644 --- a/sc/source/core/tool/interpr4.cxx +++ b/sc/source/core/tool/interpr4.cxx @@ -4139,7 +4139,9 @@ StackVar ScInterpreter::Interpret() case ocTrimMean : ScTrimMean(); break; case ocProb : ScProbability(); break; case ocCorrel : ScCorrel(); break; - case ocCovar : ScCovar(); break; + case ocCovar : + case ocCovarianceP : ScCovarianceP(); break; + case ocCovarianceS : ScCovarianceS(); break; case ocPearson : ScPearson(); break; case ocRSQ : ScRSQ(); break; case ocSTEYX : ScSTEXY(); break; diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx index 8c4373941d43..f293230c4885 100644 --- a/sc/source/filter/excel/xlformula.cxx +++ b/sc/source/filter/excel/xlformula.cxx @@ -394,6 +394,23 @@ static const XclFunctionInfo saFuncTable_Oox[] = { opcode, NOID, minparam, MX, V, { RX }, EXC_FUNCFLAG_IMPORTONLY|(flags), EXC_FUNCNAME( asciiname ) }, \ { opcode, 255, (minparam)+1, MX, V, { RO_E, RX }, EXC_FUNCFLAG_EXPORTONLY|(flags), EXC_FUNCNAME( asciiname ) } +#define EXC_FUNCENTRY_V_VA( opcode, minparam, maxparam, flags, asciiname ) \ + { opcode, NOID, minparam, maxparam, V, { VA }, EXC_FUNCFLAG_IMPORTONLY|(flags), EXC_FUNCNAME( asciiname ) }, \ + { opcode, 255, (minparam)+1, (maxparam)+1, V, { RO_E, RO }, EXC_FUNCFLAG_EXPORTONLY|(flags), EXC_FUNCNAME( asciiname ) } + +/** Functions new in Excel 2010. + + See http://office.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx + A lot of statistical functions have been renamed (the 'old' function names still exist). + + @See sc/source/filter/oox/formulabase.cxx saFuncTable2010 for V,VR,RO,... + */ +static const XclFunctionInfo saFuncTable_2010[] = +{ + EXC_FUNCENTRY_V_VA( ocCovarianceP, 2, 2, 0, "COVARIANCE.P" ), + EXC_FUNCENTRY_V_VA( ocCovarianceS, 2, 2, 0, "COVARIANCE.S" ) +}; + /** Functions new in Excel 2013. See http://office.microsoft.com/en-us/excel-help/new-functions-in-excel-2013-HA103980604.aspx @@ -505,6 +522,7 @@ XclFunctionProvider::XclFunctionProvider( const XclRoot& rRoot ) if( eBiff >= EXC_BIFF8 ) (this->*pFillFunc)( saFuncTable_8, STATIC_ARRAY_END( saFuncTable_8 ) ); (this->*pFillFunc)( saFuncTable_Oox, STATIC_ARRAY_END( saFuncTable_Oox ) ); + (this->*pFillFunc)( saFuncTable_2010, STATIC_ARRAY_END( saFuncTable_2010 ) ); (this->*pFillFunc)( saFuncTable_2013, STATIC_ARRAY_END( saFuncTable_2013 ) ); (this->*pFillFunc)( saFuncTable_Odf, STATIC_ARRAY_END( saFuncTable_Odf ) ); } diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx index 00965d9ee3c9..d119a88142fc 100644 --- a/sc/source/filter/oox/formulabase.cxx +++ b/sc/source/filter/oox/formulabase.cxx @@ -727,6 +727,29 @@ static const FunctionData saFuncTableOox[] = { "AVERAGEIFS", "AVERAGEIFS", 484, NOID, 3, MX, V, { RO, RO, VR }, FUNCFLAG_MACROCALL | FUNCFLAG_PARAMPAIRS } }; +/** Functions new in Excel 2010. + + A lot of statistical functions have been renamed (although the 'old' function name still is valid). + See http://office.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx + + Functions with FUNCFLAG_IMPORTONLY are rewritten in + sc/source/filter/excel/xeformula.cxx during export for + BIFF, OOXML export uses this different mapping here but still uses the + mapping there to determine the feature set. + + FIXME: either have the exporter determine the feature set from the active + mapping, preferred, or enhance that mapping there such that for OOXML the + rewrite can be overridden. + + @See sc/source/filter/excel/xlformula.cxx saFuncTable_2010 + */ +/* FIXME: BIFF12 function identifiers available? Where to obtain? */ +static const FunctionData saFuncTable2010[] = +{ + { "COM.MICROSOFT.COVARIANCE.P", "COVARIANCE.P", NOID, NOID, 2, 2, V, { VA }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.COVARIANCE.S", "COVARIANCE.S", NOID, NOID, 2, 2, V, { VA }, FUNCFLAG_MACROCALL_NEW } +}; + /** Functions new in Excel 2013. See http://office.microsoft.com/en-us/excel-help/new-functions-in-excel-2013-HA103980604.aspx @@ -957,6 +980,7 @@ FunctionProviderImpl::FunctionProviderImpl( FilterType eFilter, BiffType eBiff, if( eBiff >= BIFF8 ) initFuncs( saFuncTableBiff8, STATIC_ARRAY_END( saFuncTableBiff8 ), nMaxParam, bImportFilter, eFilter ); initFuncs( saFuncTableOox, STATIC_ARRAY_END( saFuncTableOox ), nMaxParam, bImportFilter, eFilter ); + initFuncs( saFuncTable2010, STATIC_ARRAY_END( saFuncTable2010 ), nMaxParam, bImportFilter, eFilter ); initFuncs( saFuncTable2013, STATIC_ARRAY_END( saFuncTable2013 ), nMaxParam, bImportFilter, eFilter ); initFuncs( saFuncTableOdf, STATIC_ARRAY_END( saFuncTableOdf ), nMaxParam, bImportFilter, eFilter ); initFuncs( saFuncTableOOoLO, STATIC_ARRAY_END( saFuncTableOOoLO ), nMaxParam, bImportFilter, eFilter ); diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src index b054a6d37b43..4d5dd944fea5 100644 --- a/sc/source/ui/src/scfuncs.src +++ b/sc/source/ui/src/scfuncs.src @@ -7515,7 +7515,71 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2 { String 1 // Description { - Text [ en-US ] = "Calculates the covariance." ; + Text [ en-US ] = "Calculates the population covariance." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_KOVAR ); + 2; 0; 0; + 0; + }; + String 2 // Name of Parameter 1 + { + Text [ en-US ] = "Data_1" ; + }; + String 3 // Description of Parameter 1 + { + Text [ en-US ] = "The first record array." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "Data_2" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The second record array." ; + }; + }; + // -=*# Resource for function COVARIANCE.P #*=- + Resource SC_OPCODE_COVARIANCE_P + { + String 1 // Description + { + Text [ en-US ] = "Calculates the population covariance." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_KOVAR ); + 2; 0; 0; + 0; + }; + String 2 // Name of Parameter 1 + { + Text [ en-US ] = "Data_1" ; + }; + String 3 // Description of Parameter 1 + { + Text [ en-US ] = "The first record array." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "Data_2" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The second record array." ; + }; + }; + // -=*# Resource for function COVARIANCE.S #*=- + Resource SC_OPCODE_COVARIANCE_S + { + String 1 // Description + { + Text [ en-US ] = "Calculates the sample covariance." ; }; ExtraData = { -- cgit v1.2.3