fdo#71350 add Excel 2010 function CONFIDENCE.NORM and CONFIDENCE.T

Change-Id: I341b0c7a61047627aac1f12bc4653b6f9e65b2e3
Reviewed-on: https://gerrit.libreoffice.org/6625
Reviewed-by: Eike Rathke <erack@redhat.com>
Tested-by: Eike Rathke <erack@redhat.com>

11 files changed, 135 insertions, 7 deletions

diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index 0773c80a0d9b..536de400c235 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -311,6 +311,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
     String SC_OPCODE_QUARTILE { Text = "QUARTILE" ; };
     String SC_OPCODE_NORM_INV { Text = "NORMINV" ; };
     String SC_OPCODE_CONFIDENCE { Text = "CONFIDENCE" ; };
+    String SC_OPCODE_CONFIDENCE_N { Text = "COM.MICROSOFT.CONFIDENCE.NORM" ; };
+    String SC_OPCODE_CONFIDENCE_T { Text = "COM.MICROSOFT.CONFIDENCE.T" ; };
     String SC_OPCODE_F_TEST { Text = "FTEST" ; };
     String SC_OPCODE_TRIM_MEAN { Text = "TRIMMEAN" ; };
     String SC_OPCODE_PROB { Text = "PROB" ; };
@@ -672,6 +674,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML
     String SC_OPCODE_QUARTILE { Text = "QUARTILE" ; };
     String SC_OPCODE_NORM_INV { Text = "NORMINV" ; };
     String SC_OPCODE_CONFIDENCE { Text = "CONFIDENCE" ; };
+    String SC_OPCODE_CONFIDENCE_N { Text = "_xlfn.CONFIDENCE.NORM" ; };
+    String SC_OPCODE_CONFIDENCE_T { Text = "_xlfn.CONFIDENCE.T" ; };
     String SC_OPCODE_F_TEST { Text = "FTEST" ; };
     String SC_OPCODE_TRIM_MEAN { Text = "TRIMMEAN" ; };
     String SC_OPCODE_PROB { Text = "PROB" ; };
@@ -1035,6 +1039,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
     String SC_OPCODE_QUARTILE { Text = "QUARTILE" ; };
     String SC_OPCODE_NORM_INV { Text = "NORMINV" ; };
     String SC_OPCODE_CONFIDENCE { Text = "CONFIDENCE" ; };
+    String SC_OPCODE_CONFIDENCE_N { Text = "CONFIDENCE.NORM" ; };
+    String SC_OPCODE_CONFIDENCE_T { Text = "CONFIDENCE.T" ; };
     String SC_OPCODE_F_TEST { Text = "FTEST" ; };
     String SC_OPCODE_TRIM_MEAN { Text = "TRIMMEAN" ; };
     String SC_OPCODE_PROB { Text = "PROB" ; };
@@ -2187,6 +2193,14 @@ Resource RID_STRLIST_FUNCTION_NAMES
     {
         Text [ en-US ] = "CONFIDENCE" ;
     };
