Difference between revisions of "Calc/Drafts/Treatment of new Excel 2010 functions"

Revision as of 21:00, 7 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?

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

renamed BETADIST

BETA.INV

renamed BETAINV

BINOM.DIST

renamed BINOMDIST

BINOM.INV

renamed CRITBINOM

CEILING.PRECISE

new

Excel

CEILING.PRECISE(number, [significance])

COMMENT

It seems to be the same function as ISO.CEILING. Both are available in Excel 2010 Beta.

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

renamed 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

renamed CHIINV

CHISQ.TEST

renamed CHITEST

CONFIDENCE.NORM

renamed CONFIDENCE

CONFIDENCE.T

new

COVARIANCE.P

renamed COVAR

COVARIANCE.S

new

EXPON.DIST

renamed 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

renamed FDIST

F.INV

new

F.INV.RT

renamed FINV

F.TEST

renamed FTEST

FLOOR.PRECISE

new

Excel

FLOOR.PRECISE(number, [significance])

GAMMA.DIST

renamed GAMMADIST

GAMMA.INV

renamed GAMMAINV

HYPGEOM.DIST

renamed 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 has to 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.

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

renamed LOGNORMDIST

LOGNORM.INV

renamed LOGINV

MODE.MULT

new

MODE.SNGL

renamed MODE

NEGBINOM.DIST

renamed NEGBINOMDIST

NETWORKDAYS.INTL

new

NORM.DIST

renamed NORMDIST

NORM.INV

renamed NORMINV

NORM.S.DIST

renamed NORMSDIST

NORM.S.INV

renamed NORMSINV

PERCENTILE.EXC

new

PERCENTILE.INC

renamed PERCENTILE

PERCENTRANK.EXC

new

PERCENTRANK.INC

renamed PERCENTRANK

POISSON.DIST

renamed POISSON

QUARTILE.EXC

new

QUARTILE.INC

renamed QUARTILE

RANK.AVG

new

RANK.EQ

renamed RANK

STDEV.P

renamed STDEVP

STDEV.S

renamed STDEV

T.DIST

new

T.DIST.2T

renamed TDIST

T.DIST.RT

renamed TDIST

T.INV

new

T.INV.2T

renamed TINV

T.TEST

renamed TTEST

VAR.P

renamed VARP

VAR.S

renamed VAR

WEIBULL.DIST

renamed WEIBULL

WORKDAY.INTL

new

Z.TEST

renamed ZTEST