# Calc/Drafts/Treatment of new Excel 2010 functions

< Calc‎ | Drafts

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?

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

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

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.

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

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

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

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.

## Z.TEST

follow-up version of ZTEST

## Synopsis as Table

Excel 2010 Kind OpenFormula Calc Conversion
AGGREGATE new impossible
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.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
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