+    String SC_OPCODE_CONFIDENCE_N
+    {
+        Text [ en-US ] = "CONFIDENCE.NORM" ;
+    };
+    String SC_OPCODE_CONFIDENCE_T
+    {
+        Text [ en-US ] = "CONFIDENCE.T" ;
+    };
     String SC_OPCODE_F_TEST
     {
         Text [ en-US ] = "FTEST" ;
diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc
index 92a4cfa3c67c..1cf1c2d44f2f 100644
--- a/include/formula/compiler.hrc
+++ b/include/formula/compiler.hrc
@@ -423,8 +423,10 @@
 #define SC_OPCODE_CHI_TEST_MS       425
 #define SC_OPCODE_CHISQ_DIST_MS     426
 #define SC_OPCODE_CHISQ_INV_MS      427
+#define SC_OPCODE_CONFIDENCE_N      428
+#define SC_OPCODE_CONFIDENCE_T      429
 
-#define SC_OPCODE_STOP_2_PAR        428     /* last function with two or more parameters' OpCode + 1 */
+#define SC_OPCODE_STOP_2_PAR        430     /* 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 97298cef9c49..75fdb9210972 100644
--- a/include/formula/opcode.hxx
+++ b/include/formula/opcode.hxx
@@ -356,6 +356,8 @@ enum OpCodeEnum
         ocQuartile          = SC_OPCODE_QUARTILE,
         ocNormInv           = SC_OPCODE_NORM_INV,
         ocConfidence        = SC_OPCODE_CONFIDENCE,
+        ocConfidence_N      = SC_OPCODE_CONFIDENCE_N,
+        ocConfidence_T      = SC_OPCODE_CONFIDENCE_T,
         ocFTest             = SC_OPCODE_F_TEST,
         ocTrimMean          = SC_OPCODE_TRIM_MEAN,
         ocProb              = SC_OPCODE_PROB,
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index 71bb83ebb077..148f6de40e5c 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -693,5 +693,7 @@
 #define HID_FUNC_CHITEST_MS                                     "SC_HID_FUNC_CHITEST_MS"
 #define HID_FUNC_CHISQDIST_MS                                   "SC_HID_FUNC_CHISQDIST_MS"
 #define HID_FUNC_CHISQINV_MS                                    "SC_HID_FUNC_CHISQINV_MS"
+#define HID_FUNC_CONFIDENCE_N                                   "SC_HID_FUNC_CONFIDENCE_N"
+#define HID_FUNC_CONFIDENCE_T                                   "SC_HID_FUNC_CONFIDENCE_T"
 
 /* vim:set shiftwidth=4 softtabstop=4 expandtab: */
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index 3c9cd72ee2b7..294dd6597338 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -2375,6 +2375,8 @@ void Test::testFunctionLists()
         "CHISQINV",
         "CHITEST",
         "CONFIDENCE",
+        "CONFIDENCE.NORM",
+        "CONFIDENCE.T",
         "CORREL",
         "COUNT",
         "COUNTA",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index d5305d1aec31..813fb1b13b90 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -790,6 +790,7 @@ void ScQuartile();
 void ScNormInv();
 void ScSNormInv();
 void ScConfidence();
+void ScConfidenceT();
 void ScTrimMean();
 void ScProbability();
 void ScCorrel();
@@ -819,6 +820,7 @@ double GetUpRegIGamma(double fA,double fX);     // upper regularized incomplete
 double GetGammaDistPDF(double fX, double fAlpha, double fLambda);
 // cumulative distribution function; fLambda is "scale" parameter
 double GetGammaDist(double fX, double fAlpha, double fLambda);
+double GetTInv( double fAlpha, double fSize );
 
 public:
     ScInterpreter( ScFormulaCell* pCell, ScDocument* pDoc,
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index 3f665f30c7e4..6272792be0a5 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -2120,13 +2120,17 @@ void ScInterpreter::ScTInv()
         PushIllegalArgument();
         return;
     }
+    PushDouble( GetTInv( fP, fDF ) );
+};
 
+double ScInterpreter::GetTInv( double fAlpha, double fSize )
+{
     bool bConvError;
-    ScTDistFunction aFunc( *this, fP, fDF );
-    double fVal = lcl_IterateInverse( aFunc, fDF*0.5, fDF, bConvError );
+    ScTDistFunction aFunc( *this, fAlpha, fSize );
+    double fVal = lcl_IterateInverse( aFunc, fSize * 0.5, fSize, bConvError );
     if (bConvError)
         SetError(errNoConvergence);
-    PushDouble(fVal);
+    return( fVal );
 }
 
 class ScFDistFunction : public ScDistFunc
@@ -2247,6 +2251,20 @@ void ScInterpreter::ScConfidence()
     }
 }
 
