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@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

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<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 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 =
         {