fdo#71436 add Excel 2010 functions for F-distribution

Added F.DIST.RT, F.INV.RT, F.TEST, which are renamed
FDIST, FINV and FTEST and handle the right tail F-distribution.
Added F.DIST and F.INV, which are new functions and handle
the left tail F-distribution.

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

6 files changed, 270 insertions, 5 deletions

diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 813fb1b13b90..dc8c8b4fc538 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -749,6 +749,7 @@ void ScLogNormDist();
 void ScLogNormInv();
 void ScTDist();
 void ScFDist();
+void ScFDist_LT();
 void ScChiDist();   // for LEGACY.CHIDIST, returns right tail
 void ScChiSqDist(); // returns left tail or density
 void ScChiSqInv(); //invers to CHISQDIST
@@ -756,6 +757,7 @@ void ScWeibull();
 void ScBetaDist();
 void ScBetaDist_MS();
 void ScFInv();
+void ScFInv_LT();
 void ScTInv();
 void ScChiInv();
 void ScBetaInv();
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index 6272792be0a5..2f534f618249 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -1597,6 +1597,33 @@ void ScInterpreter::ScFDist()
     PushDouble(GetFDist(fF, fF1, fF2));
 }
 
+void ScInterpreter::ScFDist_LT()
+{
+    if ( !MustHaveParamCount( GetByte(), 4 ) )
+        return;
+    bool   bCum = GetBool();
+    double fF2 = ::rtl::math::approxFloor( GetDouble() );
+    double fF1 = ::rtl::math::approxFloor( GetDouble() );
+    double fF  = GetDouble();
+    if ( fF < 0.0 || fF1 < 1.0 || fF2 < 1.0 || fF1 >= 1.0E10 || fF2 >= 1.0E10 )
+    {
+        PushIllegalArgument();
+        return;
+    }
+    if ( bCum )
+    {
+        // left tail cumulative distribution
+        PushDouble( 1.0 - GetFDist( fF, fF1, fF2 ) );
+    }
+    else
+    {
+        // probability density function
+        PushDouble( pow( fF1 / fF2, fF1 / 2 ) * pow( fF, ( fF1 / 2 ) - 1 ) /
+                    ( pow( ( 1 + ( fF * fF1 / fF2 ) ), ( fF1 + fF2 ) / 2 ) *
+                      GetBeta( fF1 / 2, fF2 / 2 ) ) );
+    }
+}
+
 void ScInterpreter::ScChiDist()
 {
     double fResult;
@@ -2168,6 +2195,27 @@ void ScInterpreter::ScFInv()
     PushDouble(fVal);
 }
 
