summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorWinfried Donkers <winfrieddonkers@libreoffice.org>2014-01-08 17:15:22 +0100
committerEike Rathke <erack@redhat.com>2014-01-30 12:30:56 +0000
commit8185ecf65addcd0c10eda6863a849e39b93684d3 (patch)
tree404ce7ccdfbd870d5187ccad5417ab101b39cfed
parentf5115e33e4c5e49e9b79ed32fccc193a99c3dc30 (diff)
fdo#73146 Add Excel 2010 functions
PERCENTILE.EXC, PERCENTILE.INC PERCENTRANK.EXC, PERCENTRANK.INC QUARTILE.EXC, QUARTILE.INC RANK.EQ, RANK.AVG Function ScInterpreter::ScRank() has been rewritten to give the function the same way of computing as the related functions in this patch. Change-Id: If3096b265bd47ca5f4afcab7ff746f7c8511df82 Reviewed-on: https://gerrit.libreoffice.org/7319 Reviewed-by: Eike Rathke <erack@redhat.com> Tested-by: Eike Rathke <erack@redhat.com>
-rw-r--r--formula/source/core/resource/core_resource.src56
-rw-r--r--include/formula/compiler.hrc10
-rw-r--r--include/formula/opcode.hxx8
-rw-r--r--sc/inc/helpids.h8
-rw-r--r--sc/qa/unit/subsequent_filters-test.cxx16
-rw-r--r--sc/qa/unit/ucalc.cxx8
-rw-r--r--sc/source/core/inc/interpre.hxx9
-rw-r--r--sc/source/core/tool/interpr3.cxx301
-rw-r--r--sc/source/core/tool/interpr4.cxx14
-rw-r--r--sc/source/filter/excel/xlformula.cxx90
-rw-r--r--sc/source/filter/oox/formulabase.cxx10
-rw-r--r--sc/source/ui/src/scfuncs.src288
12 files changed, 626 insertions, 192 deletions
diff --git a/formula/source/core/resource/core_resource.src b/formula/source/core/resource/core_resource.src
index 4f7444167d02..d3b3ea351300 100644
--- a/formula/source/core/resource/core_resource.src
+++ b/formula/source/core/resource/core_resource.src
@@ -319,6 +319,14 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_ODFF
String SC_OPCODE_RANK { Text = "RANK" ; };
String SC_OPCODE_PERCENTILE { Text = "PERCENTILE" ; };
String SC_OPCODE_PERCENT_RANK { Text = "PERCENTRANK" ; };
+ String SC_OPCODE_PERCENTILE_INC { Text = "COM.MICROSOFT.PERCENTILE.INC" ; };
+ String SC_OPCODE_PERCENT_RANK_INC { Text = "COM.MICROSOFT.PERCENTRANK.INC" ; };
+ String SC_OPCODE_QUARTILE_INC { Text = "COM.MICROSOFT.QUARTILE.INC" ; };
+ String SC_OPCODE_RANK_EQ { Text = "COM.MICROSOFT.RANK.EQ" ; };
+ String SC_OPCODE_PERCENTILE_EXC { Text = "COM.MICROSOFT.PERCENTILE.EXC" ; };
+ String SC_OPCODE_PERCENT_RANK_EXC { Text = "COM.MICROSOFT.PERCENTRANK.EXC" ; };
+ String SC_OPCODE_QUARTILE_EXC { Text = "COM.MICROSOFT.QUARTILE.EXC" ; };
+ String SC_OPCODE_RANK_AVG { Text = "COM.MICROSOFT.RANK.AVG" ; };
String SC_OPCODE_LARGE { Text = "LARGE" ; };
String SC_OPCODE_SMALL { Text = "SMALL" ; };
String SC_OPCODE_FREQUENCY { Text = "FREQUENCY" ; };
@@ -705,6 +713,14 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH_OOXML
String SC_OPCODE_RANK { Text = "RANK" ; };
String SC_OPCODE_PERCENTILE { Text = "PERCENTILE" ; };
String SC_OPCODE_PERCENT_RANK { Text = "PERCENTRANK" ; };
+ String SC_OPCODE_PERCENTILE_INC { Text = "_xlfn.PERCENTILE.INC" ; };
+ String SC_OPCODE_PERCENT_RANK_INC { Text = "_xlfn.PERCENTRANK.INC" ; };
+ String SC_OPCODE_QUARTILE_INC { Text = "_xlfn.QUARTILE.INC" ; };
+ String SC_OPCODE_RANK_EQ { Text = "_xlfn.RANK.EQ" ; };
+ String SC_OPCODE_PERCENTILE_EXC { Text = "_xlfn.PERCENTILE.EXC" ; };
+ String SC_OPCODE_PERCENT_RANK_EXC { Text = "_xlfn.PERCENTRANK.EXC" ; };
+ String SC_OPCODE_QUARTILE_EXC { Text = "_xlfn.QUARTILE.EXC" ; };
+ String SC_OPCODE_RANK_AVG { Text = "_xlfn.RANK.AVG" ; };
String SC_OPCODE_LARGE { Text = "LARGE" ; };
String SC_OPCODE_SMALL { Text = "SMALL" ; };
String SC_OPCODE_FREQUENCY { Text = "FREQUENCY" ; };
@@ -1093,6 +1109,14 @@ Resource RID_STRLIST_FUNCTION_NAMES_ENGLISH
String SC_OPCODE_RANK { Text = "RANK" ; };
String SC_OPCODE_PERCENTILE { Text = "PERCENTILE" ; };
String SC_OPCODE_PERCENT_RANK { Text = "PERCENTRANK" ; };
+ String SC_OPCODE_PERCENTILE_INC { Text = "PERCENTILE.INC" ; };
+ String SC_OPCODE_PERCENT_RANK_INC { Text = "PERCENTRANK.INC" ; };
+ String SC_OPCODE_QUARTILE_INC { Text = "QUARTILE.INC" ; };
+ String SC_OPCODE_RANK_EQ { Text = "RANK.EQ" ; };
+ String SC_OPCODE_PERCENTILE_EXC { Text = "PERCENTILE.EXC" ; };
+ String SC_OPCODE_PERCENT_RANK_EXC { Text = "PERCENTRANK.EXC" ; };
+ String SC_OPCODE_QUARTILE_EXC { Text = "QUARTILE.EXC" ; };
+ String SC_OPCODE_RANK_AVG { Text = "RANK.AVG" ; };
String SC_OPCODE_LARGE { Text = "LARGE" ; };
String SC_OPCODE_SMALL { Text = "SMALL" ; };
String SC_OPCODE_FREQUENCY { Text = "FREQUENCY" ; };
@@ -2297,6 +2321,38 @@ Resource RID_STRLIST_FUNCTION_NAMES
{
Text [ en-US ] = "PERCENTRANK" ;
};
+ String SC_OPCODE_PERCENTILE_INC
+ {
+ Text [ en-US ] = "PERCENTILE.INC" ;
+ };
+ String SC_OPCODE_PERCENT_RANK_INC
+ {
+ Text [ en-US ] = "PERCENTRANK.INC" ;
+ };
+ String SC_OPCODE_QUARTILE_INC
+ {
+ Text [ en-US ] = "QUARTILE.INC" ;
+ };
+ String SC_OPCODE_RANK_EQ
+ {
+ Text [ en-US ] = "RANK.EQ" ;
+ };
+ String SC_OPCODE_PERCENTILE_EXC
+ {
+ Text [ en-US ] = "PERCENTILE.EXC" ;
+ };
+ String SC_OPCODE_PERCENT_RANK_EXC
+ {
+ Text [ en-US ] = "PERCENTRANK.EXC" ;
+ };
+ String SC_OPCODE_QUARTILE_EXC
+ {
+ Text [ en-US ] = "QUARTILE.EXC" ;
+ };
+ String SC_OPCODE_RANK_AVG
+ {
+ Text [ en-US ] = "RANK.AVG" ;
+ };
String SC_OPCODE_LARGE
{
Text [ en-US ] = "LARGE" ;
diff --git a/include/formula/compiler.hrc b/include/formula/compiler.hrc
index c4c20c27cff6..95abb545022e 100644
--- a/include/formula/compiler.hrc
+++ b/include/formula/compiler.hrc
@@ -448,8 +448,16 @@
#define SC_OPCODE_T_INV_2T 450
#define SC_OPCODE_T_INV_MS 451
#define SC_OPCODE_T_TEST_MS 452
+#define SC_OPCODE_PERCENTILE_INC 453
+#define SC_OPCODE_PERCENT_RANK_INC 454
+#define SC_OPCODE_QUARTILE_INC 455
+#define SC_OPCODE_RANK_EQ 456
+#define SC_OPCODE_PERCENTILE_EXC 457
+#define SC_OPCODE_PERCENT_RANK_EXC 458
+#define SC_OPCODE_QUARTILE_EXC 459
+#define SC_OPCODE_RANK_AVG 460
-#define SC_OPCODE_STOP_2_PAR 453 /* last function with two or more parameters' OpCode + 1 */
+#define SC_OPCODE_STOP_2_PAR 461 /* 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 bc7fe0fa6d84..168fa75270a2 100644
--- a/include/formula/opcode.hxx
+++ b/include/formula/opcode.hxx
@@ -364,6 +364,14 @@ enum OpCodeEnum
ocRank = SC_OPCODE_RANK,
ocPercentile = SC_OPCODE_PERCENTILE,
ocPercentrank = SC_OPCODE_PERCENT_RANK,
+ ocPercentile_Inc = SC_OPCODE_PERCENTILE_INC,
+ ocPercentrank_Inc = SC_OPCODE_PERCENT_RANK_INC,
+ ocQuartile_Inc = SC_OPCODE_QUARTILE_INC,
+ ocRank_Eq = SC_OPCODE_RANK_EQ,
+ ocPercentile_Exc = SC_OPCODE_PERCENTILE_EXC,
+ ocPercentrank_Exc = SC_OPCODE_PERCENT_RANK_EXC,
+ ocQuartile_Exc = SC_OPCODE_QUARTILE_EXC,
+ ocRank_Avg = SC_OPCODE_RANK_AVG,
ocLarge = SC_OPCODE_LARGE,
ocSmall = SC_OPCODE_SMALL,
ocFrequency = SC_OPCODE_FREQUENCY,
diff --git a/sc/inc/helpids.h b/sc/inc/helpids.h
index fbdef8242fdd..21d6390e08a4 100644
--- a/sc/inc/helpids.h
+++ b/sc/inc/helpids.h
@@ -703,5 +703,13 @@
#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"
+#define HID_FUNC_PERCENTILE_EXC "SC_HID_FUNC_PERCENTILE_EXC"
+#define HID_FUNC_PERCENTILE_INC "SC_HID_FUNC_PERCENTILE_INC"
+#define HID_FUNC_PERCENTRANK_EXC "SC_HID_FUNC_PERCENTRANK_EXC"
+#define HID_FUNC_PERCENTRANK_INC "SC_HID_FUNC_PERCENTRANK_INC"
+#define HID_FUNC_QUARTILE_INC "SC_HID_FUNC_QUARTILE_INC"
+#define HID_FUNC_QUARTILE_EXC "SC_HID_FUNC_QUARTILE_EXC"
+#define HID_FUNC_RANK_EQ "SC_HID_FUNC_RANK_EQ"
+#define HID_FUNC_RANK_AVG "SC_HID_FUNC_RANK_AVG"
/* 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 3fae9e0c26ce..003fdd06fba5 100644
--- a/sc/qa/unit/subsequent_filters-test.cxx
+++ b/sc/qa/unit/subsequent_filters-test.cxx
@@ -448,16 +448,16 @@ void ScFiltersTest::testFunctionsExcel2010()
{ 47, true },
{ 48, true },
{ 49, true },
- { 50, false },
- { 51, false },
- { 52, false },
- { 53, false },
+ { 50, true },
+ { 51, true },
+ { 52, true },
+ { 53, true },
{ 54, true },
{ 55, true },
- { 56, false },
- { 57, false },
- { 58, false },
- { 59, false },
+ { 56, true },
+ { 57, true },
+ { 58, true },
+ { 59, true },
{ 60, true },
{ 61, true },
{ 62, true },
diff --git a/sc/qa/unit/ucalc.cxx b/sc/qa/unit/ucalc.cxx
index adab89fb9b0f..7e9bba755bbf 100644
--- a/sc/qa/unit/ucalc.cxx
+++ b/sc/qa/unit/ucalc.cxx
@@ -2750,7 +2750,11 @@ void Test::testFunctionLists()
"NORMSINV",
"PEARSON",
"PERCENTILE",
+ "PERCENTILE.EXC",
+ "PERCENTILE.INC",
"PERCENTRANK",
+ "PERCENTRANK.EXC",
+ "PERCENTRANK.INC",
"PERMUT",
"PERMUTATIONA",
"PHI",
@@ -2758,7 +2762,11 @@ void Test::testFunctionLists()
"POISSON.DIST",
"PROB",
"QUARTILE",
+ "QUARTILE.EXC",
+ "QUARTILE.INC",
"RANK",
+ "RANK.AVG",
+ "RANK.EQ",
"RSQ",
"SKEW",
"SKEWP",
diff --git a/sc/source/core/inc/interpre.hxx b/sc/source/core/inc/interpre.hxx
index 51d94e54fd9b..4e017c4dfe1e 100644
--- a/sc/source/core/inc/interpre.hxx
+++ b/sc/source/core/inc/interpre.hxx
@@ -777,6 +777,7 @@ void ScSkewp();
void ScMedian();
double GetMedian( ::std::vector<double> & rArray );
double GetPercentile( ::std::vector<double> & rArray, double fPercentile );
+double GetPercentileExclusive( ::std::vector<double> & rArray, double fPercentile );
void GetNumberSequenceArray( sal_uInt8 nParamCount, ::std::vector<double>& rArray );
void GetSortArray(sal_uInt8 nParamCount, ::std::vector<double>& rSortArray, ::std::vector<long>* pIndexOrder = NULL);
void QuickSort(::std::vector<double>& rSortArray, ::std::vector<long>* pIndexOrder = NULL);
@@ -787,13 +788,15 @@ void ScZTest();
void ScTTest();
void ScFTest();
void ScChiTest();
-void ScRank();
-void ScPercentile();
+void ScRank( bool bAverage );
+void ScPercentile( bool bInclusive );
void ScPercentrank();
+void ScPercentrank_MS( bool bInclusive );
+double GetPercentrank( ::std::vector<double> & rArray, double fVal, bool bInclusive );
void ScLarge();
void ScSmall();
void ScFrequency();
-void ScQuartile();
+void ScQuartile( bool bInclusive );
void ScNormInv();
void ScSNormInv();
void ScConfidence();
diff --git a/sc/source/core/tool/interpr3.cxx b/sc/source/core/tool/interpr3.cxx
index bd02398cfe34..03f6c5b770e8 100644
--- a/sc/source/core/tool/interpr3.cxx
+++ b/sc/source/core/tool/interpr3.cxx
@@ -3358,34 +3358,71 @@ double ScInterpreter::GetPercentile( vector<double> & rArray, double fPercentile
}
}
-void ScInterpreter::ScPercentile()
+double ScInterpreter::GetPercentileExclusive( vector<double> & rArray, double fPercentile )
+{
+ size_t nSize1 = rArray.size() + 1;
+ if ( rArray.empty() || nSize1 == 1 || nGlobalError )
+ {
+ SetError( errNoValue );
+ return 0.0;
+ }
+ if ( fPercentile * nSize1 < 1.0 || fPercentile * nSize1 > (double) ( nSize1 - 1 ) )
+ {
+ SetError( errIllegalParameter );
+ return 0.0;
+ }
+
+ size_t nIndex = (size_t)::rtl::math::approxFloor( fPercentile * nSize1 - 1 );
+ double fDiff = fPercentile * nSize1 - 1 - ::rtl::math::approxFloor( fPercentile * nSize1 - 1 );
+ OSL_ENSURE(nIndex < ( nSize1 - 1 ), "GetPercentile: wrong index(1)");
+ vector<double>::iterator iter = rArray.begin() + nIndex;
+ ::std::nth_element( rArray.begin(), iter, rArray.end());
+ if (fDiff == 0.0)
+ return *iter;
+ else
+ {
+ OSL_ENSURE(nIndex < nSize1, "GetPercentile: wrong index(2)");
+ double fVal = *iter;
+ iter = rArray.begin() + nIndex + 1;
+ ::std::nth_element( rArray.begin(), iter, rArray.end());
+ return fVal + fDiff * (*iter - fVal);
+ }
+}
+
+void ScInterpreter::ScPercentile( bool bInclusive )
{
if ( !MustHaveParamCount( GetByte(), 2 ) )
return;
double alpha = GetDouble();
- if (alpha < 0.0 || alpha > 1.0)
+ if ( bInclusive ? ( alpha < 0.0 || alpha > 1.0 ) : ( alpha <= 0.0 || alpha >= 1.0 ) )
{
PushIllegalArgument();
return;
}
vector<double> aArray;
GetNumberSequenceArray( 1, aArray);
- PushDouble( GetPercentile( aArray, alpha));
+ if ( bInclusive )
+ PushDouble( GetPercentile( aArray, alpha ));
+ else
+ PushDouble( GetPercentileExclusive( aArray, alpha ));
}
-void ScInterpreter::ScQuartile()
+void ScInterpreter::ScQuartile( bool bInclusive )
{
if ( !MustHaveParamCount( GetByte(), 2 ) )
return;
double fFlag = ::rtl::math::approxFloor(GetDouble());
- if (fFlag < 0.0 || fFlag > 4.0)
+ if ( bInclusive ? ( fFlag < 0.0 || fFlag > 4.0 ) : ( fFlag <= 0.0 || fFlag >= 4.0 ) )
{
PushIllegalArgument();
return;
}
vector<double> aArray;
GetNumberSequenceArray( 1, aArray);
- PushDouble( fFlag == 2.0 ? GetMedian( aArray) : GetPercentile( aArray, 0.25 * fFlag));
+ if ( bInclusive )
+ PushDouble( fFlag == 2.0 ? GetMedian( aArray ) : GetPercentile( aArray, 0.25 * fFlag ) );
+ else
+ PushDouble( fFlag == 2.0 ? GetMedian( aArray ) : GetPercentileExclusive( aArray, 0.25 * fFlag ) );
}
void ScInterpreter::ScModalValue()
@@ -3491,44 +3528,98 @@ void ScInterpreter::ScPercentrank()
else if ( nSize == 1 )
PushDouble(1.0); // fNum == pSortArray[0], see test above
else
+ PushDouble( GetPercentrank( aSortArray, fNum, true ) );
+ }
+}
+
+void ScInterpreter::ScPercentrank_MS( bool bInclusive )
+{
+ sal_uInt8 nParamCount = GetByte();
+ if ( !MustHaveParamCount( nParamCount, 2, 3 ) )
+ return;
+ double fSignificance = ( nParamCount == 3 ? ::rtl::math::approxFloor( GetDouble() ) : 3.0 );
+ double fNum = GetDouble();
+ vector<double> aSortArray;
+ GetSortArray( 1, aSortArray );
+ SCSIZE nSize = aSortArray.size();
+ if ( aSortArray.empty() || nSize == 0 || nGlobalError )
+ PushNoValue();
+ else
+ {
+ if ( fNum < aSortArray[ 0 ] || fNum > aSortArray[ nSize - 1 ] )
+ PushNoValue();
+ else
{
double fRes;
- SCSIZE nOldCount = 0;
- double fOldVal = aSortArray[0];
- SCSIZE i;
- for (i = 1; i < nSize && aSortArray[i] < fNum; i++)
+ if ( nSize == 1 )
+ fRes = 1.0; // fNum == aSortArray[ 0 ], see test above
+ else
+ fRes = GetPercentrank( aSortArray, fNum, bInclusive );
+ if ( fRes != 0.0 )
{
- if (aSortArray[i] != fOldVal)
- {
- nOldCount = i;
- fOldVal = aSortArray[i];
- }
+ double fExp = ::rtl::math::approxFloor( log( fRes ) );
+ fRes = ::rtl::math::round( fRes * pow( 10, -fExp + fSignificance - 1 ) ) / pow( 10, -fExp + fSignificance - 1 );
}
- if (aSortArray[i] != fOldVal)
+ PushDouble( fRes );
+ }
+ }
+}
+
+double ScInterpreter::GetPercentrank( ::std::vector<double> & rArray, double fVal, bool bInclusive )
+{
+ SCSIZE nSize = rArray.size();
+ double fRes;
+ if ( fVal == rArray[ 0 ] )
+ {
+ if ( bInclusive )
+ fRes = 0.0;
+ else
+ fRes = 1.0 / ( double )( nSize + 1 );
+ }
+ else
+ {
+ SCSIZE nOldCount = 0;
+ double fOldVal = rArray[ 0 ];
+ SCSIZE i;
+ for ( i = 1; i < nSize && rArray[ i ] < fVal; i++ )
+ {
+ if ( rArray[ i ] != fOldVal )
+ {
nOldCount = i;
- if (fNum == aSortArray[i])
- fRes = (double)nOldCount/(double)(nSize-1);
+ fOldVal = rArray[ i ];
+ }
+ }
+ if ( rArray[ i ] != fOldVal )
+ nOldCount = i;
+ if ( fVal == rArray[ i ] )
+ {
+ if ( bInclusive )
+ fRes = ( double )nOldCount / ( double )( nSize - 1 );
else
+ fRes = ( double )( i + 1 ) / ( double )( nSize + 1 );
+ }
+ else
+ {
+ // nOldCount is the count of smaller entries
+ // fVal is between rArray[ nOldCount - 1 ] and rArray[ nOldCount ]
+ // use linear interpolation to find a position between the entries
+ if ( nOldCount == 0 )
{
- // nOldCount is the count of smaller entries
- // fNum is between pSortArray[nOldCount-1] and pSortArray[nOldCount]
- // use linear interpolation to find a position between the entries
-
- if ( nOldCount == 0 )
- {
- OSL_FAIL("should not happen");
- fRes = 0.0;
- }
+ OSL_FAIL( "should not happen" );
+ fRes = 0.0;
+ }
+ else
+ {
+ double fFract = ( fVal - rArray[ nOldCount - 1 ] ) /
+ ( rArray[ nOldCount ] - rArray[ nOldCount - 1 ] );
+ if ( bInclusive )
+ fRes = ( ( double )( nOldCount - 1 ) + fFract ) / ( double )( nSize - 1 );
else
- {
- double fFract = ( fNum - aSortArray[nOldCount-1] ) /
- ( aSortArray[nOldCount] - aSortArray[nOldCount-1] );
- fRes = ( (double)(nOldCount-1)+fFract )/(double)(nSize-1);
- }
+ fRes = ( ( double )nOldCount + fFract ) / ( double )( nSize + 1 );
}
- PushDouble(fRes);
}
}
+ return fRes;
}
void ScInterpreter::ScTrimMean()
@@ -3735,126 +3826,66 @@ void ScInterpreter::QuickSort( vector<double>& rSortArray, vector<long>* pIndexO
lcl_QuickSort(0, n-1, rSortArray, pIndexOrder);
}
-void ScInterpreter::ScRank()
+void ScInterpreter::ScRank( bool bAverage )
{
sal_uInt8 nParamCount = GetByte();
if ( !MustHaveParamCount( nParamCount, 2, 3 ) )
return;
- bool bDescending;
- if (nParamCount == 3)
- bDescending = GetBool();
+ bool bAscending;
+ if ( nParamCount == 3 )
+ bAscending = GetBool();
+ else
+ bAscending = false;
+
+ vector<double> aSortArray;
+ GetSortArray( 1, aSortArray );
+ double fVal = GetDouble();
+ SCSIZE nSize = aSortArray.size();
+ if ( aSortArray.empty() || nSize == 0 || nGlobalError )
+ PushNoValue();
else
- bDescending = false;
- double fCount = 1.0;
- bool bValid = false;
- switch (GetStackType())
{
- case formula::svDouble :
- {
- double x = GetDouble();
- double fVal = GetDouble();
- if (x == fVal)
- bValid = true;
- break;
- }
- case svSingleRef :
- {
- ScAddress aAdr;
- PopSingleRef( aAdr );
- double fVal = GetDouble();
- ScRefCellValue aCell;
- aCell.assign(*pDok, aAdr);
- if (aCell.hasNumeric())
- {
- double x = GetCellValue(aAdr, aCell);
- if (x == fVal)
- bValid = true;
- }
- break;
- }
- case formula::svDoubleRef :
- case svRefList :
+ if ( fVal < aSortArray[ 0 ] || fVal > aSortArray[ nSize - 1 ] )
+ PushNoValue();
+ else
{
- ScRange aRange;
- short nParam = 1;
- size_t nRefInList = 0;
- while (nParam-- > 0)
+ double fLastPos, fFirstPos = -1.0;
+ bool bFinished = false;
+ SCSIZE i;
+ for ( i = 0; i < nSize && !bFinished && !nGlobalError; i++ )
{
- sal_uInt16 nErr = 0;
- // Preserve stack until all RefList elements are done!
- sal_uInt16 nSaveSP = sp;
- PopDoubleRef( aRange, nParam, nRefInList);
- if (nParam)
- --sp; // simulate pop
- double fVal = GetDouble();
- if (nParam)
- sp = nSaveSP;
- double nCellVal;
- ScValueIterator aValIter(pDok, aRange, glSubTotal);
- if (aValIter.GetFirst(nCellVal, nErr))
+ if ( aSortArray[ i ] == fVal )
{
- if (nCellVal == fVal)
- bValid = true;
- else if ((!bDescending && nCellVal > fVal) ||
- (bDescending && nCellVal < fVal) )
- fCount++;
- SetError(nErr);
- while ((nErr == 0) && aValIter.GetNext(nCellVal, nErr))
- {
- if (nCellVal == fVal)
- bValid = true;
- else if ((!bDescending && nCellVal > fVal) ||
- (bDescending && nCellVal < fVal) )
- fCount++;
- }
+ if ( fFirstPos < 0 )
+ fFirstPos = i + 1.0;
}
- SetError(nErr);
- }
- }
- break;
- case svMatrix :
- case svExternalSingleRef:
- case svExternalDoubleRef:
- {
- ScMatrixRef pMat = GetMatrix();
- double fVal = GetDouble();
- if (pMat)
- {
- SCSIZE nCount = pMat->GetElementCount();
- if (pMat->IsNumeric())
+ else
{
- for (SCSIZE i = 0; i < nCount; i++)
+ if ( aSortArray[ i ] > fVal )
{
- double x = pMat->GetDouble(i);
- if (x == fVal)
- bValid = true;
- else if ((!bDescending && x > fVal) ||
- (bDescending && x < fVal) )
- fCount++;
+ fLastPos = i;
+ bFinished = true;
}
}
+ }
+ if ( !bFinished )
+ fLastPos = i;
+ if ( !bAverage )
+ {
+ if ( bAscending )
+ PushDouble( fFirstPos );
else
- {
- for (SCSIZE i = 0; i < nCount; i++)
- if (!pMat->IsString(i))
- {
- double x = pMat->GetDouble(i);
- if (x == fVal)
- bValid = true;
- else if ((!bDescending && x > fVal) ||
- (bDescending && x < fVal) )
- fCount++;
- }
- }
+ PushDouble( nSize + 1.0 - fLastPos );
+ }
+ else
+ {
+ if ( bAscending )
+ PushDouble( ( fFirstPos + fLastPos ) / 2.0 );
+ else
+ PushDouble( nSize + 1.0 - ( fFirstPos + fLastPos ) / 2.0 );
}
}
- break;
- default : SetError(errIllegalParameter); break;
}
- if (bValid)
- PushDouble(fCount);
- else
- PushNoValue();
}
void ScInterpreter::ScAveDev()
diff --git a/sc/source/core/tool/interpr4.cxx b/sc/source/core/tool/interpr4.cxx
index 6b38b5f202ea..7d22ea9b330a 100644
--- a/sc/source/core/tool/interpr4.cxx
+++ b/sc/source/core/tool/interpr4.cxx
@@ -4148,13 +4148,21 @@ StackVar ScInterpreter::Interpret()
case ocTTest_MS : ScTTest(); break;
case ocFTest :
case ocFTest_MS : ScFTest(); break;
- case ocRank : ScRank(); break;
- case ocPercentile : ScPercentile(); break;
+ case ocRank :
+ case ocRank_Eq : ScRank( false ); break;
+ case ocRank_Avg : ScRank( true ); break;
+ case ocPercentile :
+ case ocPercentile_Inc : ScPercentile( true ); break;
+ case ocPercentile_Exc : ScPercentile( false ); break;
case ocPercentrank : ScPercentrank(); break;
+ case ocPercentrank_Inc : ScPercentrank_MS( true ); break;
+ case ocPercentrank_Exc : ScPercentrank_MS( false ); break;
case ocLarge : ScLarge(); break;
case ocSmall : ScSmall(); break;
case ocFrequency : ScFrequency(); break;
- case ocQuartile : ScQuartile(); break;
+ case ocQuartile :
+ case ocQuartile_Inc : ScQuartile( true ); break;
+ case ocQuartile_Exc : ScQuartile( false ); break;
case ocNormInv :
case ocNormInv_MS : ScNormInv(); break;
case ocSNormInv :
diff --git a/sc/source/filter/excel/xlformula.cxx b/sc/source/filter/excel/xlformula.cxx
index 66f0d10426ae..4895721f96f7 100644
--- a/sc/source/filter/excel/xlformula.cxx
+++ b/sc/source/filter/excel/xlformula.cxx
@@ -429,47 +429,55 @@ static const XclFunctionInfo saFuncTable_Oox[] =
*/
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" ),
- EXC_FUNCENTRY_V_RX( ocStDevP_MS, 1, MX, 0, "STDEV.P" ),
- EXC_FUNCENTRY_V_RX( ocStDevS, 1, MX, 0, "STDEV.S" ),
- EXC_FUNCENTRY_V_RX( ocVarP_MS, 1, MX, 0, "VAR.P" ),
- EXC_FUNCENTRY_V_RX( ocVarS, 1, MX, 0, "VAR.S" ),
- EXC_FUNCENTRY_V_VR( ocBetaDist_MS, 4, 6, 0, "BETA.DIST" ),
- EXC_FUNCENTRY_V_VR( ocBetaInv_MS, 3, 5, 0, "BETA.INV" ),
- EXC_FUNCENTRY_V_VR( ocBinomDist_MS, 4, 4, 0, "BINOM.DIST" ),
- EXC_FUNCENTRY_V_VR( ocBinomInv, 3, 3, 0, "BINOM.INV" ),
- EXC_FUNCENTRY_V_VR( ocChiSqDist_MS, 3, 3, 0, "CHISQ.DIST" ),
- 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( ocConfidence_N, 3, 3, 0, "CONFIDENCE.NORM" ),
- 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" ),
- EXC_FUNCENTRY_V_VR( ocExpDist_MS, 3, 3, 0, "EXPON.DIST" ),
- EXC_FUNCENTRY_V_VR( ocHypGeomDist_MS, 5, 5, 0, "HYPGEOM.DIST" ),
- EXC_FUNCENTRY_V_VR( ocPoissonDist_MS, 3, 3, 0, "POISSON.DIST" ),
- EXC_FUNCENTRY_V_VR( ocWeibull_MS, 4, 4, 0, "WEIBULL.DIST" ),
- EXC_FUNCENTRY_V_VR( ocGammaDist_MS, 4, 4, 0, "GAMMA.DIST" ),
- EXC_FUNCENTRY_V_VR( ocGammaInv_MS, 3, 3, 0, "GAMMA.INV" ),
- EXC_FUNCENTRY_V_VR( ocGammaLn_MS, 1, 1, 0, "GAMMALN.PRECISE" ),
- EXC_FUNCENTRY_V_VR( ocLogNormDist_MS, 4, 4, 0, "LOGNORM.DIST" ),
- EXC_FUNCENTRY_V_VR( ocLogInv_MS, 3, 3, 0, "LOGNORM.INV" ),
- 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( 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" )
+ EXC_FUNCENTRY_V_VA( ocCovarianceP, 2, 2, 0, "COVARIANCE.P" ),
+ EXC_FUNCENTRY_V_VA( ocCovarianceS, 2, 2, 0, "COVARIANCE.S" ),
+ EXC_FUNCENTRY_V_RX( ocStDevP_MS, 1, MX, 0, "STDEV.P" ),
+ EXC_FUNCENTRY_V_RX( ocStDevS, 1, MX, 0, "STDEV.S" ),
+ EXC_FUNCENTRY_V_RX( ocVarP_MS, 1, MX, 0, "VAR.P" ),
+ EXC_FUNCENTRY_V_RX( ocVarS, 1, MX, 0, "VAR.S" ),
+ EXC_FUNCENTRY_V_VR( ocBetaDist_MS, 4, 6, 0, "BETA.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocBetaInv_MS, 3, 5, 0, "BETA.INV" ),
+ EXC_FUNCENTRY_V_VR( ocBinomDist_MS, 4, 4, 0, "BINOM.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocBinomInv, 3, 3, 0, "BINOM.INV" ),
+ EXC_FUNCENTRY_V_VR( ocChiSqDist_MS, 3, 3, 0, "CHISQ.DIST" ),
+ 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( ocConfidence_N, 3, 3, 0, "CONFIDENCE.NORM" ),
+ 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" ),
+ EXC_FUNCENTRY_V_VR( ocExpDist_MS, 3, 3, 0, "EXPON.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocHypGeomDist_MS, 5, 5, 0, "HYPGEOM.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocPoissonDist_MS, 3, 3, 0, "POISSON.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocWeibull_MS, 4, 4, 0, "WEIBULL.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocGammaDist_MS, 4, 4, 0, "GAMMA.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocGammaInv_MS, 3, 3, 0, "GAMMA.INV" ),
+ EXC_FUNCENTRY_V_VR( ocGammaLn_MS, 1, 1, 0, "GAMMALN.PRECISE" ),
+ EXC_FUNCENTRY_V_VR( ocLogNormDist_MS, 4, 4, 0, "LOGNORM.DIST" ),
+ EXC_FUNCENTRY_V_VR( ocLogInv_MS, 3, 3, 0, "LOGNORM.INV" ),
+ 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( 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" ),
+ EXC_FUNCENTRY_V_VR( ocPercentile_Inc, 2, 2, 0, "PERCENTILE.INC" ),
+ EXC_FUNCENTRY_V_VR( ocPercentrank_Inc, 2, 3, 0, "PERCENTRANK.INC" ),
+ EXC_FUNCENTRY_V_VR( ocQuartile_Inc, 2, 2, 0, "QUARTILE.INC" ),
+ EXC_FUNCENTRY_V_VR( ocRank_Eq, 2, 3, 0, "RANK.EQ" ),
+ EXC_FUNCENTRY_V_VR( ocPercentile_Exc, 2, 2, 0, "PERCENTILE.EXC" ),
+ EXC_FUNCENTRY_V_VR( ocPercentrank_Exc, 2, 3, 0, "PERCENTRANK.EXC" ),
+ EXC_FUNCENTRY_V_VR( ocQuartile_Exc, 2, 2, 0, "QUARTILE.EXC" ),
+ EXC_FUNCENTRY_V_VR( ocRank_Avg, 2, 3, 0, "RANK.AVG" )
};
/** Functions new in Excel 2013.
diff --git a/sc/source/filter/oox/formulabase.cxx b/sc/source/filter/oox/formulabase.cxx
index f0dd1ed1eac2..0fd11295274a 100644
--- a/sc/source/filter/oox/formulabase.cxx
+++ b/sc/source/filter/oox/formulabase.cxx
@@ -787,7 +787,15 @@ static const FunctionData saFuncTable2010[] =
{ "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 }
+ { "COM.MICROSOFT.T.TEST", "T.TEST", NOID, NOID, 4, 4, V, { VA, VA, VR }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.PERCENTILE.INC", "PERCENTILE.INC", NOID, NOID, 2, 2, V, { RX, VR }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.PERCENTRANK.INC", "PERCENTRANK.INC", NOID, NOID, 2, 3, V, { RX, VR, VR_E }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.QUARTILE.INC", "QUARTILE.INC", NOID, NOID, 2, 2, V, { RX, VR }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.RANK.EQ", "RANK.EQ", NOID, NOID, 2, 3, V, { VR, RO, VR }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.PERCENTILE.EXC", "PERCENTILE.EXC", NOID, NOID, 2, 2, V, { RX, VR }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.PERCENTRANK.EXC", "PERCENTRANK.EXC", NOID, NOID, 2, 3, V, { RX, VR, VR_E }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.QUARTILE.EXC", "QUARTILE.EXC", NOID, NOID, 2, 2, V, { RX, VR }, FUNCFLAG_MACROCALL_NEW },
+ { "COM.MICROSOFT.RANK.AVG", "RANK.AVG", NOID, NOID, 2, 3, V, { VR, RO, 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 cedf6328868d..ac97442eac4f 100644
--- a/sc/source/ui/src/scfuncs.src
+++ b/sc/source/ui/src/scfuncs.src
@@ -5647,6 +5647,70 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
Text [ en-US ] = "The percentage rate of the quantile between 0 and 1." ;
};
};
+ // -=*# Resource for function PERCENTILE.EXC #*=-
+ Resource SC_OPCODE_PERCENTILE_EXC
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the alpha percentile of a sample." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_PERCENTILE_EXC );
+ 2; 0; 0;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "data" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "Alpha" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The percentile value, range 0...1, exclusive." ;
+ };
+ };
+ // -=*# Resource for function PERCENTILE.INC #*=-
+ Resource SC_OPCODE_PERCENTILE_INC
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the alpha percentile of a sample." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_PERCENTILE_INC );
+ 2; 0; 0;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "data" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "Alpha" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The percentile value, range 0...1, inclusive." ;
+ };
+ };
// -=*# Resource for function QUARTILE #*=-
Resource SC_OPCODE_QUARTILE
{
@@ -5679,6 +5743,70 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
Text [ en-US ] = "The type of the quartile (0 = MIN, 1 = 25%, 2 = 50%, 3 = 75%, 4 = MAX)." ;
};
};
+ // -=*# Resource for function QUARTILE.EXC #*=-
+ Resource SC_OPCODE_QUARTILE_EXC
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the quartile of a sample." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_QUARTILE_EXC );
+ 2; 0; 0;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "data" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "Type" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The type of the quartile (1 = 25%, 2 = 50 %, 3 = 75 %)." ;
+ };
+ };
+ // -=*# Resource for function QUARTILE.INC #*=-
+ Resource SC_OPCODE_QUARTILE_INC
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the quartile of a sample." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_QUARTILE_INC );
+ 2; 0; 0;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "data" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "Type" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The type of the quartile (0 = MIN, 1 = 25%, 2 = 50 %, 3 = 75 %, 4 =MAX)." ;
+ };
+ };
// -=*# Resource for function KGRÖSSTE #*=-
Resource SC_OPCODE_LARGE
{
@@ -5775,6 +5903,86 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
Text [ en-US ] = "The value for which percentage ranking is to be determined." ;
};
};
+ // -=*# Resource for function PERCENTRANK.EXC #*=-
+ Resource SC_OPCODE_PERCENT_RANK_EXC
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the percentage rank (0..1, exclusive) of a value in a sample." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_PERCENTRANK_EXC );
+ 3; 0; 0; 1;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "data" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "value" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The value for which percentage ranking is to be determined." ;
+ };
+ String 6 // Name of Parameter 3
+ {
+ Text [ en-US ] = "significance" ;
+ };
+ String 7 // Description of Parameter 3
+ {
+ Text [ en-US ] = "The number of significant digits for the returned percentage: if ommitted, a value of 3 is used." ;
+ };
+ };
+ // -=*# Resource for function PERCENTRANK.INC #*=-
+ Resource SC_OPCODE_PERCENT_RANK_INC
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the percentage rank (0..1, inclusive) of a value in a sample." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_PERCENTRANK_INC );
+ 3; 0; 0; 1;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "data" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "value" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The value for which percentage ranking is to be determined." ;
+ };
+ String 6 // Name of Parameter 3
+ {
+ Text [ en-US ] = "significance" ;
+ };
+ String 7 // Description of Parameter 3
+ {
+ Text [ en-US ] = "The number of significant digits for the returned percentage: if ommitted, a value of 3 is used." ;
+ };
+ };
// -=*# Resource for function RANG #*=-
Resource SC_OPCODE_RANK
{
@@ -5815,6 +6023,86 @@ Resource RID_SC_FUNCTION_DESCRIPTIONS2
Text [ en-US ] = "Sequence order: 0 or omitted means descending, any other value than 0 means ascending." ;
};
};
+ // -=*# Resource for function RANK.EQ #*=-
+ Resource SC_OPCODE_RANK_EQ
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the ranking of a value in a sample; if more than one value has the same rank, the top rank of that set of values is returned." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_RANK_EQ );
+ 3; 0; 0; 1;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "value" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The value for which the rank is to be determined." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "Data" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 6 // Name of Parameter 3
+ {
+ Text [ en-US ] = "Type" ;
+ };
+ String 7 // Description of Parameter 3
+ {
+ Text [ en-US ] = "Sequence order: 0 or omitted means descending, any other value than 0 means ascending." ;
+ };
+ };
+ // -=*# Resource for function RANK.AVG #*=-
+ Resource SC_OPCODE_RANK_AVG
+ {
+ String 1 // Description
+ {
+ Text [ en-US ] = "Returns the ranking of a value in a sample; if more than one value has the same rank, the average rank is returned." ;
+ };
+ ExtraData =
+ {
+ 0;
+ ID_FUNCTION_GRP_STATISTIC;
+ U2S( HID_FUNC_RANK_AVG );
+ 3; 0; 0; 1;
+ 0;
+ };
+ String 2 // Name of Parameter 1
+ {
+ Text [ en-US ] = "value" ;
+ };
+ String 3 // Description of Parameter 1
+ {
+ Text [ en-US ] = "The value for which the rank is to be determined." ;
+ };
+ String 4 // Name of Parameter 2
+ {
+ Text [ en-US ] = "Data" ;
+ };
+ String 5 // Description of Parameter 2
+ {
+ Text [ en-US ] = "The array of the data in the sample." ;
+ };
+ String 6 // Name of Parameter 3
+ {
+ Text [ en-US ] = "Type" ;
+ };
+ String 7 // Description of Parameter 3
+ {
+ Text [ en-US ] = "Sequence order: 0 or omitted means descending, any other value than 0 means ascending." ;
+ };
+ };
// -=*# Resource for function GESTUTZTMITTEL #*=-
Resource SC_OPCODE_TRIM_MEAN
{