Difference between revisions of "Calc/Drafts/Treatment of new Excel 2010 functions"
(→CEILING.PRECISE) |
(→ISO.CEILING) |
||
| Line 149: | Line 149: | ||
;ODF1.2: CEILING( Number N [ ; [ Number significance ] [ ; Number mode ] ] ) | ;ODF1.2: 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 | + | : N and significance must have the same sign if not 0. |
;OOo3.2: CEILING(Number; Significance [; Mode]) | ;OOo3.2: CEILING(Number; Significance [; Mode]) | ||
: 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. | ||
| Line 159: | Line 159: | ||
With a given second argument, the composed formula <tt>CEILING(number; SIGN(number)*ABS(significance))</tt> would give the same result. But the question is, whether such change should be done at all. | With a given second argument, the composed formula <tt>CEILING(number; SIGN(number)*ABS(significance))</tt> would give the same result. But the question is, whether such change should be done at all. | ||
| − | 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. | + | 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 == | == LOGNORM.DIST == | ||
Revision as of 21:33, 8 April 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 chance 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 ODF 1.2? 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 EXPON.DIST
- 18 F.DIST
- 19 F.DIST.RT
- 20 F.INV
- 21 F.INV.RT
- 22 F.TEST
- 23 FLOOR.PRECISE
- 24 GAMMA.DIST
- 25 GAMMA.INV
- 26 HYPGEOM.DIST
- 27 ISO.CEILING
- 28 LOGNORM.DIST
- 29 LOGNORM.INV
- 30 MODE.MULT
- 31 MODE.SNGL
- 32 NEGBINOM.DIST
- 33 NETWORKDAYS.INTL
- 34 NORM.DIST
- 35 NORM.INV
- 36 NORM.S.DIST
- 37 NORM.S.INV
- 38 PERCENTILE.EXC
- 39 PERCENTILE.INC
- 40 PERCENTRANK.EXC
- 41 PERCENTRANK.INC
- 42 POISSON.DIST
- 43 QUARTILE.EXC
- 44 QUARTILE.INC
- 45 RANK.AVG
- 46 RANK.EQ
- 47 STDEV.P
- 48 STDEV.S
- 49 T.DIST
- 50 T.DIST.2T
- 51 T.DIST.RT
- 52 T.INV
- 53 T.INV.2T
- 54 T.TEST
- 55 VAR.P
- 56 VAR.S
- 57 WEIBULL.DIST
- 58 WORKDAY.INTL
- 59 Z.TEST
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.
For ODF 1.2 the actual document from OpenDocument - Formula SC Public Documents is used.
AGGREGATE
new
BETA.DIST
follow-up version of BETADIST
BETA.INV
follow-up version of BETAINV
BINOM.DIST
follow-up version of BINOMDIST
BINOM.INV
follow-up version of CRITBINOM
CEILING.PRECISE
new
- Excel
- CEILING.PRECISE(number, [significance])
- significance has default value 1, always the absolute value of significance is used
- ODF1.2
- 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 ODF 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
- ODF1.2
- CHISQDIST( Number x ; Number DegreesOfFreedom [ ; Logical Cumulative = TRUE() ] )
- OOo3.2
- is already implemented as CHISQDIST as ODF1.2 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
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
- ODF1.2
- CHISQINV( Number p ; Number DegreesOfFreedom )
- 0<=p<1
- DegreesOfFreedom is a positive integer
- OOo3.2
- is already implemented as CHISQINV as ODF1.2 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
CHISQ.TEST
follow-up version of CHITEST
CONFIDENCE.NORM
follow-up version of CONFIDENCE
CONFIDENCE.T
new
COVARIANCE.P
follow-up version of COVAR
COVARIANCE.S
new
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
- ODF1.2
- 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.
The implementation would be no problem, just a call of ScInterpreter::GetBetaDist.
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])
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
- ODF1.2
- 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 ODF 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
MODE.SNGL
follow-up version of MODE
NEGBINOM.DIST
follow-up version of NEGBINOMDIST
NETWORKDAYS.INTL
new
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
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
Z.TEST
follow-up version of ZTEST
