# Calc/Drafts/Treatment of new Excel 2010 functions

< Calc‎ | Drafts

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.

new

## 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
ODF1.2
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 ODF1.2 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.
ODF1.2
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 ODF1.2, but empty Start is possible.

### Comment

Directly mapping is possible.

## 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

new

## COVARIANCE.P

follow-up version of COVAR

Excel
COVARIANCE.P(array1,array2)
a pair with one text, logical or empty is ignored ODF1.2
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 ODF1.2
no such function defined
OOo3.2
not implemented

### Comment

Direct mapping impossible, because a suitable function not contained in ODF1.2.

Implementation would be easy, just copy code from COVAR and use different denominator.

## 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.

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

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

new

## MODE.SNGL

follow-up version of MODE

## NEGBINOM.DIST

follow-up version of NEGBINOMDIST

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

new

## PERCENTILE.INC

follow-up version of PERCENTILE

new

## PERCENTRANK.INC

follow-up version of PERCENTRANK

## POISSON.DIST

follow-up version of POISSON

new

## QUARTILE.INC

follow-up version of QUARTILE

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
ODF1.2
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 CPF 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 ODF1.2 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

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

new

## Z.TEST

follow-up version of ZTEST