+void ScInterpreter::ScFInv_LT()
+{
+    if ( !MustHaveParamCount( GetByte(), 3 ) )
+        return;
+    double fF2 = ::rtl::math::approxFloor(GetDouble());
+    double fF1 = ::rtl::math::approxFloor(GetDouble());
+    double fP  = GetDouble();
+    if (fP <= 0.0 || fF1 < 1.0 || fF2 < 1.0 || fF1 >= 1.0E10 || fF2 >= 1.0E10 || fP > 1.0)
+    {
+        PushIllegalArgument();
+        return;
+    }
+
+    bool bConvError;
+    ScFDistFunction aFunc( *this, ( 1.0 - fP ), fF1, fF2 );
+    double fVal = lcl_IterateInverse( aFunc, fF1*0.5, fF1, bConvError );
+    if (bConvError)
+        SetError(errNoConvergence);
+    PushDouble(fVal);
+}
+
 class ScChiDistFunction : public ScDistFunc
 {
     ScInterpreter&  rInt;
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index f17f8470a3ad..38d73b096c29 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4110,7 +4110,9 @@ StackVar ScInterpreter::Interpret()
                 case ocHypGeomDist      : ScHypGeomDist();              break;
                 case ocLogNormDist      : ScLogNormDist();              break;
                 case ocTDist            : ScTDist();                    break;
-                case ocFDist            : ScFDist();                    break;
+                case ocFDist            :
+                case ocFDist_RT         : ScFDist();                    break;
+                case ocFDist_LT         : ScFDist_LT();                 break;
                 case ocChiDist          :
                 case ocChiDist_MS       : ScChiDist();                  break;
                 case ocChiSqDist        :
@@ -4133,7 +4135,8 @@ StackVar ScInterpreter::Interpret()
                 case ocBad              : ScBadName();                  break;
                 case ocZTest            : ScZTest();                    break;
                 case ocTTest            : ScTTest();                    break;
-                case ocFTest            : ScFTest();                    break;
+                case ocFTest            :
+                case ocFTest_MS         : ScFTest();                    break;
                 case ocRank             : ScRank();                     break;
                 case ocPercentile       : ScPercentile();               break;
                 case ocPercentrank      : ScPercentrank();              break;
@@ -4173,7 +4176,9 @@ StackVar ScInterpreter::Interpret()
                 case ocChiSqInv         :
                 case ocChiSqInv_MS      : ScChiSqInv();                 break;
                 case ocTInv             : ScTInv();                     break;
-                case ocFInv             : ScFInv();                     break;
+                case ocFInv             :
+                case ocFInv_RT          : ScFInv();                     break;
+                case ocFInv_LT          : ScFInv_LT();                  break;
                 case ocLogInv           : ScLogNormInv();               break;
                 case ocBetaDist         : ScBetaDist();                 break;
                 case ocBetaDist_MS      : ScBetaDist_MS();              break;
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 9f0184325d1d..b525d75b14e5 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -445,7 +445,12 @@ static const XclFunctionInfo saFuncTable_2010[] =
     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(         ocConfidence_N,  3,  3,  0,  "CONFIDENCE.NORM" ),
-    EXC_FUNCENTRY_V_VR(         ocConfidence_T,  3,  3,  0,  "CONFIDENCE.T" )
+    EXC_FUNCENTRY_V_VR(         ocConfidence_T,  3,  3,  0,  "CONFIDENCE.T" ),
+    EXC_FUNCENTRY_V_VR(         ocFDist_LT,      4,  4,  0,  "F.DIST" ),
+    EXC_FUNCENTRY_V_VR(         ocFDist_RT,      3,  3,  0,  "F.DIST.RT" ),
+    EXC_FUNCENTRY_V_VR(         ocFInv_LT,       3,  3,  0,  "F.INV" ),
+    EXC_FUNCENTRY_V_VR(         ocFInv_RT,       3,  3,  0,  "F.INV.RT" ),
+    EXC_FUNCENTRY_V_VR(         ocFTest_MS,      2,  2,  0,  "F.TEST" )
 };
 
 /** Functions new in Excel 2013.
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index 09838b3623be..b2376f431960 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -763,7 +763,12 @@ static const FunctionData saFuncTable2010[] =
     { "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.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 }
+    { "COM.MICROSOFT.CONFIDENCE.T",           "CONFIDENCE.T",        NOID,    NOID,   3,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.F.DIST",                 "F.DIST",              NOID,   NOID,    4,  4,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.F.DIST.RT",              "F.DIST.RT",           NOID,   NOID,    3,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.F.INV",                  "F.INV",               NOID,   NOID,    3,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.F.INV.RT",               "F.INV.RT",            NOID,   NOID,    3,  3,  V, { VR }, FUNCFLAG_MACROCALL_NEW },
+    { "COM.MICROSOFT.F.TEST",                 "F.TEST",              NOID,   NOID,    2,  2,  V, { VA }, 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 67c346ea3fd7..36dee21c2ec3 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -7145,6 +7145,94 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
             Text [ en-US ] = "The degrees of freedom in the denominator of the F distribution." ;
         };
     };
+     // -=*# Resource for function F.DIST #*=-
+    Resource SC_OPCODE_F_DIST_LT
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Values of the left tail F probability distribution." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            U2S( HID_FUNC_F_DIST_LT );
+            4;  0;  0;  0; 0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "Number" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The value for which the F distribution is to be calculated." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "degrees_freedom_1" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The degrees of freedom in the numerator of the F distribution." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "degrees_freedom_2" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The degrees of freedom in the denominator of the F distribution." ;
+        };
+        String 8 // Name of Parameter 4
+        {
+            Text [ en-US ] = "cumulative" ;
+        };
+        String 9 // Description of Parameter 4
+        {
+            Text [ en-US ] = "Cumulative distribution function (TRUE) or probability density function (FALSE)." ;
+        };
+    };
+     // -=*# Resource for function F.DIST.RT #*=-
+    Resource SC_OPCODE_F_DIST_RT
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Values of the right tail F probability distribution." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            U2S( HID_FUNC_F_DIST_RT );
+            3;  0;  0;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "Number" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The value for which the F distribution is to be calculated." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "degrees_freedom_1" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The degrees of freedom in the numerator of the F distribution." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "degrees_freedom_2" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The degrees of freedom in the denominator of the F distribution." ;
+        };
+    };
      // -=*# Resource for function FINV #*=-
     Resource SC_OPCODE_F_INV
     {
@@ -7185,6 +7273,86 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
             Text [ en-US ] = "The degrees of freedom in the denominator of the F distribution." ;
         };
     };
+     // -=*# Resource for function F.INV #*=-
+    Resource SC_OPCODE_F_INV_LT
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Values of the inverse left tail F distribution." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            U2S( HID_FUNC_F_INV_LT );
+            3;  0;  0;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "number" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The probability value for which the inverse F distribution is to be calculated." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "degrees_freedom_1" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The degrees of freedom in the numerator of the F distribution." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "degrees_freedom_2" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The degrees of freedom in the denominator of the F distribution." ;
+        };
+    };
+     // -=*# Resource for function F.INV.RT #*=-
+    Resource SC_OPCODE_F_INV_RT
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Values of the inverse right tail F distribution." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            U2S( HID_FUNC_F_INV_RT );
+            3;  0;  0;  0;
+            0;
+        };
+        String 2 // Name of Parameter 1
+        {
+            Text [ en-US ] = "number" ;
+        };
+        String 3 // Description of Parameter 1
+        {
+            Text [ en-US ] = "The probability value for which the inverse F distribution is to be calculated." ;
+        };
+        String 4 // Name of Parameter 2
+        {
+            Text [ en-US ] = "degrees_freedom_1" ;
+        };
+        String 5 // Description of Parameter 2
+        {
+            Text [ en-US ] = "The degrees of freedom in the numerator of the F distribution." ;
+        };
+        String 6 // Name of Parameter 3
+        {
+            Text [ en-US ] = "degrees_freedom_2" ;
+        };
+        String 7 // Description of Parameter 3
+        {
+            Text [ en-US ] = "The degrees of freedom in the denominator of the F distribution." ;
+        };
+    };
      // -=*# Resource for function CHIDIST #*=-
     Resource SC_OPCODE_CHI_DIST
     {
@@ -7826,6 +7994,38 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
             Text [ en-US ] = "The second record array." ;
         };
     };
+     // -=*# Resource for function F.TEST #*=-
+    Resource SC_OPCODE_F_TEST_MS
+    {
+        String 1 // Description
+        {
+            Text [ en-US ] = "Calculates the F test." ;
+        };
+        ExtraData =
+        {
+            0;
+            ID_FUNCTION_GRP_STATISTIC;
+            U2S( HID_FUNC_F_TEST_MS );
+            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 TTEST #*=-
     Resource SC_OPCODE_T_TEST
     {