Difference between revisions of "Calc/Drafts/Treatment of new Excel 2010 functions"
(→CONFIDENCE.T) |
(typo) |
||
(23 intermediate revisions by one other user not shown) | |||
Line 1: | Line 1: | ||
This article is a workspace for discussing the treatment of new functions of Excel 2010 in OpenOffice.org '''code'''. For using functions in spreadsheets look at [[Documentation/How_Tos/Calc:_Functions_listed_alphabetically]] | This article is a workspace for discussing the treatment of new functions of Excel 2010 in OpenOffice.org '''code'''. For using functions in spreadsheets look at [[Documentation/How_Tos/Calc:_Functions_listed_alphabetically]] | ||
− | Please do not | + | Please do not change the layout to a table. Such table makes it hard to edit a single section. |
− | You want to help collecting the information? Try to add: Which parameters has the function? Does a corresponding OOo function exists already? Does a corresponding function exists in | + | You want to help collecting the information? Try to add: Which parameters has the function? Does a corresponding OOo function exists already? Does a corresponding function exists in OpenFormula? How difficult is it to implement a new function if necessary? Do you see any problems or obstacles? |
== Reference Documents == | == Reference Documents == | ||
− | The discussion uses the description of the new functions in [http://blogs.msdn.com/excel/attachment/9905140.ashx Function Improvements in Microsoft Office Excel 2010] and in [http://office2010.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx?CTT=1 What's New: Changes made to Excel functions] and the connected descriptions of the functions. | + | The discussion uses the description of the new functions in [http://blogs.msdn.com/excel/attachment/9905140.ashx Function Improvements in Microsoft Office Excel 2010] and in [http://office2010.microsoft.com/en-us/excel-help/what-s-new-changes-made-to-excel-functions-HA010355760.aspx?CTT=1 What's New: Changes made to Excel functions] and the connected descriptions of the functions. A further list can be found in chapter 2.2.3 of <nowiki> [MS-XLSX]: Excel Extensions to the Office Open XML SpreadsheetML File Format (.xlsx) Specification</nowiki> [http://msdn.microsoft.com/en-us/library/dd907480.aspx]. |
− | For | + | For OpenFormula the actual document from [http://www.oasis-open.org/committees/documents.php?wg_abbrev=office-formula OpenDocument - Formula SC Public Documents] is used. |
+ | |||
+ | [[File:Compare_Function_Category_Excel_ODF_OOo.ods]] Table of functions with their categories collected from Excel 2010 Beta, ODFF draft 28 and OOo3.2. | ||
== AGGREGATE == | == AGGREGATE == | ||
− | new | + | new |
+ | |||
+ | ;Excel: AGGREGATE(function_num, options, ref1, [ref2], …) or AGGREGATE(function_num, options, array, [k]) | ||
+ | ;OpenFormula: no such function specified | ||
+ | ;OOo3.2: no similar function implemented | ||
+ | |||
+ | === Comment === | ||
+ | Mapping is not possible, because it is not specified in OpenFormula. The function AGGREGATE extends the function SUBTOTAL with further functions and more options to decide, which rows are ignored. A mapping to SUBTOTAL would be only possible in very few cases, when the option is given as constant 0 or 5. For to map option 5 to SUBTOTAL it is needed that {{bug|35718}} is fixed. | ||
== BETA.DIST == | == BETA.DIST == | ||
Line 20: | Line 29: | ||
: The argument A may be empty, using two semicolons. | : The argument A may be empty, using two semicolons. | ||
: A <= x <= B | : A <= x <= B | ||
− | ; | + | ;OpenFormula: BETADIST( Number x ; Number alpha ; Number beta [ ; Number a = 0 [ ; Number b = 1 [ ; Logical Cumulative = TRUE() ] ] ] ) |
: alpha > 0, beta > 0, a < b | : alpha > 0, beta > 0, a < b | ||
: no constraints for x besides the cases with pole. | : no constraints for x besides the cases with pole. | ||
;OOo3.2: BETADIST(number; alpha; beta [; start [; end [; cumulative]]]) | ;OOo3.2: BETADIST(number; alpha; beta [; start [; end [; cumulative]]]) | ||
− | : implemented as in | + | : implemented as in OpenFormula defined |
=== Comment === | === Comment === | ||
Line 34: | Line 43: | ||
;Excel: BETA.INV(probability,alpha,beta,[A],[B]), default A=0 and B=1 | ;Excel: BETA.INV(probability,alpha,beta,[A],[B]), default A=0 and B=1 | ||
: The argument A may be empty, using two semicolons. | : The argument A may be empty, using two semicolons. | ||
− | ; | + | ;OpenFormula: BETAINV( Number p ; Number a ; Number b [ ; Number a = 0 [ ; Number b = 1 ] ] ) |
: | : | ||
;OOo3.2: BETAINV(number; alpha; beta; Start; End) | ;OOo3.2: BETAINV(number; alpha; beta; Start; End) | ||
− | : implemented as defined in | + | : implemented as defined in OpenFormula, but empty Start is possible. |
=== Comment === | === Comment === | ||
Line 44: | Line 53: | ||
== BINOM.DIST == | == BINOM.DIST == | ||
follow-up version of BINOMDIST | follow-up version of BINOMDIST | ||
+ | |||
+ | ;Excel: BINOM.DIST(number_s,trials,probability_s,cumulative) | ||
+ | : number_s and trials are truncated to integer | ||
+ | : cumulative is boolean, but numbers are accepted too | ||
+ | ;OpenFormula: BINOMDIST( Integer S ; Integer N ; Number P ; Logical Cumulative ) | ||
+ | ;OOo3.2: =BINOMDIST(X; trials; SP; C) | ||
+ | : X and trials are truncated to integer | ||
+ | |||
+ | === Comment === | ||
+ | direct mapping is possible | ||
== BINOM.INV == | == BINOM.INV == | ||
follow-up version of CRITBINOM | follow-up version of CRITBINOM | ||
+ | |||
+ | ;Excel: BINOM.INV(trials,probability_s,alpha), all arguments required | ||
+ | :trials are truncated to integer | ||
+ | ;OpenFormula: CRITBINOM( Number Trials ; Number SP ; Number Alpha ) | ||
+ | ;OOo3.2: CRITBINOM(trials; SP; alpha) | ||
+ | |||
+ | === Comment === | ||
+ | Direct mapping is possible. Notice that the domain for 'trails' is much larger in Excel 2010 than in OOo3.2. For example =BINOM.INV(500000;0.5;0.1) is possible in Excel 2010 but =CRITBINOM(1080;0.5;0.1) fails aready in OOo3.2. A new algorithm is needed. | ||
==CEILING.PRECISE == | ==CEILING.PRECISE == | ||
Line 53: | Line 80: | ||
;Excel: CEILING.PRECISE(number, [significance]) | ;Excel: CEILING.PRECISE(number, [significance]) | ||
: significance has default value 1, always the absolute value of significance is used | : significance has default value 1, always the absolute value of significance is used | ||
− | ; | + | ;OpenFormula: CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] ) |
: nested rules, round toward positive infinity for mode omitted or zero, round away from zero in the other cases. | : nested rules, round toward positive infinity for mode omitted or zero, round away from zero in the other cases. | ||
: N and significance must have the same sign if not 0. | : N and significance must have the same sign if not 0. | ||
Line 59: | Line 86: | ||
: Significance is required, {{bug|102957}} is still open, no decision about the patch there up to now. | : Significance is required, {{bug|102957}} is still open, no decision about the patch there up to now. | ||
=== Comment === | === Comment === | ||
− | Because | + | Because OpenFormula requires, that N and significance have the same sign, a direct mapping is not possible. |
<tt>CEILING.PRECISE(number)</tt> —with second argument missing— can be mapped to <tt>CEILING(number)</tt>. To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error. | <tt>CEILING.PRECISE(number)</tt> —with second argument missing— can be mapped to <tt>CEILING(number)</tt>. To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error. | ||
Line 74: | Line 101: | ||
:deg_freedom is truncated to integer, 0<deg_freedom<=10^10 | :deg_freedom is truncated to integer, 0<deg_freedom<=10^10 | ||
:cumulative is logical, but numbers are accepted too | :cumulative is logical, but numbers are accepted too | ||
− | ; | + | ;OpenFormula: CHISQDIST( Number x ; Number DegreesOfFreedom [ ; Logical Cumulative = TRUE() ] ) |
− | ;OOo3.2: is already implemented as CHISQDIST as | + | ;OOo3.2: is already implemented as CHISQDIST as OpenFormula defines |
:no constraints for x | :no constraints for x | ||
:DegreesOfFreedom are truncated as in Excel, DegreesOfFreedom > 0, no fixed upper bound for DegreesOfFreedom | :DegreesOfFreedom are truncated as in Excel, DegreesOfFreedom > 0, no fixed upper bound for DegreesOfFreedom | ||
Line 84: | Line 111: | ||
== CHISQ.DIST.RT == | == CHISQ.DIST.RT == | ||
follow-up version of CHIDIST | follow-up version of CHIDIST | ||
+ | |||
+ | ;Excel: CHISQ.DIST.RT(x,deg_freedom) | ||
+ | : 1 < deg_freedom < 10^10, deg_freedom is truncated to integer | ||
+ | ;OpenFormula: LEGACY.CHIDIST( Number x ; Number DegreesOfFreedom ) | ||
+ | : DegreesOfFreedom is positive interger | ||
+ | ;OOo3.2: CHIDIST(Number; degrees_freedom) | ||
+ | : no constraints, degrees_freedom is truncated to integer | ||
+ | |||
+ | === Comment === | ||
+ | Direct mapping is possible. | ||
== CHISQ.INV == | == CHISQ.INV == | ||
Line 90: | Line 127: | ||
:0<=probability<1, | :0<=probability<1, | ||
:1<=deg_freedom<=10^10, deg_freedom is truncated to integer | :1<=deg_freedom<=10^10, deg_freedom is truncated to integer | ||
− | ; | + | ;OpenFormula: CHISQINV( Number p ; Number DegreesOfFreedom ) |
:0<=p<1 | :0<=p<1 | ||
:DegreesOfFreedom is a positive integer | :DegreesOfFreedom is a positive integer | ||
− | ;OOo3.2: is already implemented as CHISQINV as | + | ;OOo3.2: is already implemented as CHISQINV as OpenFormula defines |
:0<=p<1 | :0<=p<1 | ||
:DegreesOfFreedom is truncated as in Excel, DegreesOfFreedom > 0, no fixed upper bound for DegreesOfFreedom, but calculation might not converge | :DegreesOfFreedom is truncated as in Excel, DegreesOfFreedom > 0, no fixed upper bound for DegreesOfFreedom, but calculation might not converge | ||
Line 102: | Line 139: | ||
== CHISQ.INV.RT == | == CHISQ.INV.RT == | ||
follow-up version of CHIINV | follow-up version of CHIINV | ||
+ | |||
+ | ;Excel: CHISQ.INV.RT(probability,deg_freedom) | ||
+ | : The iterative search technique is limited to 64 iterations | ||
+ | : deg_freedom is truncated to integer | ||
+ | ;OpenFormula: LEGACY.CHIINV( Number p ; Number DegreesOfFreedom ) | ||
+ | ;OOo3.2: CHININV(number; degrees_freedom) | ||
+ | : degrees_freedom is truncated to integer | ||
+ | === Comment === | ||
+ | direct mapping is possibel | ||
== CHISQ.TEST == | == CHISQ.TEST == | ||
follow-up version of CHITEST | follow-up version of CHITEST | ||
+ | |||
+ | ;Excel:CHISQ.TEST(actual_range,expected_range) | ||
+ | ;OpenFormula: LEGACY.CHITEST( ForceArray Array A ; ForceArray Array E ) | ||
+ | :The function was introduced in OpenFormula to be compatible with Excel and OpenOffice.org. | ||
+ | ;OOo3.2:CHITEST(Date_B; data:E) | ||
+ | |||
+ | === Comment === | ||
+ | direct mapping possible. | ||
== CONFIDENCE.NORM == | == CONFIDENCE.NORM == | ||
Line 111: | Line 165: | ||
;Excel: CONFIDENCE.NORM(alpha,standard_dev,size) | ;Excel: CONFIDENCE.NORM(alpha,standard_dev,size) | ||
: size is truncated to integer | : size is truncated to integer | ||
− | ; | + | ; OpenFormula: CONFIDENCE( Number alpha ; Number stddev ; Number size ) |
: is same as NORMINV(1 - alpha / 2; 0; 1) * stddev / SQRT (size) | : is same as NORMINV(1 - alpha / 2; 0; 1) * stddev / SQRT (size) | ||
;OOo3.2: CONFIDENCE(alpha; STDEV; size) | ;OOo3.2: CONFIDENCE(alpha; STDEV; size) | ||
: size is truncated to integer | : size is truncated to integer | ||
− | : implemented as defined in | + | : implemented as defined in OpenFormula |
=== Comment === | === Comment === | ||
Line 125: | Line 179: | ||
;Excel: CONFIDENCE.T(alpha,standard_dev,size) | ;Excel: CONFIDENCE.T(alpha,standard_dev,size) | ||
: calculates <math>\frac {standarddev} {\sqrt {size}} \cdot \operatorname{T.INV} (1-\frac {alpha} 2; size-1)</math> | : calculates <math>\frac {standarddev} {\sqrt {size}} \cdot \operatorname{T.INV} (1-\frac {alpha} 2; size-1)</math> | ||
− | ; | + | ;OpenFormula: no suitable function defined |
: | : | ||
;OOo3.2: no suitable function implemented | ;OOo3.2: no suitable function implemented | ||
=== Comment === | === Comment === | ||
− | Direct mapping is impossible, because no suitable function exists in | + | Direct mapping is impossible, because no suitable function exists in OpenFormula. |
For implementation <math>\frac {standarddev} {\sqrt {size}} \cdot \operatorname{TINV} (alpha; size-1)</math> is possible, where TINV is the already implemented function in OOo. | For implementation <math>\frac {standarddev} {\sqrt {size}} \cdot \operatorname{TINV} (alpha; size-1)</math> is possible, where TINV is the already implemented function in OOo. | ||
Line 140: | Line 194: | ||
: a pair with one text, logical or empty is ignored | : a pair with one text, logical or empty is ignored | ||
: <math>\textstyle \frac 1 N \sum_{k=1}^N (x_k-\bar X)(y_k-\bar Y)</math> | : <math>\textstyle \frac 1 N \sum_{k=1}^N (x_k-\bar X)(y_k-\bar Y)</math> | ||
− | ; | + | ;OpenFormula: COVAR( ForceArray Array n1 ; ForceArray Array n2 ) |
;OOo3.2: COVAR(Data_1; Data_2) | ;OOo3.2: COVAR(Data_1; Data_2) | ||
Line 152: | Line 206: | ||
: a pair with one text, logical or empty is ignored | : a pair with one text, logical or empty is ignored | ||
: <math>\textstyle \frac 1 {N-1} \sum_{k=1}^N (x_k-\bar X)(y_k-\bar Y)</math> | : <math>\textstyle \frac 1 {N-1} \sum_{k=1}^N (x_k-\bar X)(y_k-\bar Y)</math> | ||
− | ; | + | ;OpenFormula: no such function defined |
;OOo3.2: not implemented | ;OOo3.2: not implemented | ||
=== Comment === | === Comment === | ||
− | Direct mapping impossible, because a suitable function not contained in | + | Direct mapping impossible, because a suitable function not contained in OpenFormula. |
Implementation would be easy, just copy code from COVAR and use different denominator. | Implementation would be easy, just copy code from COVAR and use different denominator. | ||
+ | |||
+ | == ECMA.CEILING == | ||
+ | new | ||
+ | |||
+ | ;Excel: ECMA.CEILING(number, significance), all arguments are required | ||
+ | |||
+ | === Comment === | ||
+ | This function is mentioned in chapter 2.2.3 of <nowiki>[MS-XLSX]: Excel Extensions to the Office Open XML SpreadsheetML File Format (.xlsx) Specification </nowiki> [http://msdn.microsoft.com/en-us/library/dd907480.aspx], but it is not listed in the help inside Excel 2010 Beta. | ||
+ | |||
+ | It can be handled as CEILING.PRECISE. | ||
== EXPON.DIST == | == EXPON.DIST == | ||
Line 170: | Line 234: | ||
:deg_freedom1,deg_freedom2 are truncated to integer, both >=1 | :deg_freedom1,deg_freedom2 are truncated to integer, both >=1 | ||
:cumulative is boolean, but numbers are accepted too | :cumulative is boolean, but numbers are accepted too | ||
− | ; | + | ;OpenFormula: FDIST( Number x ; Number r1 ; Number r2 [ ; Logical Cumulative = TRUE() ] ) |
:no constraint for x | :no constraint for x | ||
:r1 and r2 are positive integers | :r1 and r2 are positive integers | ||
;OOo3.2: not implemented | ;OOo3.2: not implemented | ||
− | === | + | === Comment === |
This left tailed version has the same name as the right tailed version of OOo2. Because the version OOo2 cannot interpret namespaces, a simple solution is not possible. | This left tailed version has the same name as the right tailed version of OOo2. Because the version OOo2 cannot interpret namespaces, a simple solution is not possible. | ||
Line 196: | Line 260: | ||
new | new | ||
− | ;Excel:FLOOR.PRECISE(number, [significance]) | + | ;Excel:FLOOR.PRECISE(number, [significance]), significance has default 1 |
+ | |||
+ | ;OpenFormula: FLOOR( Number N [ ; [ Number significance ] [ ; Number mode ] ] ) | ||
+ | : nested rules, round toward negative infinity for mode omitted or zero, round toward zero in the other cases. | ||
+ | : N and significance must have the same sign if not 0 | ||
+ | |||
+ | ;OOo3.2: FLOOR(number; significance [; mode]) | ||
+ | :Significance is required, {{bug|102957}} is still open, no decision about the patch there up to now. | ||
+ | |||
+ | === Comment === | ||
+ | Because OpenFormula requires, that N and significance have the same sign, a direct mapping is not possible. | ||
+ | |||
+ | FLOOR.PRECISE(number) —with second argument missing— can be mapped to FLOOR(number). To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error. | ||
+ | |||
+ | With a given second argument, the composed formula FLOOR(number; SIGN(number)*ABS(significance)) would give the same result. But the question is, whether such change should be done at all. | ||
+ | |||
+ | see also CEILING.PRECISE | ||
== GAMMA.DIST == | == GAMMA.DIST == | ||
Line 212: | Line 292: | ||
;Excel: ISO.CEILING(number, [significance]) | ;Excel: ISO.CEILING(number, [significance]) | ||
: significance has default value 1, always the absolute value of significance is used | : significance has default value 1, always the absolute value of significance is used | ||
− | ; | + | ;OpenFormula: CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] ) |
: nested rules, round toward positive infinity for mode omitted or zero, round away from zero in the other cases. | : nested rules, round toward positive infinity for mode omitted or zero, round away from zero in the other cases. | ||
: N and significance must have the same sign if not 0. | : N and significance must have the same sign if not 0. | ||
Line 218: | Line 298: | ||
: Significance is required, {{bug|102957}} is still open, no decision about the patch there up to now. | : Significance is required, {{bug|102957}} is still open, no decision about the patch there up to now. | ||
=== Comment === | === Comment === | ||
− | Because | + | Because OpenFormula requires, that N and significance have the same sign, a direct mapping is not possible. |
<tt>ISO.CEILING(number)</tt> —with second argument missing— can be mapped to <tt>CEILING(number)</tt>. To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error. | <tt>ISO.CEILING(number)</tt> —with second argument missing— can be mapped to <tt>CEILING(number)</tt>. To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error. | ||
Line 233: | Line 313: | ||
== MODE.MULT == | == MODE.MULT == | ||
− | new | + | new |
+ | |||
+ | ;Excel: MODE.MULT((number1,[number2],...]) | ||
+ | :array function, the target cells have to be selected. | ||
+ | :The help does not mention, in which order the values are returned. Tests show, that values start with smallest cell position in order row, then column. | ||
+ | ;OpenFormula: no suitable function defined | ||
+ | ;OOo3.2: no suitable function implemented | ||
+ | |||
+ | === Comment === | ||
+ | A suitable function is neither defined in OpenFormula nor implemented in OOo3.2. So import is impossible. | ||
== MODE.SNGL == | == MODE.SNGL == | ||
follow-up version of MODE | follow-up version of MODE | ||
+ | |||
+ | ;Excel: MODE.SNGL(number1,[number2],...]) | ||
+ | : The help does not mention, which value is returned, if there are more than one value with the same largest frequency. Tests show, that it is the values with smallest cell position in order row, then column. | ||
+ | : up to 254 arguments | ||
+ | ; OpenFormula: MODE( { ForceArray NumberSequence N }+ ) | ||
+ | : If there are more than one value with the same largest frequency, the smallest one is returned. | ||
+ | ; OOo3.2: MODE(Number1; Number2; ...Number30), each argument can be a number or a range, only the first argument is requiered. | ||
+ | === Comment === | ||
+ | In simple cases the function returns the same result in Excel as in OOo. But in case of more than one value with the same largest frequency the result might differ. | ||
+ | |||
+ | The function can only be mapped on import, if there are not more than 30 arguments in Excel. | ||
+ | |||
+ | This problems are not new for MODE.SNGL, but exist already for the old MODE version. | ||
== NEGBINOM.DIST == | == NEGBINOM.DIST == | ||
Line 242: | Line 344: | ||
== NETWORKDAYS.INTL == | == NETWORKDAYS.INTL == | ||
− | new | + | new |
+ | |||
+ | ;Excel: NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) | ||
+ | : The argument weekend can be given as number 1..17 or as string containing seven 0 or 1, for example 1100000. | ||
+ | ;OpenFormula: NETWORKDAYS( DateParam Date1 ; DateParam Date2 [ ; [ DateSequence holidays ] [ ; LogicalSequence workdays ] ] ) | ||
+ | ;OOo3.2: NETWORKDAYS((Start_date; End_date [; Holidays]) | ||
+ | |||
+ | === Comment === | ||
+ | The existing function <tt>NETWORKDAYS(Start_date; End_date; Holidays)</tt> has to be expanded to accept a forth argument, see {{bug|71059}}. In addition it needs the capability to use empty arguments indicated by ;; to skip the argument holidays. | ||
+ | |||
+ | The syntax for the argument holidays is the same in all three versions. The argument workdays has the type LogicalSequence in OpenFormula, that is a term of kind {1,1,0,0,0,0,0}, but has the type string in Excel. The first position is attributed to Sunday, but in Excel the first position is attributed to Monday. If the argument weekend is given as constant, mapping is possible, but need some care in converting the argument value. If the argument weekend is given as reference, a direct mapping is not possible. | ||
+ | |||
+ | see also WORKDAY.INTL | ||
== NORM.DIST == | == NORM.DIST == | ||
Line 295: | Line 409: | ||
: deg_freedom is truncated to integer | : deg_freedom is truncated to integer | ||
: cumulative is boolean, but numbers are accepted | : cumulative is boolean, but numbers are accepted | ||
− | ; | + | ;OpenFormula: Currently only a right tail version is defined. But that will likely change till specification is final. |
;OOo: not implemented | ;OOo: not implemented | ||
=== Comment === | === Comment === | ||
− | An implementation of a left tail type and of a density function does not exist yet. The | + | An implementation of a left tail type and of a density function does not exist yet. The CDF is easily implemented via a call to <tt>ScInterpreter::GetBetaDist</tt>. The term of the density function does not look difficult and might be calculated directly or with ln in case of intermediate over/underflow. |
− | Because the | + | Because the OpenFormula definition might change from the current right tail version to left tail, the implementation should be postponed. |
== T.DIST.2T == | == T.DIST.2T == | ||
Line 328: | Line 442: | ||
== WORKDAY.INTL == | == WORKDAY.INTL == | ||
− | new | + | new |
+ | |||
+ | ;Excel: WORKDAY.INTL(start_date, days, [weekend], [holidays]) | ||
+ | : The argument weekend can be given as number 1..17 or as string containing seven 0 or 1, for example 1100000. | ||
+ | ;OpenFormula: WORKDAY( DateParam Date ; Number Offset [ ; [ DateSequence Holidays ] [ ; LogicalSequence Workdays ] ] ) | ||
+ | |||
+ | ;OOo: WORKDAY(Start_date; Days [;Holydays]) | ||
+ | |||
+ | === Comment === | ||
+ | The existing function <tt>WORKDAY(Start_date; End_date; Holidays)</tt> has to be expanded to accept a forth argument, see {{bug|71059}}. In addition it needs the capability to use empty arguments indicated by ;; to skip the argument holidays. | ||
+ | |||
+ | The syntax for the argument holidays is the same in all three versions. The argument workdays has the type LogicalSequence in OpenFormula, that is a term of kind {1,1,0,0,0,0,0}, but has the type string in Excel. The first position is attributed to Sunday, but in Excel the first position is attributed to Monday. If the argument weekend is given as constant, mapping is possible, but need some care in converting the argument value. If the argument weekend is given as reference, a direct mapping is not possible. | ||
+ | |||
+ | see also NETWORKDAYS.INTL | ||
== Z.TEST == | == Z.TEST == | ||
follow-up version of ZTEST | follow-up version of ZTEST | ||
+ | == Synopsis as Table == | ||
+ | |||
+ | {| class="wikitable" | ||
+ | |- | ||
+ | !Excel 2010 !!Kind !!OpenFormula !!Calc !!Conversion | ||
+ | |||
+ | |- | ||
+ | !AGGREGATE | ||
+ | |new || — || — ||style="background:#FF8080" |impossible | ||
+ | |||
+ | |- | ||
+ | !BETA.DIST | ||
+ | |follow-up version of BETADIST||BETADIST||BETADIST||style="background:#B2FFB2"|possible | ||
+ | |||
+ | |- | ||
+ | !BETA.INV | ||
+ | |follow-up version of BETAINV||BETAINV||BETAINV||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !BINOM.DIST | ||
+ | |follow-up version of BINOMDIST ||BINOMDIST||BINOMDIST||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !BINOM.INV | ||
+ | |follow-up version of CRITBINOM||CRITBINOM||CRITBINOM||style="background:#B2FFB2"|possible | ||
+ | |||
+ | |- | ||
+ | !CEILING.PRECISE | ||
+ | |new||CEILING||CEILING||style="background:#FFE600"|partly | ||
+ | |||
+ | |- | ||
+ | !CHISQ.DIST | ||
+ | |new||CHISQDIST||CHISQDIST||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !CHISQ.DIST.RT | ||
+ | |follow-up version of CHIDIST||LEGACY.CHIDIST||CHIDIST||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !CHISQ.INV | ||
+ | |new||CHISQINV||CHISQINV||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !CHISQ.INV.RT | ||
+ | |follow-up version of CHIINV||LEGACY.CHIINV||CHIINV||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !CHISQ.TEST | ||
+ | |follow-up version of CHITEST||LEGACY.CHITEST||CHITEST||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !CONFIDENCE.NORM | ||
+ | |follow-up version of CONFIDENCE||CONFIDENCE||CONFIDENCE||style="background:#00D900"|direct | ||
+ | |||
+ | |- | ||
+ | !CONFIDENCE.T | ||
+ | |new|| — || — ||style="background:#FF8080" |impossible | ||
+ | |||
+ | |- | ||
+ | !COVARIANCE.P | ||
+ | |follow-up version of COVAR||COVAR||COVAR||style="background:#00D900" |direct | ||
+ | |||
+ | |- | ||
+ | !COVARIANCE.S | ||
+ | |new|| — || — ||style="background:#FF8080" |impossible | ||
+ | |||
+ | |- | ||
+ | !ECMA.CEILING | ||
+ | |new||CEILING||CEILING||style="background:#FFE600" |partly | ||
+ | |||
+ | |- | ||
+ | !EXPON.DIST | ||
+ | |follow-up version of EXPONDIST || || || style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !F.DIST | ||
+ | |new||FDIST|| — ||style="background:#FF8080"|impossible | ||
+ | |||
+ | |- | ||
+ | !F.DIST.RT | ||
+ | |follow-up version of FDIST|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !F.INV | ||
+ | |new|| || || style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !F.INV.RT | ||
+ | |follow-up version of FINV|| || || style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !F.TEST | ||
+ | |follow-up version of FTEST|| || || style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !FLOOR.PRECISE | ||
+ | |new||FLOOR||FLOOR||style="background:#FFE600"| partly | ||
+ | |||
+ | |- | ||
+ | !GAMMA.DIST | ||
+ | |follow-up version of GAMMADIST|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !GAMMA.INV | ||
+ | |follow-up version of GAMMAINV|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !HYPGEOM.DIST | ||
+ | |follow-up version of HYPGEOMDIST|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !ISO.CEILING | ||
+ | |new||CEILING||CEILING||style="background:#FFE600" |partly | ||
+ | |||
+ | |- | ||
+ | !LOGNORM.DIST | ||
+ | |follow-up version of LOGNORMDIST|| || || style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !LOGNORM.INV | ||
+ | |follow-up version of LOGINV|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !MODE.MULT | ||
+ | |new|| — || — ||style="background:#FF8080" |impossible | ||
+ | |||
+ | |- | ||
+ | !MODE.SNGL | ||
+ | |follow-up version of MODE||MODE||MODE||style="background:#B2FFB2"|possible | ||
+ | |||
+ | |- | ||
+ | !NEGBINOM.DIST | ||
+ | |follow-up version of NEGBINOMDIST|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !NETWORKDAYS.INTL | ||
+ | |new||NETWORKDAYS||NETWORKDAYS||style="background:#FFE600" |partly | ||
+ | |||
+ | |- | ||
+ | !NORM.DIST | ||
+ | |follow-up version of NORMDIST|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !NORM.INV | ||
+ | |follow-up version of NORMINV|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !NORM.S.DIST | ||
+ | |follow-up version of NORMSDIST|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !NORM.S.INV | ||
+ | |follow-up version of NORMSINV|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !PERCENTILE.EXC | ||
+ | |new|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !PERCENTILE.INC | ||
+ | |follow-up version of Percentile|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !PERCENTRANK.EXC | ||
+ | |new|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !PERCENTRANK.INC | ||
+ | |follow-up version of PERCENTRANK|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !POISSON.DIST | ||
+ | |follow-up version of POISSON|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !QUARTILE.EXC | ||
+ | |new|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !QUARTILE.INC | ||
+ | |follow-up version of QUARTILE|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !RANK.AVG | ||
+ | |new|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !RANK.EQ | ||
+ | |follow-up version of RANK|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !STDEV.P | ||
+ | |follow-up version of STDEVP|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !STDEV.S | ||
+ | |follow-up version of STDEV|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !T.DIST | ||
+ | |new|| open issue || — ||style="background:#FF8080"|impossible | ||
+ | |||
+ | |- | ||
+ | !T.DIST.2T | ||
+ | |follow-up version of TDIST|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !T.DIST.RT | ||
+ | |follow-up version of TDIST||open issue || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !T.INV | ||
+ | |new|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !T.INV.2T | ||
+ | |follow-up version of TINV|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !T.TEST | ||
+ | |follow-up version of TTEST|| || ||style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !VAR.P | ||
+ | |follow-up version of VARP|| || || style="background:#FFFFFF" | | ||
+ | |||
+ | |- | ||
+ | !VAR.S | ||
+ | |follow-up version of VAR|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !WEIBULL.DIST | ||
+ | |follow-up version of WEILBULLDIST|| || ||style="background:#FFFFFF"| | ||
+ | |||
+ | |- | ||
+ | !WORKDAY.INTL | ||
+ | |new||WORKDAY||WORKDAY||style="background:#FFE600" |partly | ||
+ | |||
+ | |- | ||
+ | !Z.TEST | ||
+ | |follow-up version of ZTEST|| || ||style="background:#FFFFFF" | | ||
+ | |} | ||
[[Category:Calc]] | [[Category:Calc]] | ||
[[Category:To-Do]] | [[Category:To-Do]] | ||
[[Category:Draft]] | [[Category:Draft]] |
Latest revision as of 15:13, 20 October 2010
This article is a workspace for discussing the treatment of new functions of Excel 2010 in OpenOffice.org code. For using functions in spreadsheets look at Documentation/How_Tos/Calc:_Functions_listed_alphabetically
Please do not change the layout to a table. Such table makes it hard to edit a single section.
You want to help collecting the information? Try to add: Which parameters has the function? Does a corresponding OOo function exists already? Does a corresponding function exists in OpenFormula? How difficult is it to implement a new function if necessary? Do you see any problems or obstacles?
Contents
- 1 Reference Documents
- 2 AGGREGATE
- 3 BETA.DIST
- 4 BETA.INV
- 5 BINOM.DIST
- 6 BINOM.INV
- 7 CEILING.PRECISE
- 8 CHISQ.DIST
- 9 CHISQ.DIST.RT
- 10 CHISQ.INV
- 11 CHISQ.INV.RT
- 12 CHISQ.TEST
- 13 CONFIDENCE.NORM
- 14 CONFIDENCE.T
- 15 COVARIANCE.P
- 16 COVARIANCE.S
- 17 ECMA.CEILING
- 18 EXPON.DIST
- 19 F.DIST
- 20 F.DIST.RT
- 21 F.INV
- 22 F.INV.RT
- 23 F.TEST
- 24 FLOOR.PRECISE
- 25 GAMMA.DIST
- 26 GAMMA.INV
- 27 HYPGEOM.DIST
- 28 ISO.CEILING
- 29 LOGNORM.DIST
- 30 LOGNORM.INV
- 31 MODE.MULT
- 32 MODE.SNGL
- 33 NEGBINOM.DIST
- 34 NETWORKDAYS.INTL
- 35 NORM.DIST
- 36 NORM.INV
- 37 NORM.S.DIST
- 38 NORM.S.INV
- 39 PERCENTILE.EXC
- 40 PERCENTILE.INC
- 41 PERCENTRANK.EXC
- 42 PERCENTRANK.INC
- 43 POISSON.DIST
- 44 QUARTILE.EXC
- 45 QUARTILE.INC
- 46 RANK.AVG
- 47 RANK.EQ
- 48 STDEV.P
- 49 STDEV.S
- 50 T.DIST
- 51 T.DIST.2T
- 52 T.DIST.RT
- 53 T.INV
- 54 T.INV.2T
- 55 T.TEST
- 56 VAR.P
- 57 VAR.S
- 58 WEIBULL.DIST
- 59 WORKDAY.INTL
- 60 Z.TEST
- 61 Synopsis as Table
Reference Documents
The discussion uses the description of the new functions in Function Improvements in Microsoft Office Excel 2010 and in What's New: Changes made to Excel functions and the connected descriptions of the functions. A further list can be found in chapter 2.2.3 of [MS-XLSX]: Excel Extensions to the Office Open XML SpreadsheetML File Format (.xlsx) Specification [1].
For OpenFormula the actual document from OpenDocument - Formula SC Public Documents is used.
File:Compare Function Category Excel ODF OOo.ods Table of functions with their categories collected from Excel 2010 Beta, ODFF draft 28 and OOo3.2.
AGGREGATE
new
- Excel
- AGGREGATE(function_num, options, ref1, [ref2], …) or AGGREGATE(function_num, options, array, [k])
- OpenFormula
- no such function specified
- OOo3.2
- no similar function implemented
Comment
Mapping is not possible, because it is not specified in OpenFormula. The function AGGREGATE extends the function SUBTOTAL with further functions and more options to decide, which rows are ignored. A mapping to SUBTOTAL would be only possible in very few cases, when the option is given as constant 0 or 5. For to map option 5 to SUBTOTAL it is needed that Issue 35718 is fixed.
BETA.DIST
follow-up version of BETADIST
- Excel
- BETA.DIST(x,alpha,beta,cumulative,[A],[B]), default A=0 and B=1
- cumulative is boolean, but numbers are accepted too
- The argument A may be empty, using two semicolons.
- A <= x <= B
- OpenFormula
- BETADIST( Number x ; Number alpha ; Number beta [ ; Number a = 0 [ ; Number b = 1 [ ; Logical Cumulative = TRUE() ] ] ] )
- alpha > 0, beta > 0, a < b
- no constraints for x besides the cases with pole.
- OOo3.2
- BETADIST(number; alpha; beta [; start [; end [; cumulative]]])
- implemented as in OpenFormula defined
Comment
Mapping is possible for import. Notice the different order of arguments. Missing A or B have to be set to their default, if cumulative is given.
BETA.INV
follow-up version of BETAINV
- Excel
- BETA.INV(probability,alpha,beta,[A],[B]), default A=0 and B=1
- The argument A may be empty, using two semicolons.
- OpenFormula
- BETAINV( Number p ; Number a ; Number b [ ; Number a = 0 [ ; Number b = 1 ] ] )
- OOo3.2
- BETAINV(number; alpha; beta; Start; End)
- implemented as defined in OpenFormula, but empty Start is possible.
Comment
Directly mapping is possible.
BINOM.DIST
follow-up version of BINOMDIST
- Excel
- BINOM.DIST(number_s,trials,probability_s,cumulative)
- number_s and trials are truncated to integer
- cumulative is boolean, but numbers are accepted too
- OpenFormula
- BINOMDIST( Integer S ; Integer N ; Number P ; Logical Cumulative )
- OOo3.2
- =BINOMDIST(X; trials; SP; C)
- X and trials are truncated to integer
Comment
direct mapping is possible
BINOM.INV
follow-up version of CRITBINOM
- Excel
- BINOM.INV(trials,probability_s,alpha), all arguments required
- trials are truncated to integer
- OpenFormula
- CRITBINOM( Number Trials ; Number SP ; Number Alpha )
- OOo3.2
- CRITBINOM(trials; SP; alpha)
Comment
Direct mapping is possible. Notice that the domain for 'trails' is much larger in Excel 2010 than in OOo3.2. For example =BINOM.INV(500000;0.5;0.1) is possible in Excel 2010 but =CRITBINOM(1080;0.5;0.1) fails aready in OOo3.2. A new algorithm is needed.
CEILING.PRECISE
new
- Excel
- CEILING.PRECISE(number, [significance])
- significance has default value 1, always the absolute value of significance is used
- OpenFormula
- CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] )
- nested rules, round toward positive infinity for mode omitted or zero, round away from zero in the other cases.
- N and significance must have the same sign if not 0.
- OOo3.2
- CEILING(Number; Significance [; Mode])
- Significance is required, Issue 102957 is still open, no decision about the patch there up to now.
Comment
Because OpenFormula requires, that N and significance have the same sign, a direct mapping is not possible.
CEILING.PRECISE(number) —with second argument missing— can be mapped to CEILING(number). To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error.
With a given second argument, the composed formula CEILING(number; SIGN(number)*ABS(significance)) would give the same result. But the question is, whether such change should be done at all.
This function can be used with the name ISO.CEILING in Excel 2010 Beta too. It seems that CEILING.PRECISE is the favorite name, because ISO.CEILING works in EXCEL 2010 Beta, but is not listed in the available functions there. In addition an ISO.FLOOR does not exist, but only a FLOOR.PRECISE.
CHISQ.DIST
new
- Excel
- CHISQ.DIST(x,deg_freedom,cumulative), all arguments required
- x >= 0
- deg_freedom is truncated to integer, 0<deg_freedom<=10^10
- cumulative is logical, but numbers are accepted too
- OpenFormula
- CHISQDIST( Number x ; Number DegreesOfFreedom [ ; Logical Cumulative = TRUE() ] )
- OOo3.2
- is already implemented as CHISQDIST as OpenFormula defines
- no constraints for x
- DegreesOfFreedom are truncated as in Excel, DegreesOfFreedom > 0, no fixed upper bound for DegreesOfFreedom
Comment
Directly mapping is possible in import.
CHISQ.DIST.RT
follow-up version of CHIDIST
- Excel
- CHISQ.DIST.RT(x,deg_freedom)
- 1 < deg_freedom < 10^10, deg_freedom is truncated to integer
- OpenFormula
- LEGACY.CHIDIST( Number x ; Number DegreesOfFreedom )
- DegreesOfFreedom is positive interger
- OOo3.2
- CHIDIST(Number; degrees_freedom)
- no constraints, degrees_freedom is truncated to integer
Comment
Direct mapping is possible.
CHISQ.INV
new
- Excel
- CHISQ.INV(probability,deg_freedom), both arguments required
- 0<=probability<1,
- 1<=deg_freedom<=10^10, deg_freedom is truncated to integer
- OpenFormula
- CHISQINV( Number p ; Number DegreesOfFreedom )
- 0<=p<1
- DegreesOfFreedom is a positive integer
- OOo3.2
- is already implemented as CHISQINV as OpenFormula defines
- 0<=p<1
- DegreesOfFreedom is truncated as in Excel, DegreesOfFreedom > 0, no fixed upper bound for DegreesOfFreedom, but calculation might not converge
Comment
Directly mapping is possible in import.
CHISQ.INV.RT
follow-up version of CHIINV
- Excel
- CHISQ.INV.RT(probability,deg_freedom)
- The iterative search technique is limited to 64 iterations
- deg_freedom is truncated to integer
- OpenFormula
- LEGACY.CHIINV( Number p ; Number DegreesOfFreedom )
- OOo3.2
- CHININV(number; degrees_freedom)
- degrees_freedom is truncated to integer
Comment
direct mapping is possibel
CHISQ.TEST
follow-up version of CHITEST
- Excel
- CHISQ.TEST(actual_range,expected_range)
- OpenFormula
- LEGACY.CHITEST( ForceArray Array A ; ForceArray Array E )
- The function was introduced in OpenFormula to be compatible with Excel and OpenOffice.org.
- OOo3.2
- CHITEST(Date_B; data:E)
Comment
direct mapping possible.
CONFIDENCE.NORM
follow-up version of CONFIDENCE
- Excel
- CONFIDENCE.NORM(alpha,standard_dev,size)
- size is truncated to integer
- OpenFormula
- CONFIDENCE( Number alpha ; Number stddev ; Number size )
- is same as NORMINV(1 - alpha / 2; 0; 1) * stddev / SQRT (size)
- OOo3.2
- CONFIDENCE(alpha; STDEV; size)
- size is truncated to integer
- implemented as defined in OpenFormula
Comment
Direct mapping is possible.
CONFIDENCE.T
new
- Excel
- CONFIDENCE.T(alpha,standard_dev,size)
- calculates
- OpenFormula
- no suitable function defined
- OOo3.2
- no suitable function implemented
Comment
Direct mapping is impossible, because no suitable function exists in OpenFormula.
For implementation is possible, where TINV is the already implemented function in OOo.
COVARIANCE.P
follow-up version of COVAR
- Excel
- COVARIANCE.P(array1,array2)
- a pair with one text, logical or empty is ignored
- OpenFormula
- COVAR( ForceArray Array n1 ; ForceArray Array n2 )
- OOo3.2
- COVAR(Data_1; Data_2)
Comment
Direct mapping possible.
COVARIANCE.S
new
- Excel
- COVARIANCE.S(array1,array2)
- a pair with one text, logical or empty is ignored
- OpenFormula
- no such function defined
- OOo3.2
- not implemented
Comment
Direct mapping impossible, because a suitable function not contained in OpenFormula.
Implementation would be easy, just copy code from COVAR and use different denominator.
ECMA.CEILING
new
- Excel
- ECMA.CEILING(number, significance), all arguments are required
Comment
This function is mentioned in chapter 2.2.3 of [MS-XLSX]: Excel Extensions to the Office Open XML SpreadsheetML File Format (.xlsx) Specification [2], but it is not listed in the help inside Excel 2010 Beta.
It can be handled as CEILING.PRECISE.
EXPON.DIST
follow-up version of EXPONDIST
F.DIST
new
- Excel
- F.DIST(x,deg_freedom1,deg_freedom2,cumulative), all arguments are required
- x>=0
- deg_freedom1,deg_freedom2 are truncated to integer, both >=1
- cumulative is boolean, but numbers are accepted too
- OpenFormula
- FDIST( Number x ; Number r1 ; Number r2 [ ; Logical Cumulative = TRUE() ] )
- no constraint for x
- r1 and r2 are positive integers
- OOo3.2
- not implemented
Comment
This left tailed version has the same name as the right tailed version of OOo2. Because the version OOo2 cannot interpret namespaces, a simple solution is not possible.
For the implementation I have already written a patch. It would only need to be adapted to the actual code. CDF is implemented with a call of ScInterpreter::GetBetaDist, density function is calculated directly or via ln, if directly calculating not possible.
Please discuss solutions on the discussion page.
F.DIST.RT
follow-up version of FDIST
F.INV
new
F.INV.RT
follow-up version of FINV
F.TEST
follow-up version of FTEST
FLOOR.PRECISE
new
- Excel
- FLOOR.PRECISE(number, [significance]), significance has default 1
- OpenFormula
- FLOOR( Number N [ ; [ Number significance ] [ ; Number mode ] ] )
- nested rules, round toward negative infinity for mode omitted or zero, round toward zero in the other cases.
- N and significance must have the same sign if not 0
- OOo3.2
- FLOOR(number; significance [; mode])
- Significance is required, Issue 102957 is still open, no decision about the patch there up to now.
Comment
Because OpenFormula requires, that N and significance have the same sign, a direct mapping is not possible.
FLOOR.PRECISE(number) —with second argument missing— can be mapped to FLOOR(number). To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error.
With a given second argument, the composed formula FLOOR(number; SIGN(number)*ABS(significance)) would give the same result. But the question is, whether such change should be done at all.
see also CEILING.PRECISE
GAMMA.DIST
follow-up version of GAMMADIST
GAMMA.INV
follow-up version of GAMMAINV
HYPGEOM.DIST
follow-up version of HYPGEOMDIST
ISO.CEILING
new
- Excel
- ISO.CEILING(number, [significance])
- significance has default value 1, always the absolute value of significance is used
- OpenFormula
- CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] )
- nested rules, round toward positive infinity for mode omitted or zero, round away from zero in the other cases.
- N and significance must have the same sign if not 0.
- OOo3.2
- CEILING(Number; Significance [; Mode])
- Significance is required, Issue 102957 is still open, no decision about the patch there up to now.
Comment
Because OpenFormula requires, that N and significance have the same sign, a direct mapping is not possible.
ISO.CEILING(number) —with second argument missing— can be mapped to CEILING(number). To this a corrected version in OOo is needed, so that a missing second argument no longer results in an error.
With a given second argument, the composed formula CEILING(number; SIGN(number)*ABS(significance)) would give the same result. But the question is, whether such change should be done at all.
This function can be used with the name CEILING.PRECISE in Excel 2010 Beta too. It seems that CEILING.PRECISE is the favorite name, because ISO.CEILING works in EXCEL 2010 Beta, but is not listed in the available functions there. In addition an ISO.FLOOR does not exist, but only a FLOOR.PRECISE.
LOGNORM.DIST
follow-up version of LOGNORMDIST
LOGNORM.INV
follow-up version of LOGINV
MODE.MULT
new
- Excel
- MODE.MULT((number1,[number2],...])
- array function, the target cells have to be selected.
- The help does not mention, in which order the values are returned. Tests show, that values start with smallest cell position in order row, then column.
- OpenFormula
- no suitable function defined
- OOo3.2
- no suitable function implemented
Comment
A suitable function is neither defined in OpenFormula nor implemented in OOo3.2. So import is impossible.
MODE.SNGL
follow-up version of MODE
- Excel
- MODE.SNGL(number1,[number2],...])
- The help does not mention, which value is returned, if there are more than one value with the same largest frequency. Tests show, that it is the values with smallest cell position in order row, then column.
- up to 254 arguments
- OpenFormula
- MODE( { ForceArray NumberSequence N }+ )
- If there are more than one value with the same largest frequency, the smallest one is returned.
- OOo3.2
- MODE(Number1; Number2; ...Number30), each argument can be a number or a range, only the first argument is requiered.
Comment
In simple cases the function returns the same result in Excel as in OOo. But in case of more than one value with the same largest frequency the result might differ.
The function can only be mapped on import, if there are not more than 30 arguments in Excel.
This problems are not new for MODE.SNGL, but exist already for the old MODE version.
NEGBINOM.DIST
follow-up version of NEGBINOMDIST
NETWORKDAYS.INTL
new
- Excel
- NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
- The argument weekend can be given as number 1..17 or as string containing seven 0 or 1, for example 1100000.
- OpenFormula
- NETWORKDAYS( DateParam Date1 ; DateParam Date2 [ ; [ DateSequence holidays ] [ ; LogicalSequence workdays ] ] )
- OOo3.2
- NETWORKDAYS((Start_date; End_date [; Holidays])
Comment
The existing function NETWORKDAYS(Start_date; End_date; Holidays) has to be expanded to accept a forth argument, see Issue 71059 . In addition it needs the capability to use empty arguments indicated by ;; to skip the argument holidays.
The syntax for the argument holidays is the same in all three versions. The argument workdays has the type LogicalSequence in OpenFormula, that is a term of kind {1,1,0,0,0,0,0}, but has the type string in Excel. The first position is attributed to Sunday, but in Excel the first position is attributed to Monday. If the argument weekend is given as constant, mapping is possible, but need some care in converting the argument value. If the argument weekend is given as reference, a direct mapping is not possible.
see also WORKDAY.INTL
NORM.DIST
follow-up version of NORMDIST
NORM.INV
follow-up version of NORMINV
NORM.S.DIST
follow-up version of NORMSDIST
NORM.S.INV
follow-up version of NORMSINV
PERCENTILE.EXC
new
PERCENTILE.INC
follow-up version of PERCENTILE
PERCENTRANK.EXC
new
PERCENTRANK.INC
follow-up version of PERCENTRANK
POISSON.DIST
follow-up version of POISSON
QUARTILE.EXC
new
QUARTILE.INC
follow-up version of QUARTILE
RANK.AVG
new
RANK.EQ
follow-up version of RANK
STDEV.P
follow-up version of STDEVP
STDEV.S
follow-up version of STDEV
T.DIST
new
- Excel
- T.DIST(x,deg_freedom, cumulative), all required
- deg_freedom is truncated to integer
- cumulative is boolean, but numbers are accepted
- OpenFormula
- Currently only a right tail version is defined. But that will likely change till specification is final.
- OOo
- not implemented
Comment
An implementation of a left tail type and of a density function does not exist yet. The CDF is easily implemented via a call to ScInterpreter::GetBetaDist. The term of the density function does not look difficult and might be calculated directly or with ln in case of intermediate over/underflow.
Because the OpenFormula definition might change from the current right tail version to left tail, the implementation should be postponed.
T.DIST.2T
follow-up version of TDIST
T.DIST.RT
follow-up version of TDIST
T.INV
new
T.INV.2T
follow-up version of TINV
T.TEST
follow-up version of TTEST
VAR.P
follow-up version of VARP
VAR.S
follow-up version of VAR
WEIBULL.DIST
follow-up version of WEIBULL
WORKDAY.INTL
new
- Excel
- WORKDAY.INTL(start_date, days, [weekend], [holidays])
- The argument weekend can be given as number 1..17 or as string containing seven 0 or 1, for example 1100000.
- OpenFormula
- WORKDAY( DateParam Date ; Number Offset [ ; [ DateSequence Holidays ] [ ; LogicalSequence Workdays ] ] )
- OOo
- WORKDAY(Start_date; Days [;Holydays])
Comment
The existing function WORKDAY(Start_date; End_date; Holidays) has to be expanded to accept a forth argument, see Issue 71059 . In addition it needs the capability to use empty arguments indicated by ;; to skip the argument holidays.
The syntax for the argument holidays is the same in all three versions. The argument workdays has the type LogicalSequence in OpenFormula, that is a term of kind {1,1,0,0,0,0,0}, but has the type string in Excel. The first position is attributed to Sunday, but in Excel the first position is attributed to Monday. If the argument weekend is given as constant, mapping is possible, but need some care in converting the argument value. If the argument weekend is given as reference, a direct mapping is not possible.
see also NETWORKDAYS.INTL
Z.TEST
follow-up version of ZTEST
Synopsis as Table
Excel 2010 | Kind | OpenFormula | Calc | Conversion |
---|---|---|---|---|
AGGREGATE | new | — | — | impossible |
BETA.DIST | follow-up version of BETADIST | BETADIST | BETADIST | possible |
BETA.INV | follow-up version of BETAINV | BETAINV | BETAINV | direct |
BINOM.DIST | follow-up version of BINOMDIST | BINOMDIST | BINOMDIST | direct |
BINOM.INV | follow-up version of CRITBINOM | CRITBINOM | CRITBINOM | possible |
CEILING.PRECISE | new | CEILING | CEILING | partly |
CHISQ.DIST | new | CHISQDIST | CHISQDIST | direct |
CHISQ.DIST.RT | follow-up version of CHIDIST | LEGACY.CHIDIST | CHIDIST | direct |
CHISQ.INV | new | CHISQINV | CHISQINV | direct |
CHISQ.INV.RT | follow-up version of CHIINV | LEGACY.CHIINV | CHIINV | direct |
CHISQ.TEST | follow-up version of CHITEST | LEGACY.CHITEST | CHITEST | direct |
CONFIDENCE.NORM | follow-up version of CONFIDENCE | CONFIDENCE | CONFIDENCE | direct |
CONFIDENCE.T | new | — | — | impossible |
COVARIANCE.P | follow-up version of COVAR | COVAR | COVAR | direct |
COVARIANCE.S | new | — | — | impossible |
ECMA.CEILING | new | CEILING | CEILING | partly |
EXPON.DIST | follow-up version of EXPONDIST | |||
F.DIST | new | FDIST | — | impossible |
F.DIST.RT | follow-up version of FDIST | |||
F.INV | new | |||
F.INV.RT | follow-up version of FINV | |||
F.TEST | follow-up version of FTEST | |||
FLOOR.PRECISE | new | FLOOR | FLOOR | partly |
GAMMA.DIST | follow-up version of GAMMADIST | |||
GAMMA.INV | follow-up version of GAMMAINV | |||
HYPGEOM.DIST | follow-up version of HYPGEOMDIST | |||
ISO.CEILING | new | CEILING | CEILING | partly |
LOGNORM.DIST | follow-up version of LOGNORMDIST | |||
LOGNORM.INV | follow-up version of LOGINV | |||
MODE.MULT | new | — | — | impossible |
MODE.SNGL | follow-up version of MODE | MODE | MODE | possible |
NEGBINOM.DIST | follow-up version of NEGBINOMDIST | |||
NETWORKDAYS.INTL | new | NETWORKDAYS | NETWORKDAYS | partly |
NORM.DIST | follow-up version of NORMDIST | |||
NORM.INV | follow-up version of NORMINV | |||
NORM.S.DIST | follow-up version of NORMSDIST | |||
NORM.S.INV | follow-up version of NORMSINV | |||
PERCENTILE.EXC | new | |||
PERCENTILE.INC | follow-up version of Percentile | |||
PERCENTRANK.EXC | new | |||
PERCENTRANK.INC | follow-up version of PERCENTRANK | |||
POISSON.DIST | follow-up version of POISSON | |||
QUARTILE.EXC | new | |||
QUARTILE.INC | follow-up version of QUARTILE | |||
RANK.AVG | new | |||
RANK.EQ | follow-up version of RANK | |||
STDEV.P | follow-up version of STDEVP | |||
STDEV.S | follow-up version of STDEV | |||
T.DIST | new | open issue | — | impossible |
T.DIST.2T | follow-up version of TDIST | |||
T.DIST.RT | follow-up version of TDIST | open issue | ||
T.INV | new | |||
T.INV.2T | follow-up version of TINV | |||
T.TEST | follow-up version of TTEST | |||
VAR.P | follow-up version of VARP | |||
VAR.S | follow-up version of VAR | |||
WEIBULL.DIST | follow-up version of WEILBULLDIST | |||
WORKDAY.INTL | new | WORKDAY | WORKDAY | partly |
Z.TEST | follow-up version of ZTEST |