From 5b1e440304c271e4286255c17a6fe475899bd94f Mon Sep 17 00:00:00 2001 From: Winfried Donkers Date: Sun, 29 Dec 2013 16:16:42 +0100 Subject: fdo#72793 Add Excel 2010 functions T.DIST, T.DIST.2T, T.DIST.RT, T.INV, T.INV.2T, T.TEST Also, fixed some incorrect string assignments in formula/source/core/resource/core_resource.src from previously added Excel 2010 functions. Change-Id: Ia4d13e7597d2a054d345a08b66b878f91c43f872 Reviewed-on: https://gerrit.libreoffice.org/7226 Reviewed-by: Eike Rathke Tested-by: Eike Rathke --- formula/source/core/resource/core_resource.src | 52 +++++- include/formula/compiler.hrc | 8 +- include/formula/opcode.hxx | 6 + sc/inc/helpids.h | 6 + sc/qa/unit/subsequent_filters-test.cxx | 10 +- sc/qa/unit/ucalc.cxx | 6 + sc/source/core/inc/interpre.hxx | 8 +- sc/source/core/tool/interpr3.cxx | 91 ++++++++--- sc/source/core/tool/interpr4.cxx | 10 +- sc/source/filter/excel/xlformula.cxx | 8 +- sc/source/filter/oox/formulabase.cxx | 8 +- sc/source/ui/src/scfuncs.src | 216 +++++++++++++++++++++++++ 12 files changed, 388 insertions(+), 41 deletions(-) diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src index fbd35034ddd0..4f7444167d02 100644 --- a/formula/source/core/resource/core_resource.src +++ b/formula/source/core/resource/core_resource.src @@ -290,6 +290,9 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF String SC_OPCODE_LOG_NORM_DIST { Text = "LOGNORMDIST" ; }; String SC_OPCODE_LOG_NORM_DIST_MS { Text = "COM.MICROSOFT.LOGNORM.DIST" ; }; String SC_OPCODE_T_DIST { Text = "LEGACY.TDIST" ; }; + String SC_OPCODE_T_DIST_2T { Text = "COM.MICROSOFT.T.DIST.2T" ; }; + String SC_OPCODE_T_DIST_MS { Text = "COM.MICROSOFT.T.DIST" ; }; + String SC_OPCODE_T_DIST_RT { Text = "COM.MICROSOFT.T.DIST.RT" ; }; String SC_OPCODE_F_DIST { Text = "LEGACY.FDIST" ; }; String SC_OPCODE_F_DIST_LT { Text = "COM.MICROSOFT.F.DIST" ; }; String SC_OPCODE_F_DIST_RT { Text = "COM.MICROSOFT.F.DIST.RT" ; }; @@ -312,6 +315,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF String SC_OPCODE_MODAL_VALUE { Text = "MODE" ; }; String SC_OPCODE_Z_TEST { Text = "ZTEST" ; }; String SC_OPCODE_T_TEST { Text = "TTEST" ; }; + String SC_OPCODE_T_TEST_MS { Text = "COM.MICROSOFT.T.TEST" ; }; String SC_OPCODE_RANK { Text = "RANK" ; }; String SC_OPCODE_PERCENTILE { Text = "PERCENTILE" ; }; String SC_OPCODE_PERCENT_RANK { Text = "PERCENTRANK" ; }; @@ -349,6 +353,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF String SC_OPCODE_GAMMA_INV { Text = "GAMMAINV" ; }; String SC_OPCODE_GAMMA_INV_MS { Text = "COM.MICROSOFT.GAMMA.INV" ; }; String SC_OPCODE_T_INV { Text = "TINV" ; }; + String SC_OPCODE_T_INV_2T { Text = "COM.MICROSOFT.T.INV.2T" ; }; + String SC_OPCODE_T_INV_MS { Text = "COM.MICROSOFT.T.INV" ; }; String SC_OPCODE_F_INV { Text = "LEGACY.FINV" ; }; String SC_OPCODE_F_INV_LT { Text = "COM.MICROSOFT.F.INV" ; }; String SC_OPCODE_F_INV_RT { Text = "COM.MICROSOFT.F.INV.RT" ; }; @@ -670,6 +676,9 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML String SC_OPCODE_LOG_NORM_DIST { Text = "LOGNORMDIST" ; }; String SC_OPCODE_LOG_NORM_DIST_MS { Text = "_xlfn.LOGNORM.DIST" ; }; String SC_OPCODE_T_DIST { Text = "TDIST" ; }; + String SC_OPCODE_T_DIST_2T { Text = "_xlfn.T.DIST.2T" ; }; + String SC_OPCODE_T_DIST_MS { Text = "_xlfn.T.DIST" ; }; + String SC_OPCODE_T_DIST_RT { Text = "_xlfn.T.DIST.RT" ; }; String SC_OPCODE_F_DIST { Text = "FDIST" ; }; String SC_OPCODE_F_DIST_LT { Text = "_xlfn.F.DIST" ; }; String SC_OPCODE_F_DIST_RT { Text = "_xlfn.F.DIST.RT" ; }; @@ -692,6 +701,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML String SC_OPCODE_MODAL_VALUE { Text = "MODE" ; }; String SC_OPCODE_Z_TEST { Text = "ZTEST" ; }; String SC_OPCODE_T_TEST { Text = "TTEST" ; }; + String SC_OPCODE_T_TEST_MS { Text = "_xlfn.T.TEST" ; }; String SC_OPCODE_RANK { Text = "RANK" ; }; String SC_OPCODE_PERCENTILE { Text = "PERCENTILE" ; }; String SC_OPCODE_PERCENT_RANK { Text = "PERCENTRANK" ; }; @@ -729,6 +739,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML String SC_OPCODE_GAMMA_INV { Text = "GAMMAINV" ; }; String SC_OPCODE_GAMMA_INV_MS { Text = "_xlfn.GAMMA.INV" ; }; String SC_OPCODE_T_INV { Text = "TINV" ; }; + String SC_OPCODE_T_INV_2T { Text = "_xlfn.T.INV.2T" ; }; + String SC_OPCODE_T_INV_MS { Text = "_xlfn.T.INV" ; }; String SC_OPCODE_F_INV { Text = "FINV" ; }; String SC_OPCODE_F_INV_LT { Text = "_xlfn.F.INV" ; }; String SC_OPCODE_F_INV_RT { Text = "_xlfn.F.INV.RT" ; }; @@ -1052,6 +1064,9 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH String SC_OPCODE_LOG_NORM_DIST { Text = "LOGNORMDIST" ; }; String SC_OPCODE_LOG_NORM_DIST_MS { Text = "LOGNORM.DIST" ; }; String SC_OPCODE_T_DIST { Text = "TDIST" ; }; + String SC_OPCODE_T_DIST_2T { Text = "T.DIST.2T" ; }; + String SC_OPCODE_T_DIST_MS { Text = "T.DIST" ; }; + String SC_OPCODE_T_DIST_RT { Text = "T.DIST.RT" ; }; String SC_OPCODE_F_DIST { Text = "FDIST" ; }; String SC_OPCODE_F_DIST_LT { Text = "F.DIST" ; }; String SC_OPCODE_F_DIST_RT { Text = "F.DIST.RT" ; }; @@ -1074,6 +1089,7 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH String SC_OPCODE_MODAL_VALUE { Text = "MODE" ; }; String SC_OPCODE_Z_TEST { Text = "ZTEST" ; }; String SC_OPCODE_T_TEST { Text = "TTEST" ; }; + String SC_OPCODE_T_TEST_MS { Text = "T.TEST" ; }; String SC_OPCODE_RANK { Text = "RANK" ; }; String SC_OPCODE_PERCENTILE { Text = "PERCENTILE" ; }; String SC_OPCODE_PERCENT_RANK { Text = "PERCENTRANK" ; }; @@ -1111,6 +1127,8 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH String SC_OPCODE_GAMMA_INV { Text = "GAMMAINV" ; }; String SC_OPCODE_GAMMA_INV_MS { Text = "GAMMA.INV" ; }; String SC_OPCODE_T_INV { Text = "TINV" ; }; + String SC_OPCODE_T_INV_2T { Text = "T.INV.2T" ; }; + String SC_OPCODE_T_INV_MS { Text = "T.INV" ; }; String SC_OPCODE_F_INV { Text = "FINV" ; }; String SC_OPCODE_F_INV_LT { Text = "F.INV" ; }; String SC_OPCODE_F_INV_RT { Text = "F.INV.RT" ; }; @@ -1548,7 +1566,7 @@ Resource RID_STRLIST_FUNCTION_NAMES }; String SC_OPCODE_STD_NORM_DIST_MS { - Text = "NORM.S.DIST" ; + Text [ en-US ] = "NORM.S.DIST" ; }; String SC_OPCODE_FISHER { @@ -1564,7 +1582,7 @@ Resource RID_STRLIST_FUNCTION_NAMES }; String SC_OPCODE_S_NORM_INV_MS { - Text = "NORM.S.INV" ; + Text [ en-US ] = "NORM.S.INV" ; }; String SC_OPCODE_GAMMA_LN { @@ -1788,7 +1806,7 @@ Resource RID_STRLIST_FUNCTION_NAMES }; String SC_OPCODE_NORM_DIST_MS { - Text = "NORM.DIST" ; + Text [ en-US ] = "NORM.DIST" ; }; String SC_OPCODE_EXP_DIST { @@ -2163,6 +2181,18 @@ Resource RID_STRLIST_FUNCTION_NAMES { Text [ en-US ] = "TDIST" ; }; + String SC_OPCODE_T_DIST_2T + { + Text [ en-US ] = "T.DIST.2T" ; + }; + String SC_OPCODE_T_DIST_MS + { + Text [ en-US ] = "T.DIST" ; + }; + String SC_OPCODE_T_DIST_RT + { + Text [ en-US ] = "T.DIST.RT" ; + }; String SC_OPCODE_F_DIST { Text [ en-US ] = "FDIST" ; @@ -2251,6 +2281,10 @@ Resource RID_STRLIST_FUNCTION_NAMES { Text [ en-US ] = "TTEST" ; }; + String SC_OPCODE_T_TEST_MS + { + Text [ en-US ] = "T.TEST" ; + }; String SC_OPCODE_RANK { Text [ en-US ] = "RANK" ; @@ -2285,7 +2319,7 @@ Resource RID_STRLIST_FUNCTION_NAMES }; String SC_OPCODE_NORM_INV_MS { - Text = "NORM.INV" ; + Text [ en-US ] = "NORM.INV" ; }; String SC_OPCODE_CONFIDENCE { @@ -2399,6 +2433,14 @@ Resource RID_STRLIST_FUNCTION_NAMES { Text [ en-US ] = "TINV" ; }; + String SC_OPCODE_T_INV_2T + { + Text [ en-US ] = "T.INV.2T" ; + }; + String SC_OPCODE_T_INV_MS + { + Text [ en-US ] = "T.INV" ; + }; String SC_OPCODE_F_INV { Text [ en-US ] = "FINV" ; @@ -2425,7 +2467,7 @@ Resource RID_STRLIST_FUNCTION_NAMES }; String SC_OPCODE_LOG_INV_MS { - Text = "LOGNORM.INV" ; + Text [ en-US ] = "LOGNORM.INV" ; }; String SC_OPCODE_TABLE_OP { diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc index ffab76715c45..c4c20c27cff6 100644 --- a/include/formula/compiler.hrc +++ b/include/formula/compiler.hrc @@ -442,8 +442,14 @@ #define SC_OPCODE_NORM_INV_MS 444 #define SC_OPCODE_STD_NORM_DIST_MS 445 #define SC_OPCODE_S_NORM_INV_MS 446 +#define SC_OPCODE_T_DIST_MS 447 +#define SC_OPCODE_T_DIST_RT 448 +#define SC_OPCODE_T_DIST_2T 449 +#define SC_OPCODE_T_INV_2T 450 +#define SC_OPCODE_T_INV_MS 451 +#define SC_OPCODE_T_TEST_MS 452 -#define SC_OPCODE_STOP_2_PAR 447 /* last function with two or more parameters' OpCode + 1 */ +#define SC_OPCODE_STOP_2_PAR 453 /* 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 ce6fd1529996..bc7fe0fa6d84 100644 --- a/include/formula/opcode.hxx +++ b/include/formula/opcode.hxx @@ -332,6 +332,9 @@ enum OpCodeEnum ocLogNormDist = SC_OPCODE_LOG_NORM_DIST, ocLogNormDist_MS = SC_OPCODE_LOG_NORM_DIST_MS, ocTDist = SC_OPCODE_T_DIST, + ocTDist_MS = SC_OPCODE_T_DIST_MS, + ocTDist_RT = SC_OPCODE_T_DIST_RT, + ocTDist_2T = SC_OPCODE_T_DIST_2T, ocFDist = SC_OPCODE_F_DIST, ocFDist_LT = SC_OPCODE_F_DIST_LT, ocFDist_RT = SC_OPCODE_F_DIST_RT, @@ -357,6 +360,7 @@ enum OpCodeEnum ocModalValue = SC_OPCODE_MODAL_VALUE, ocZTest = SC_OPCODE_Z_TEST, ocTTest = SC_OPCODE_T_TEST, + ocTTest_MS = SC_OPCODE_T_TEST_MS, ocRank = SC_OPCODE_RANK, ocPercentile = SC_OPCODE_PERCENTILE, ocPercentrank = SC_OPCODE_PERCENT_RANK, @@ -394,6 +398,8 @@ enum OpCodeEnum ocGammaInv = SC_OPCODE_GAMMA_INV, ocGammaInv_MS = SC_OPCODE_GAMMA_INV_MS, ocTInv = SC_OPCODE_T_INV, + ocTInv_2T = SC_OPCODE_T_INV_2T, + ocTInv_MS = SC_OPCODE_T_INV_MS, ocFInv = SC_OPCODE_F_INV, ocFInv_LT = SC_OPCODE_F_INV_LT, ocFInv_RT = SC_OPCODE_F_INV_RT, diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h index f9fd155b586a..fbdef8242fdd 100644 --- a/sc/inc/helpids.h +++ b/sc/inc/helpids.h @@ -697,5 +697,11 @@ #define HID_FUNC_NORMINV_MS "SC_HID_FUNC_NORMINV_MS" #define HID_FUNC_STD_NORMDIST_MS "SC_HID_FUNC_STD_NORMDIST_MS" #define HID_FUNC_STD_NORMINV_MS "SC_HID_FUNC_STD_NORMINV_MS" +#define HID_FUNC_TDIST_2T "SC_HID_FUNC_TDIST_2T" +#define HID_FUNC_TDIST_MS "SC_HID_FUNC_TDIST_MS" +#define HID_FUNC_TDIST_RT "SC_HID_FUNC_TDIST_RT" +#define HID_FUNC_TINV_2T "SC_HID_FUNC_TINV_2T" +#define HID_FUNC_TINV_MS "SC_HID_FUNC_TINV_MS" +#define HID_FUNC_TTEST_MS "SC_HID_FUNC_TTEST_MS" /* vim:set shiftwidth=4 softtabstop=4 expandtab: */ diff --git a/sc/qa/unit/subsequent_filters-test.cxx b/sc/qa/unit/subsequent_filters-test.cxx index 1f3b3d44b492..8f1d4c9987ac 100644 --- a/sc/qa/unit/subsequent_filters-test.cxx +++ b/sc/qa/unit/subsequent_filters-test.cxx @@ -457,12 +457,12 @@ void ScFiltersTest::testFunctionsExcel2010() { 59, false }, { 60, true }, { 61, true }, - { 62, false }, - { 63, false }, - { 64, false }, - { 65, false }, + { 62, true }, + { 63, true }, + { 64, true }, + { 65, true }, { 66, false }, - { 67, false }, + { 67, true }, { 68, true }, { 69, true }, { 70, true }, diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx index 111b94019bf9..c4737cf10a3f 100644 --- a/sc/qa/unit/ucalc.cxx +++ b/sc/qa/unit/ucalc.cxx @@ -2774,6 +2774,12 @@ void Test::testFunctionLists() "STDEVP", "STDEVPA", "STEYX", + "T.DIST", + "T.DIST.2T", + "T.DIST.RT", + "T.INV", + "T.INV.2T", + "T.TEST", "TDIST", "TINV", "TRIMMEAN", diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx index 1902dadb949e..51d94e54fd9b 100644 --- a/sc/source/core/inc/interpre.hxx +++ b/sc/source/core/inc/interpre.hxx @@ -716,7 +716,7 @@ double GetChiDist(double fChi, double fDF); // for LEGACY.CHIDIST, returns r double GetChiSqDistCDF(double fX, double fDF); // for CHISQDIST, returns left tail double GetChiSqDistPDF(double fX, double fDF); // probability density function double GetFDist(double x, double fF1, double fF2); -double GetTDist(double T, double fDF); +double GetTDist( double T, double fDF, int nType ); double Fakultaet(double x); double BinomKoeff(double n, double k); double GetGamma(double x); @@ -750,6 +750,8 @@ void ScHypGeomDist_MS(); void ScLogNormDist( int nMinParamCount ); void ScLogNormInv(); void ScTDist(); +void ScTDist_MS(); +void ScTDist_T( int nTails ); void ScFDist(); void ScFDist_LT(); void ScChiDist(); // for LEGACY.CHIDIST, returns right tail @@ -761,7 +763,7 @@ void ScBetaDist(); void ScBetaDist_MS(); void ScFInv(); void ScFInv_LT(); -void ScTInv(); +void ScTInv( int nType ); void ScChiInv(); void ScBetaInv(); void ScCritBinom(); @@ -825,7 +827,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 ); +double GetTInv( double fAlpha, double fSize, int nType ); public: ScInterpreter( ScFormulaCell* pCell, ScDocument* pDoc, diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx index dce896588b3f..2a5d77005849 100644 --- a/sc/source/core/tool/interpr3.cxx +++ b/sc/source/core/tool/interpr3.cxx @@ -633,9 +633,23 @@ double ScInterpreter::GetFDist(double x, double fF1, double fF2) return (GetBetaDist(arg, alpha, beta)); } -double ScInterpreter::GetTDist(double T, double fDF) +double ScInterpreter::GetTDist( double T, double fDF, int nType ) { - return 0.5 * GetBetaDist(fDF/(fDF+T*T), fDF/2.0, 0.5); + switch ( nType ) + { + case 1 : // 1-tailed T-distribution + return 0.5 * GetBetaDist( fDF / ( fDF + T * T ), fDF / 2.0, 0.5 ); + case 2 : // 2-tailed T-distribution + return GetBetaDist( fDF / ( fDF + T * T ), fDF / 2.0, 0.5); + case 3 : // left-tailed T-distribution (probability density function) + return pow( 1 + ( T * T / fDF ), -( fDF + 1 ) / 2 ) / ( sqrt( fDF ) * GetBeta( 0.5, fDF / 2.0 ) ); + case 4 : // left-tailed T-distribution (cumulative distribution function) + double X = fDF / ( T * T + fDF ); + double R = 0.5 * GetBetaDist( X, 0.5 * fDF, 0.5 ); + return ( T < 0 ? R : 1 - R ); + } + SetError( errIllegalArgument ); + return HUGE_VAL; } // for LEGACY.CHIDIST, returns right tail, fDF=degrees of freedom @@ -1614,11 +1628,36 @@ void ScInterpreter::ScTDist() PushIllegalArgument(); return; } - double R = GetTDist(T, fDF); - if (fFlag == 1.0) - PushDouble(R); - else - PushDouble(2.0*R); + PushDouble( GetTDist( T, fDF, ( int )fFlag ) ); +} + +void ScInterpreter::ScTDist_T( int nTails ) +{ + if ( !MustHaveParamCount( GetByte(), 2 ) ) + return; + double fDF = ::rtl::math::approxFloor( GetDouble() ); + double T = GetDouble(); + if ( fDF < 1.0 || T < 0.0 ) + { + PushIllegalArgument(); + return; + } + PushDouble( GetTDist( T, fDF, nTails ) ); +} + +void ScInterpreter::ScTDist_MS() +{ + if ( !MustHaveParamCount( GetByte(), 3 ) ) + return; + bool bCumulative = GetBool(); + double fDF = ::rtl::math::approxFloor( GetDouble() ); + double T = GetDouble(); + if ( fDF < 1.0 ) + { + PushIllegalArgument(); + return; + } + PushDouble( GetTDist( T, fDF, ( bCumulative ? 4 : 3 ) ) ); } void ScInterpreter::ScFDist() @@ -2214,34 +2253,43 @@ class ScTDistFunction : public ScDistFunc { ScInterpreter& rInt; double fp, fDF; + int nT; public: - ScTDistFunction( ScInterpreter& rI, double fpVal, double fDFVal ) : - rInt(rI), fp(fpVal), fDF(fDFVal) {} + ScTDistFunction( ScInterpreter& rI, double fpVal, double fDFVal, int nType ) : + rInt( rI ), fp( fpVal ), fDF( fDFVal ), nT( nType ) {} virtual ~ScTDistFunction() {} - double GetValue( double x ) const { return fp - 2 * rInt.GetTDist(x, fDF); } + double GetValue( double x ) const { return fp - rInt.GetTDist( x, fDF, nT ); } }; -void ScInterpreter::ScTInv() +void ScInterpreter::ScTInv( int nType ) { if ( !MustHaveParamCount( GetByte(), 2 ) ) return; double fDF = ::rtl::math::approxFloor(GetDouble()); double fP = GetDouble(); - if (fDF < 1.0 || fDF > 1.0E10 || fP <= 0.0 || fP > 1.0 ) + if (fDF < 1.0 || fP <= 0.0 || fP > 1.0 ) { PushIllegalArgument(); return; } - PushDouble( GetTInv( fP, fDF ) ); + if ( nType == 4 ) // left-tailed cumulative t-distribution + { + if ( fP < 0.5 ) + PushDouble( -GetTInv( 1 - fP, fDF, nType ) ); + else + PushDouble( GetTInv( fP, fDF, nType ) ); + } + else + PushDouble( GetTInv( fP, fDF, nType ) ); }; -double ScInterpreter::GetTInv( double fAlpha, double fSize ) +double ScInterpreter::GetTInv( double fAlpha, double fSize, int nType ) { bool bConvError; - ScTDistFunction aFunc( *this, fAlpha, fSize ); + ScTDistFunction aFunc( *this, fAlpha, fSize, nType ); double fVal = lcl_IterateInverse( aFunc, fSize * 0.5, fSize, bConvError ); if (bConvError) SetError(errNoConvergence); @@ -2397,7 +2445,7 @@ void ScInterpreter::ScConfidenceT() if (sigma <= 0.0 || alpha <= 0.0 || alpha >= 1.0 || n < 1.0) PushIllegalArgument(); else - PushDouble( sigma * GetTInv( alpha, n - 1 ) / sqrt( n ) ); + PushDouble( sigma * GetTInv( alpha, n - 1, 2 ) / sqrt( n ) ); } } @@ -2593,9 +2641,9 @@ void ScInterpreter::ScTTest() { if ( !MustHaveParamCount( GetByte(), 4 ) ) return; - double fTyp = ::rtl::math::approxFloor(GetDouble()); - double fAnz = ::rtl::math::approxFloor(GetDouble()); - if (fAnz != 1.0 && fAnz != 2.0) + double fTyp = ::rtl::math::approxFloor(GetDouble()); + double fTails = ::rtl::math::approxFloor(GetDouble()); + if (fTails != 1.0 && fTails != 2.0) { PushIllegalArgument(); return; @@ -2662,10 +2710,7 @@ void ScInterpreter::ScTTest() PushIllegalArgument(); return; } - if (fAnz == 1.0) - PushDouble(GetTDist(fT, fF)); - else - PushDouble(2.0*GetTDist(fT, fF)); + PushDouble( GetTDist( fT, fF, ( int )fTails ) ); } void ScInterpreter::ScFTest() diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx index b77166d28f6d..6b38b5f202ea 100644 --- a/sc/source/core/tool/interpr4.cxx +++ b/sc/source/core/tool/interpr4.cxx @@ -4116,6 +4116,9 @@ StackVar ScInterpreter::Interpret() case ocLogNormDist : ScLogNormDist( 1 ); break; case ocLogNormDist_MS : ScLogNormDist( 4 ); break; case ocTDist : ScTDist(); break; + case ocTDist_MS : ScTDist_MS(); break; + case ocTDist_RT : ScTDist_T( 1 ); break; + case ocTDist_2T : ScTDist_T( 2 ); break; case ocFDist : case ocFDist_RT : ScFDist(); break; case ocFDist_LT : ScFDist_LT(); break; @@ -4141,7 +4144,8 @@ StackVar ScInterpreter::Interpret() case ocNoName : ScNoName(); break; case ocBad : ScBadName(); break; case ocZTest : ScZTest(); break; - case ocTTest : ScTTest(); break; + case ocTTest : + case ocTTest_MS : ScTTest(); break; case ocFTest : case ocFTest_MS : ScFTest(); break; case ocRank : ScRank(); break; @@ -4187,7 +4191,9 @@ StackVar ScInterpreter::Interpret() case ocChiInv_MS : ScChiInv(); break; case ocChiSqInv : case ocChiSqInv_MS : ScChiSqInv(); break; - case ocTInv : ScTInv(); break; + case ocTInv : + case ocTInv_2T : ScTInv( 2 ); break; + case ocTInv_MS : ScTInv( 4 ); break; case ocFInv : case ocFInv_RT : ScFInv(); break; case ocFInv_LT : ScFInv_LT(); break; diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx index bf335a7be149..66f0d10426ae 100644 --- a/sc/source/filter/excel/xlformula.cxx +++ b/sc/source/filter/excel/xlformula.cxx @@ -463,7 +463,13 @@ static const XclFunctionInfo saFuncTable_2010[] = EXC_FUNCENTRY_V_VR( ocNormDist_MS, 4, 4, 0, "NORM.DIST" ), EXC_FUNCENTRY_V_VR( ocNormInv_MS, 3, 3, 0, "NORM.INV" ), EXC_FUNCENTRY_V_VR( ocStdNormDist_MS, 2, 2, 0, "NORM.S.DIST" ), - EXC_FUNCENTRY_V_VR( ocSNormInv_MS, 1, 1, 0, "NORM.S.INV" ) + EXC_FUNCENTRY_V_VR( ocSNormInv_MS, 1, 1, 0, "NORM.S.INV" ), + EXC_FUNCENTRY_V_VR( ocTDist_2T, 2, 2, 0, "T.DIST.2T" ), + EXC_FUNCENTRY_V_VR( ocTDist_MS, 3, 3, 0, "T.DIST" ), + EXC_FUNCENTRY_V_VR( ocTDist_RT, 2, 2, 0, "T.DIST.RT" ), + EXC_FUNCENTRY_V_VR( ocTInv_2T, 2, 2, 0, "T.INV.2T" ), + EXC_FUNCENTRY_V_VR( ocTInv_MS, 2, 2, 0, "T.INV" ), + EXC_FUNCENTRY_V_VR( ocTTest_MS, 4, 4, 0, "T.TEST" ) }; /** Functions new in Excel 2013. diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx index 63d5006467be..f0dd1ed1eac2 100644 --- a/sc/source/filter/oox/formulabase.cxx +++ b/sc/source/filter/oox/formulabase.cxx @@ -781,7 +781,13 @@ static const FunctionData saFuncTable2010[] = { "COM.MICROSOFT.NORM.DIST", "NORM.DIST", NOID, NOID, 4, 4, V, { VR }, FUNCFLAG_MACROCALL_NEW }, { "COM.MICROSOFT.NORM.INV", "NORM.INV", NOID, NOID, 3, 3, V, { VR }, FUNCFLAG_MACROCALL_NEW }, { "COM.MICROSOFT.NORM.S.DIST", "NORM.S.DIST", NOID, NOID, 2, 2, V, { VR }, FUNCFLAG_MACROCALL_NEW }, - { "COM.MICROSOFT.NORM.S.INV", "NORM.S.INV", NOID, NOID, 1, 1, V, { VR }, FUNCFLAG_MACROCALL_NEW } + { "COM.MICROSOFT.NORM.S.INV", "NORM.S.INV", NOID, NOID, 1, 1, V, { VR }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.T.DIST", "T.DIST", NOID, NOID, 3, 3, V, { VR }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.T.DIST.2T", "T.DIST.2T", NOID, NOID, 2, 2, V, { VR }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.T.DIST.RT", "T.DIST.RT", NOID, NOID, 2, 2, V, { VR }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.T.INV", "T.INV", NOID, NOID, 2, 2, V, { VR }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.T.INV.2T", "T.INV.2T", NOID, NOID, 2, 2, V, { VR }, FUNCFLAG_MACROCALL_NEW }, + { "COM.MICROSOFT.T.TEST", "T.TEST", NOID, NOID, 4, 4, V, { VA, VA, 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 3f2e710956e2..cedf6328868d 100644 --- a/sc/source/ui/src/scfuncs.src +++ b/sc/source/ui/src/scfuncs.src @@ -7600,6 +7600,110 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2 { Text [ en-US ] = "Mode = 1 calculates the one-tailed test, 2 = two-tailed distribution." ; }; + }; + // -=*# Resource for function T.DIST.2T #*=- + Resource SC_OPCODE_T_DIST_2T + { + String 1 // Description + { + Text [ en-US ] = "Returns the two-tailed t-distribution." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_TDIST_2T ); + 2; 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 T distribution is to be calculated." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "degrees_freedom" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The degrees of freedom of the T distribution." ; + }; + }; + // -=*# Resource for function T.DIST #*=- + Resource SC_OPCODE_T_DIST_MS + { + String 1 // Description + { + Text [ en-US ] = "Returns the t-distribution." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_TDIST_MS ); + 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 T distribution is to be calculated." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "degrees_freedom" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The degrees of freedom of the T distribution." ; + }; + String 6 // Name of Parameter 3 + { + Text [ en-US ] = "cumulative" ; + }; + String 7 // Description of Parameter 3 + { + Text [ en-US ] = "True calculates the cumulative distribution function, false the probability density function." ; + }; + }; + // -=*# Resource for function T.DIST.RT #*=- + Resource SC_OPCODE_T_DIST_RT + { + String 1 // Description + { + Text [ en-US ] = "Returns the right-tailed t-distribution." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_TDIST_RT ); + 2; 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 T distribution is to be calculated." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "degrees_freedom" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The degrees of freedom of the T distribution." ; + }; }; // -=*# Resource for function TINV #*=- Resource SC_OPCODE_T_INV @@ -7632,6 +7736,70 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2 { Text [ en-US ] = "The degrees of freedom of the T distribution." ; }; + }; + // -=*# Resource for function T.INV #*=- + Resource SC_OPCODE_T_INV_MS + { + String 1 // Description + { + Text [ en-US ] = "Values of the left-tailed inverse t-distribution." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_TINV_MS ); + 2; 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 T distribution is to be calculated." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "degrees_freedom" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The degrees of freedom of the T distribution." ; + }; + }; + // -=*# Resource for function T.INV.2T #*=- + Resource SC_OPCODE_T_INV_2T + { + String 1 // Description + { + Text [ en-US ] = "Values of the two-tailed inverse t-distribution." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_TINV_2T ); + 2; 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 T distribution is to be calculated." ; + }; + String 4 // Name of Parameter 2 + { + Text [ en-US ] = "degrees_freedom" ; + }; + String 5 // Description of Parameter 2 + { + Text [ en-US ] = "The degrees of freedom of the T distribution." ; + }; }; // -=*# Resource for function FVERT #*=- Resource SC_OPCODE_F_DIST @@ -8601,6 +8769,54 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2 { Text [ en-US ] = "The type of the T test." ; }; + }; + // -=*# Resource for function T.TEST #*=- + Resource SC_OPCODE_T_TEST_MS + { + String 1 // Description + { + Text [ en-US ] = "Calculates the T test." ; + }; + ExtraData = + { + 0; + ID_FUNCTION_GRP_STATISTIC; + U2S( HID_FUNC_TTEST_MS ); + 4; 0; 0; 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." ; + }; + String 6 // Name of Parameter 3 + { + Text [ en-US ] = "mode" ; + }; + String 7 // Description of Parameter 3 + { + Text [ en-US ] = "Mode specifies the number of distribution tails to return. 1= one-tailed, 2 = two-tailed distribution" ; + }; + String 8 // Name of Parameter 4 + { + Text [ en-US ] = "Type" ; + }; + String 9 // Description of Parameter 4 + { + Text [ en-US ] = "The type of the T test." ; + }; }; // -=*# Resource for function BESTIMMTHEITSMASS #*=- Resource SC_OPCODE_RSQ -- cgit v1.2.3