[Libreoffice-commits] core.git: formula/source include/formula sc/qa sc/source
Winfried Donkers
winfrieddonkers at libreoffice.org
Fri Oct 18 12:04:28 PDT 2013
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(-)
New commits:
commit ccbebd991b6bbce30d10b88f464140338f706c5b
Author: Winfried Donkers <winfrieddonkers at libreoffice.org>
Date: Fri Oct 4 17:56:04 2013 +0200
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 <erack at redhat.com>
Tested-by: Eike Rathke <erack at redhat.com>
diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index 6b1b1a8..d1eb878 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 b53b825..21449b5 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 2df6e79..ae109c5 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 9bba942..bdcbbd0 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 29c722b..304f841 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<double>&
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 36f7fc1..ee30c48 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 4e83602..af6424c 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 8c43739..f293230 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 00965d9..d119a88 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 b054a6d..4d5dd94 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 =
{
More information about the Libreoffice-commits
mailing list