+void ScInterpreter::ScConfidenceT()
+{
+    if ( MustHaveParamCount( GetByte(), 3 ) )
+    {
+        double n     = ::rtl::math::approxFloor(GetDouble());
+        double sigma = GetDouble();
+        double alpha = GetDouble();
+        if (sigma <= 0.0 || alpha <= 0.0 || alpha >= 1.0 || n < 1.0)
+            PushIllegalArgument();
+        else
+            PushDouble( sigma * GetTInv( 1 - alpha / 2, n - 1 ) / sqrt( n ) );
+    }
+}
+
 void ScInterpreter::ScZTest()
 {
     sal_uInt8 nParamCount = GetByte();
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 53fdcd2fa10b..f17f8470a3ad 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4143,7 +4143,9 @@ StackVar ScInterpreter::Interpret()
                 case ocQuartile         : ScQuartile();                 break;
                 case ocNormInv          : ScNormInv();                  break;
                 case ocSNormInv         : ScSNormInv();                 break;
-                case ocConfidence       : ScConfidence();               break;
+                case ocConfidence       :
+                case ocConfidence_N     : ScConfidence();               break;
+                case ocConfidence_T     : ScConfidenceT();              break;
                 case ocTrimMean         : ScTrimMean();                 break;
                 case ocProb             : ScProbability();              break;
                 case ocCorrel           : ScCorrel();                   break;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 6d159bd8cabc..9f0184325d1d 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -443,7 +443,9 @@ static const XclFunctionInfo saFuncTable_2010[] =
     EXC_FUNCENTRY_V_VR(         ocChiSqInv_MS,   2,  2,  0,  "CHISQ.INV" ),
     EXC_FUNCENTRY_V_VR(         ocChiDist_MS,    2,  2,  0,  "CHISQ.DIST.RT" ),
     EXC_FUNCENTRY_V_VR(         ocChiInv_MS,     2,  2,  0,  "CHISQ.INV.RT" ),
-    EXC_FUNCENTRY_V_VR(         ocChiTest_MS,    2,  2,  0,  "CHISQ.TEST" )
+    EXC_FUNCENTRY_V_VR(         ocChiTest_MS,    2,  2,  0,  "CHISQ.TEST" ),
+    EXC_FUNCENTRY_V_VR(         ocConfidence_N,  3,  3,  0,  "CONFIDENCE.NORM" ),
+    EXC_FUNCENTRY_V_VR(         ocConfidence_T,  3,  3,  0,  "CONFIDENCE.T" )
 };
 
 /** Functions new in Excel 2013.
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 9ffda5b3129f..09838b3623be 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -761,7 +761,9 @@ static const FunctionData saFuncTable2010[] =
     { "COM.MICROSOFT.CHISQ.INV",              "CHISQ.INV",           NOID,    NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.CHISQ.DIST.RT",          "CHISQ.DIST.RT",       NOID,    NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
     { "COM.MICROSOFT.CHISQ.INV.RT",           "CHISQ.INV.RT",        NOID,    NOID,   2,  2,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
-    { "COM.MICROSOFT.CHISQ.TEST",             "CHISQ.TEST",          NOID,    NOID,   2,  2,  V, { VA }, FUNCFLAG_MACROCALL_NEW }
+    { "COM.MICROSOFT.CHISQ.TEST",             "CHISQ.TEST",          NOID,    NOID,   2,  2,  V, { VA }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.CONFIDENCE.NORM",        "CONFIDENCE.NORM",     NOID,    NOID,   3,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.CONFIDENCE.T",           "CONFIDENCE.T",        NOID,    NOID,   3,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW }
 };
 
 /** Functions new in Excel 2013.
diff --git a/sc/source/ui/src/scfuncs.src b/sc/source/ui/src/scfuncs.src
index 60d8a8e1d5dd..e41cfab3210a 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -7610,6 +7610,86 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
             Text [ en-US ] = "The size of the population." ;
         };
     };
+     // -=*# Resource for function CONFIDENCE.NORM #*=-
+    Resource SC_OPCODE_CONFIDENCE_N
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns a (1 alpha) confidence interval for a normal distribution." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            U2S( HID_FUNC_CONFIDENCE_N );
+            3;  0;  0;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "alpha" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The level of the confidence interval." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "STDEV" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The standard deviation of the population." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "size" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The size of the population." ;
+        };
+    };
+     // -=*# Resource for function CONFIDENCE.T #*=-
+    Resource SC_OPCODE_CONFIDENCE_T
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Returns a (1 alpha) confidence interval for a Student's t distribution." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            U2S( HID_FUNC_CONFIDENCE_T );
+            3;  0;  0;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "alpha" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The level of the confidence interval." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "STDEV" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The standard deviation of the population." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "size" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The size of the population." ;
+        };
+    };
      // -=*# Resource for function GTEST #*=-
     Resource SC_OPCODE_Z_TEST
     {