# Function Reference

This appendix provides a list of all the functions which are currently defined in Gnumeric.

## Bitwise Operations

BITAND

### `BITAND`

bitwise and

    BITAND(a,b)

#### Arguments

`a`: non-negative integer

`b`: non-negative integer

#### Description

`BITAND` returns the bitwise and of the binary representations of its arguments.

#### See also

`BITOR`, `BITXOR`.

BITLSHIFT

### `BITLSHIFT`

bit-shift to the left

    BITLSHIFT(a,n)

#### Arguments

`a`: non-negative integer

`n`: integer

#### Description

`BITLSHIFT` returns the binary representations of `a` shifted `n` positions to the left.

#### Note

If `n` is negative, `BITLSHIFT` shifts the bits to the right by ABS(`n`) positions.

#### See also

`BITRSHIFT`.

BITOR

### `BITOR`

bitwise or

    BITOR(a,b)

#### Arguments

`a`: non-negative integer

`b`: non-negative integer

#### Description

`BITOR` returns the bitwise or of the binary representations of its arguments.

#### See also

`BITXOR`, `BITAND`.

BITRSHIFT

### `BITRSHIFT`

bit-shift to the right

    BITRSHIFT(a,n)

#### Arguments

`a`: non-negative integer

`n`: integer

#### Description

`BITRSHIFT` returns the binary representations of `a` shifted `n` positions to the right.

#### Note

If `n` is negative, `BITRSHIFT` shifts the bits to the left by ABS(`n`) positions.

#### See also

`BITLSHIFT`.

BITXOR

### `BITXOR`

bitwise exclusive or

    BITXOR(a,b)

#### Arguments

`a`: non-negative integer

`b`: non-negative integer

#### Description

`BITXOR` returns the bitwise exclusive or of the binary representations of its arguments.

#### See also

`BITOR`, `BITAND`.

## Complex

COMPLEX

### `COMPLEX`

a complex number of the form `x` + `y` `i`

    COMPLEX(x,y,i)

#### Arguments

`x`: real part

`y`: imaginary part

`i`: the suffix for the complex number, either "i" or "j"; defaults to "i"

#### Note

If `i` is neither "i" nor "j", `COMPLEX` returns #VALUE!

#### Microsoft Excel Compatibility

This function is Excel compatible.

IMABS

### `IMABS`

the absolute value of the complex number `z`

    IMABS(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMAGINARY`, `IMREAL`.

IMAGINARY

### `IMAGINARY`

the imaginary part of the complex number `z`

    IMAGINARY(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMREAL`.

IMARCCOS

### `IMARCCOS`

the complex arccosine of the complex number

    IMARCCOS(z)

#### Arguments

`z`: a complex number

#### Description

`IMARCCOS` returns the complex arccosine of the complex number `z`. The branch cuts are on the real axis, less than -1 and greater than 1.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSIN`, `IMARCTAN`.

IMARCCOSH

### `IMARCCOSH`

the complex hyperbolic arccosine of the complex number `z`

    IMARCCOSH(z)

#### Arguments

`z`: a complex number

#### Description

`IMARCCOSH` returns the complex hyperbolic arccosine of the complex number `z`. The branch cut is on the real axis, less than 1.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSINH`, `IMARCTANH`.

IMARCCOT

### `IMARCCOT`

the complex arccotangent of the complex number `z`

    IMARCCOT(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSEC`, `IMARCCSC`.

IMARCCOTH

### `IMARCCOTH`

the complex hyperbolic arccotangent of the complex number `z`

    IMARCCOTH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSECH`, `IMARCCSCH`.

IMARCCSC

### `IMARCCSC`

the complex arccosecant of the complex number `z`

    IMARCCSC(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSEC`, `IMARCCOT`.

IMARCCSCH

### `IMARCCSCH`

the complex hyperbolic arccosecant of the complex number `z`

    IMARCCSCH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSECH`, `IMARCCOTH`.

IMARCSEC

### `IMARCSEC`

the complex arcsecant of the complex number `z`

    IMARCSEC(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCCSC`, `IMARCCOT`.

IMARCSECH

### `IMARCSECH`

the complex hyperbolic arcsecant of the complex number `z`

    IMARCSECH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCCSCH`, `IMARCCOTH`.

IMARCSIN

### `IMARCSIN`

the complex arcsine of the complex number `z`

    IMARCSIN(z)

#### Arguments

`z`: a complex number

#### Description

`IMARCSIN` returns the complex arcsine of the complex number `z`. The branch cuts are on the real axis, less than -1 and greater than 1.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCCOS`, `IMARCTAN`.

IMARCSINH

### `IMARCSINH`

the complex hyperbolic arcsine of the complex number `z`

    IMARCSINH(z)

#### Arguments

`z`: a complex number

#### Description

`IMARCSINH` returns the complex hyperbolic arcsine of the complex number `z`. The branch cuts are on the imaginary axis, below -i and above i.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCCOSH`, `IMARCTANH`.

IMARCTAN

### `IMARCTAN`

the complex arctangent of the complex number

    IMARCTAN(z)

#### Arguments

`z`: a complex number

#### Description

`IMARCTAN` returns the complex arctangent of the complex number `z`. The branch cuts are on the imaginary axis, below -i and above i.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSIN`, `IMARCCOS`.

IMARCTANH

### `IMARCTANH`

the complex hyperbolic arctangent of the complex number `z`

    IMARCTANH(z)

#### Arguments

`z`: a complex number

#### Description

`IMARCTANH` returns the complex hyperbolic arctangent of the complex number `z`. The branch cuts are on the real axis, less than -1 and greater than 1.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMARCSINH`, `IMARCCOSH`.

IMARGUMENT

### `IMARGUMENT`

the argument theta of the complex number `z`

    IMARGUMENT(z)

#### Arguments

`z`: a complex number

#### Description

The argument theta of a complex number is its angle in radians from the real axis.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

IMCONJUGATE

### `IMCONJUGATE`

the complex conjugate of the complex number `z`

    IMCONJUGATE(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMAGINARY`, `IMREAL`.

IMCOS

### `IMCOS`

the cosine of the complex number `z`

    IMCOS(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMSIN`, `IMTAN`.

IMCOSH

### `IMCOSH`

the hyperbolic cosine of the complex number `z`

    IMCOSH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMSINH`, `IMTANH`.

IMCOT

### `IMCOT`

the cotangent of the complex number `z`

    IMCOT(z)

#### Arguments

`z`: a complex number

#### Description

`IMCOT`(`z`) = IMCOS(`z`)/IMSIN(`z`).

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMSEC`, `IMCSC`.

IMCOTH

### `IMCOTH`

the hyperbolic cotangent of the complex number `z`

    IMCOTH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMSECH`, `IMCSCH`.

IMCSC

### `IMCSC`

the cosecant of the complex number `z`

    IMCSC(z)

#### Arguments

`z`: a complex number

#### Description

`IMCSC`(`z`) = 1/IMSIN(`z`).

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMSEC`, `IMCOT`.

IMCSCH

### `IMCSCH`

the hyperbolic cosecant of the complex number `z`

    IMCSCH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMSECH`, `IMCOTH`.

IMDIV

### `IMDIV`

the quotient of two complex numbers `z1`/`z2`

    IMDIV(z1,z2)

#### Arguments

`z1`: a complex number

`z2`: a complex number

#### Note

If `z1` or `z2` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMPRODUCT`.

IMEXP

### `IMEXP`

the exponential of the complex number `z`

    IMEXP(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMLN`.

IMINV

### `IMINV`

the reciprocal, or inverse, of the complex number `z`

    IMINV(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

IMLN

### `IMLN`

the natural logarithm of the complex number `z`

    IMLN(z)

#### Arguments

`z`: a complex number

#### Description

The result will have an imaginary part between -π and +π.

The natural logarithm is not uniquely defined on complex numbers. You may need to add or subtract an even multiple of π to the imaginary part.

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMEXP`, `IMLOG2`, `IMLOG10`.

IMLOG10

### `IMLOG10`

the base-10 logarithm of the complex number `z`

    IMLOG10(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMLN`, `IMLOG2`.

IMLOG2

### `IMLOG2`

the base-2 logarithm of the complex number `z`

    IMLOG2(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMLN`, `IMLOG10`.

IMNEG

### `IMNEG`

the negative of the complex number `z`

    IMNEG(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

IMPOWER

### `IMPOWER`

the complex number `z1` raised to the `z2`th power

    IMPOWER(z1,z2)

#### Arguments

`z1`: a complex number

`z2`: a complex number

#### Note

If `z1` or `z2` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMSQRT`.

IMPRODUCT

### `IMPRODUCT`

the product of the given complex numbers

    IMPRODUCT(z1,z2,…)

#### Arguments

`z1`: a complex number

`z2`: a complex number

#### Note

If any of `z1`, `z2`,... is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMDIV`.

IMREAL

### `IMREAL`

the real part of the complex number `z`

    IMREAL(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMAGINARY`.

IMSEC

### `IMSEC`

the secant of the complex number `z`

    IMSEC(z)

#### Arguments

`z`: a complex number

#### Description

`IMSEC`(`z`) = 1/IMCOS(`z`).

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMCSC`, `IMCOT`.

IMSECH

### `IMSECH`

the hyperbolic secant of the complex number `z`

    IMSECH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMCSCH`, `IMCOTH`.

IMSIN

### `IMSIN`

the sine of the complex number `z`

    IMSIN(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMCOS`, `IMTAN`.

IMSINH

### `IMSINH`

the hyperbolic sine of the complex number `z`

    IMSINH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMCOSH`, `IMTANH`.

IMSQRT

### `IMSQRT`

the square root of the complex number `z`

    IMSQRT(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMPOWER`.

IMSUB

### `IMSUB`

the difference of two complex numbers

    IMSUB(z1,z2)

#### Arguments

`z1`: a complex number

`z2`: a complex number

#### Note

If `z1` or `z2` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMSUM`.

IMSUM

### `IMSUM`

the sum of the given complex numbers

    IMSUM(z1,z2,…)

#### Arguments

`z1`: a complex number

`z2`: a complex number

#### Note

If any of `z1`, `z2`,... is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMSUB`.

IMTAN

### `IMTAN`

the tangent of the complex number `z`

    IMTAN(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`IMSIN`, `IMCOS`.

IMTANH

### `IMTANH`

the hyperbolic tangent of the complex number `z`

    IMTANH(z)

#### Arguments

`z`: a complex number

#### Note

If `z` is not a valid complex number, #VALUE! is returned.

#### See also

`IMSINH`, `IMCOSH`.

## Database

DAVERAGE

### `DAVERAGE`

average of the values in `field` in `database` belonging to records that match `criteria`

    DAVERAGE(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DCOUNT`.

DCOUNT

### `DCOUNT`

count of numbers in `field` in `database` belonging to records that match `criteria`

    DCOUNT(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DAVERAGE`, `DCOUNTA`.

DCOUNTA

### `DCOUNTA`

count of cells with data in `field` in `database` belonging to records that match `criteria`

    DCOUNTA(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DCOUNT`.

DGET

### `DGET`

a value from `field` in `database` belonging to records that match `criteria`

    DGET(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### Note

If none of the records match the conditions, `DGET` returns #VALUE! If more than one record match the conditions, `DGET` returns #NUM!

#### See also

`DCOUNT`.

DMAX

### `DMAX`

largest number in `field` in `database` belonging to a record that match `criteria`

    DMAX(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DMIN`.

DMIN

### `DMIN`

smallest number in `field` in `database` belonging to a record that match `criteria`

    DMIN(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DCOUNT`.

DPRODUCT

### `DPRODUCT`

product of all values in `field` in `database` belonging to records that match `criteria`

    DPRODUCT(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DSUM`.

DSTDEV

### `DSTDEV`

sample standard deviation of the values in `field` in `database` belonging to records that match `criteria`

    DSTDEV(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DSTDEVP`.

DSTDEVP

### `DSTDEVP`

standard deviation of the population of values in `field` in `database` belonging to records that match `criteria`

    DSTDEVP(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DSTDEV`.

DSUM

### `DSUM`

sum of the values in `field` in `database` belonging to records that match `criteria`

    DSUM(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DPRODUCT`.

DVAR

### `DVAR`

sample variance of the values in `field` in `database` belonging to records that match `criteria`

    DVAR(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DVARP`.

DVARP

### `DVARP`

variance of the population of values in `field` in `database` belonging to records that match `criteria`

    DVARP(database,field,criteria)

#### Arguments

`database`: a range in which rows of related information are records and columns of data are fields

`field`: a string or integer specifying which field is to be used

`criteria`: a range containing conditions

#### Description

`database` is a range in which rows of related information are records and columns of data are fields. The first row of a database contains labels for each column.

`field` is a string or integer specifying which field is to be used. If `field` is an integer n then the nth column will be used. If `field` is a string, then the column with the matching label will be used.

`criteria` is a range containing conditions. The first row of a `criteria` should contain labels. Each label specifies to which field the conditions given in that column apply. Each cell below the label specifies a condition such as ">3" or "<9". An equality condition can be given by simply specifying a value, e. g. "3" or "Jody". For a record to be considered it must satisfy all conditions in at least one of the rows of `criteria`.

#### See also

`DVAR`.

GETPIVOTDATA

### `GETPIVOTDATA`

summary data from a pivot table

    GETPIVOTDATA(pivot_table,field_name)

#### Arguments

`pivot_table`: cell range containing the pivot table

`field_name`: name of the field for which the summary data is requested

#### Note

If the summary data is unavailable, `GETPIVOTDATA` returns #REF!

## Date/Time

ASCENSIONTHURSDAY

### `ASCENSIONTHURSDAY`

Ascension Thursday in the Gregorian calendar according to the Roman rite of the Christian Church

    ASCENSIONTHURSDAY(year)

#### Arguments

`year`: year between 1582 and 9956, defaults to the year of the next Ascension Thursday

#### Note

Two digit years are adjusted as elsewhere in Gnumeric. Dates before 1904 may also be prohibited.

#### See also

`EASTERSUNDAY`.

ASHWEDNESDAY

### `ASHWEDNESDAY`

Ash Wednesday in the Gregorian calendar according to the Roman rite of the Christian Church

    ASHWEDNESDAY(year)

#### Arguments

`year`: year between 1582 and 9956, defaults to the year of the next Ash Wednesday

#### Note

Two digit years are adjusted as elsewhere in Gnumeric. Dates before 1904 may also be prohibited.

#### See also

`EASTERSUNDAY`.

DATE

### `DATE`

create a date serial value

    DATE(year,month,day)

#### Arguments

`year`: year of date

`month`: month of year

`day`: day of month

#### Description

The `DATE` function creates date serial values. 1-Jan-1900 is serial value 1, 2-Jan-1900 is serial value 2, and so on. For compatibility reasons, a serial value is reserved for the non-existing date 29-Feb-1900.

#### Note

If `month` or `day` is less than 1 or too big, then the year and/or month will be adjusted. For spreadsheets created with the Mac version of Excel, serial 1 is 1-Jan-1904.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TODAY`, `YEAR`, `MONTH`, `DAY`.

DATE2HDATE

### `DATE2HDATE`

Hebrew date

    DATE2HDATE(date)

#### Arguments

`date`: Gregorian date, defaults to today

#### See also

`HDATE`, `DATE2HDATE_HEB`.

DATE2HDATE_HEB

### `DATE2HDATE_HEB`

Hebrew date in Hebrew

    DATE2HDATE_HEB(date)

#### Arguments

`date`: Gregorian date, defaults to today

#### See also

`DATE2HDATE`, `HDATE_HEB`.

DATE2JULIAN

### `DATE2JULIAN`

Julian day number for given Gregorian date

    DATE2JULIAN(date)

#### Arguments

`date`: Gregorian date, defaults to today

#### See also

`HDATE_JULIAN`.

DATE2UNIX

### `DATE2UNIX`

the Unix timestamp corresponding to a date `d`

    DATE2UNIX(d)

#### Arguments

`d`: date

#### Description

The `DATE2UNIX` function translates a date into a Unix timestamp. A Unix timestamp is the number of seconds since midnight (0:00) of January 1st, 1970 GMT.

#### See also

`UNIX2DATE`, `DATE`.

DATEDIF

### `DATEDIF`

difference between dates

    DATEDIF(start_date,end_date,interval)

#### Arguments

`start_date`: starting date serial value

`end_date`: ending date serial value

`interval`: counting unit

#### Description

`DATEDIF` returns the distance from `start_date` to `end_date` according to the unit specified by `interval`.

#### Note

If `interval` is "y", "m", or "d" then the distance is measured in complete years, months, or days respectively. If `interval` is "ym" or "yd" then the distance is measured in complete months or days, respectively, but excluding any difference in years. If `interval` is "md" then the distance is measured in complete days but excluding any difference in months.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DAYS360`.

DATEVALUE

### `DATEVALUE`

the date part of a date and time serial value

    DATEVALUE(serial)

#### Arguments

`serial`: date and time serial value

#### Description

`DATEVALUE` returns the date serial value part of a date and time serial value.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TIMEVALUE`, `DATE`.

DAY

### `DAY`

the day-of-month part of a date serial value

    DAY(date)

#### Arguments

`date`: date serial value

#### Description

The `DAY` function returns the day-of-month part of `date`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATE`, `YEAR`, `MONTH`.

DAYS

### `DAYS`

difference between dates in days

    DAYS(start_date,end_date)

#### Arguments

`start_date`: starting date serial value

`end_date`: ending date serial value

#### Description

`DAYS` returns the positive or negative number of days from `start_date` to `end_date`.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`DATEDIF`.

DAYS360

### `DAYS360`

days between dates

    DAYS360(start_date,end_date,method)

#### Arguments

`start_date`: starting date serial value

`end_date`: ending date serial value

`method`: counting method

#### Description

`DAYS360` returns the number of days from `start_date` to `end_date`.

#### Note

If `method` is 0, the default, the MS Excel (tm) US method will be used. This is a somewhat complicated industry standard method where the last day of February is considered to be the 30th day of the month, but only for `start_date`. If `method` is 1, the European method will be used. In this case, if the day of the month is 31 it will be considered as 30 If `method` is 2, a saner version of the US method is used in which both dates get the same February treatment.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATEDIF`.

EASTERSUNDAY

### `EASTERSUNDAY`

Easter Sunday in the Gregorian calendar according to the Roman rite of the Christian Church

    EASTERSUNDAY(year)

#### Arguments

`year`: year between 1582 and 9956, defaults to the year of the next Easter Sunday

#### Note

Two digit years are adjusted as elsewhere in Gnumeric. Dates before 1904 may also be prohibited.

#### OpenDocument Format (ODF) Compatibility

The 1-argument version of `EASTERSUNDAY` is compatible with OpenOffice for years after 1904. This function is not specified in ODF/OpenFormula.

#### See also

`ASHWEDNESDAY`.

EDATE

### `EDATE`

adjust a date by a number of months

    EDATE(date,months)

#### Arguments

`date`: date serial value

`months`: signed number of months

#### Description

`EDATE` returns `date` moved forward or backward the number of months specified by `months`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATE`.

EOMONTH

### `EOMONTH`

end of month

    EOMONTH(date,months)

#### Arguments

`date`: date serial value

`months`: signed number of months

#### Description

`EOMONTH` returns the date serial value of the end of the month specified by `date` adjusted forward or backward the number of months specified by `months`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`EDATE`.

GOODFRIDAY

### `GOODFRIDAY`

Good Friday in the Gregorian calendar according to the Roman rite of the Christian Church

    GOODFRIDAY(year)

#### Arguments

`year`: year between 1582 and 9956, defaults to the year of the next Good Friday

#### Note

Two digit years are adjusted as elsewhere in Gnumeric. Dates before 1904 may also be prohibited.

#### See also

`EASTERSUNDAY`.

HDATE

### `HDATE`

Hebrew date

    HDATE(year,month,day)

#### Arguments

`year`: Gregorian year of date, defaults to the current year

`month`: Gregorian month of year, defaults to the current month

`day`: Gregorian day of month, defaults to the current day

#### See also

`HDATE_HEB`, `DATE`.

HDATE_DAY

### `HDATE_DAY`

Hebrew day of Gregorian date

    HDATE_DAY(year,month,day)

#### Arguments

`year`: Gregorian year of date, defaults to the current year

`month`: Gregorian month of year, defaults to the current month

`day`: Gregorian day of month, defaults to the current day

#### See also

`HDATE_JULIAN`.

HDATE_HEB

### `HDATE_HEB`

Hebrew date in Hebrew

    HDATE_HEB(year,month,day)

#### Arguments

`year`: Gregorian year of date, defaults to the current year

`month`: Gregorian month of year, defaults to the current month

`day`: Gregorian day of month, defaults to the current day

#### See also

`HDATE`, `DATE`.

HDATE_JULIAN

### `HDATE_JULIAN`

Julian day number for given Gregorian date

    HDATE_JULIAN(year,month,day)

#### Arguments

`year`: Gregorian year of date, defaults to the current year

`month`: Gregorian month of year, defaults to the current month

`day`: Gregorian day of month, defaults to the current day

#### See also

`HDATE`.

HDATE_MONTH

### `HDATE_MONTH`

Hebrew month of Gregorian date

    HDATE_MONTH(year,month,day)

#### Arguments

`year`: Gregorian year of date, defaults to the current year

`month`: Gregorian month of year, defaults to the current month

`day`: Gregorian day of month, defaults to the current day

#### See also

`HDATE_JULIAN`.

HDATE_YEAR

### `HDATE_YEAR`

Hebrew year of Gregorian date

    HDATE_YEAR(year,month,day)

#### Arguments

`year`: Gregorian year of date, defaults to the current year

`month`: Gregorian month of year, defaults to the current month

`day`: Gregorian day of month, defaults to the current day

#### See also

`HDATE_JULIAN`.

HOUR

### `HOUR`

compute hour part of fractional day

    HOUR(time)

#### Arguments

`time`: time of day as fractional day

#### Description

The `HOUR` function computes the hour part of the fractional day given by `time`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TIME`, `MINUTE`, `SECOND`.

ISOWEEKNUM

### `ISOWEEKNUM`

ISO week number

    ISOWEEKNUM(date)

#### Arguments

`date`: date serial value

#### Description

`ISOWEEKNUM` calculates the week number according to the ISO 8601 standard. Weeks start on Mondays and week 1 contains the first Thursday of the year.

#### Note

January 1 of a year is sometimes in week 52 or 53 of the previous year. Similarly, December 31 is sometimes in week 1 of the following year.

#### See also

`ISOYEAR`, `WEEKNUM`.

ISOYEAR

### `ISOYEAR`

year corresponding to the ISO week number

    ISOYEAR(date)

#### Arguments

`date`: date serial value

#### Description

`ISOYEAR` calculates the year to go with week number according to the ISO 8601 standard.

#### Note

January 1 of a year is sometimes in week 52 or 53 of the previous year. Similarly, December 31 is sometimes in week 1 of the following year.

#### See also

`ISOWEEKNUM`, `YEAR`.

MINUTE

### `MINUTE`

compute minute part of fractional day

    MINUTE(time)

#### Arguments

`time`: time of day as fractional day

#### Description

The `MINUTE` function computes the minute part of the fractional day given by `time`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TIME`, `HOUR`, `SECOND`.

MONTH

### `MONTH`

the month part of a date serial value

    MONTH(date)

#### Arguments

`date`: date serial value

#### Description

The `MONTH` function returns the month part of `date`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATE`, `YEAR`, `DAY`.

NETWORKDAYS

### `NETWORKDAYS`

number of workdays in range

    NETWORKDAYS(start_date,end_date,holidays,weekend)

#### Arguments

`start_date`: starting date serial value

`end_date`: ending date serial value

`holidays`: array of holidays

`weekend`: array of 0s and 1s, indicating whether a weekday (S, M, T, W, T, F, S) is on the weekend, defaults to {1,0,0,0,0,0,1}

#### Description

`NETWORKDAYS` calculates the number of days from `start_date` to `end_date` skipping weekends and `holidays` in the process.

#### Note

If an entry of `weekend` is non-zero, the corresponding weekday is not a work day.

#### Microsoft Excel Compatibility

This function is Excel compatible if the last argument is omitted.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`WORKDAY`.

NOW

### `NOW`

the date and time serial value of the current time

    NOW()

#### Description

The `NOW` function returns the date and time serial value of the moment it is computed. Recomputing later will produce a different value.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATE`.

ODF.TIME

### `ODF.TIME`

create a time serial value

    ODF.TIME(hour,minute,second)

#### Arguments

`hour`: hour

`minute`: minute

`second`: second

#### Description

The `ODF.TIME` function computes the time given by `hour`, `minute`, and `second` as a fraction of a day.

#### Note

While the return value is automatically formatted to look like a time between 0:00 and 24:00, the underlying serial time value can be any number.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`TIME`, `HOUR`, `MINUTE`, `SECOND`.

PENTECOSTSUNDAY

### `PENTECOSTSUNDAY`

Pentecost Sunday in the Gregorian calendar according to the Roman rite of the Christian Church

    PENTECOSTSUNDAY(year)

#### Arguments

`year`: year between 1582 and 9956, defaults to the year of the next Pentecost Sunday

#### Note

Two digit years are adjusted as elsewhere in Gnumeric. Dates before 1904 may also be prohibited.

#### See also

`EASTERSUNDAY`.

SECOND

### `SECOND`

compute seconds part of fractional day

    SECOND(time)

#### Arguments

`time`: time of day as fractional day

#### Description

The `SECOND` function computes the seconds part of the fractional day given by `time`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TIME`, `HOUR`, `MINUTE`.

TIME

### `TIME`

create a time serial value

    TIME(hour,minute,second)

#### Arguments

`hour`: hour of the day

`minute`: minute within the hour

`second`: second within the minute

#### Description

The `TIME` function computes the fractional day after midnight at the time given by `hour`, `minute`, and `second`.

#### Note

While the return value is automatically formatted to look like a time between 0:00 and 24:00, the underlying serial time value is a number between 0 and 1. If any of `hour`, `minute`, and `second` is negative, #NUM! is returned

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ODF.TIME`, `HOUR`, `MINUTE`, `SECOND`.

TIMEVALUE

### `TIMEVALUE`

the time part of a date and time serial value

    TIMEVALUE(serial)

#### Arguments

`serial`: date and time serial value

#### Description

`TIMEVALUE` returns the time-of-day part of a date and time serial value.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATEVALUE`, `TIME`.

TODAY

### `TODAY`

the date serial value of today

    TODAY()

#### Description

The `TODAY` function returns the date serial value of the day it is computed. Recomputing on a later date will produce a different value.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATE`.

UNIX2DATE

### `UNIX2DATE`

date value corresponding to the Unix timestamp `t`

    UNIX2DATE(t)

#### Arguments

`t`: Unix time stamp

#### Description

The `UNIX2DATE` function translates Unix timestamps into the corresponding date. A Unix timestamp is the number of seconds since midnight (0:00) of January 1st, 1970 GMT.

#### See also

`DATE2UNIX`, `DATE`.

WEEKDAY

### `WEEKDAY`

day-of-week

    WEEKDAY(date,method)

#### Arguments

`date`: date serial value

`method`: numbering system, defaults to 1

#### Description

The `WEEKDAY` function returns the day-of-week of `date`. The value of `method` determines how days are numbered; it defaults to 1.

#### Note

If `method` is 1, then Sunday is 1, Monday is 2, etc. If `method` is 2, then Monday is 1, Tuesday is 2, etc. If `method` is 3, then Monday is 0, Tuesday is 1, etc.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATE`, `ISOWEEKNUM`.

WEEKNUM

### `WEEKNUM`

week number

    WEEKNUM(date,method)

#### Arguments

`date`: date serial value

`method`: numbering system, defaults to 1

#### Description

`WEEKNUM` calculates the week number according to `method` which defaults to 1.

#### Note

If `method` is 1, then weeks start on Sundays and January 1 is in week 1. If `method` is 2, then weeks start on Mondays and January 1 is in week 1. If `method` is 150, then the ISO 8601 numbering is used.

#### See also

`ISOWEEKNUM`.

WORKDAY

### `WORKDAY`

add working days

    WORKDAY(date,days,holidays,weekend)

#### Arguments

`date`: date serial value

`days`: number of days to add

`holidays`: array of holidays

`weekend`: array of 0s and 1s, indicating whether a weekday (S, M, T, W, T, F, S) is on the weekend, defaults to {1,0,0,0,0,0,1}

#### Description

`WORKDAY` adjusts `date` by `days` skipping over weekends and `holidays` in the process.

#### Note

`days` may be negative. If an entry of `weekend` is non-zero, the corresponding weekday is not a work day.

#### Microsoft Excel Compatibility

This function is Excel compatible if the last argument is omitted.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`NETWORKDAYS`.

YEAR

### `YEAR`

the year part of a date serial value

    YEAR(date)

#### Arguments

`date`: date serial value

#### Description

The `YEAR` function returns the year part of `date`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DATE`, `MONTH`, `DAY`.

YEARFRAC

### `YEARFRAC`

fractional number of years between dates

    YEARFRAC(start_date,end_date,basis)

#### Arguments

`start_date`: starting date serial value

`end_date`: ending date serial value

`basis`: calendar basis

#### Description

`YEARFRAC` calculates the number of days from `start_date` to `end_date` according to the calendar specified by `basis`, which defaults to 0, and expresses the result as a fractional number of years.

#### Note

If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`DATE`.

## Engineering

BASE

### `BASE`

string of digits representing the number `n` in base `b`

    BASE(n,b,length)

#### Arguments

`n`: integer

`b`: base (2 ≤ `b` ≤ 36)

`length`: minimum length of the resulting string

#### Description

`BASE` converts `n` to its string representation in base `b`. Leading zeroes will be added to reach the minimum length given by `length`.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`DECIMAL`.

BESSELI

### `BESSELI`

Modified Bessel function of the first kind of order `α` at `x`

    BESSELI(X,α)

#### Arguments

`X`: number

`α`: order (any non-negative number)

#### Note

If `x` or `α` are not numeric, #VALUE! is returned. If `α` < 0, #NUM! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible if only integer orders `α` are used.

#### See also

`BESSELJ`, `BESSELK`, `BESSELY`.

BESSELJ

### `BESSELJ`

Bessel function of the first kind of order `α` at `x`

    BESSELJ(X,α)

#### Arguments

`X`: number

`α`: order (any non-negative integer)

#### Note

If `x` or `α` are not numeric, #VALUE! is returned. If `α` < 0, #NUM! is returned. If `α` is not an integer, it is truncated.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BESSELI`, `BESSELK`, `BESSELY`.

BESSELK

### `BESSELK`

Modified Bessel function of the second kind of order `α` at `x`

    BESSELK(X,α)

#### Arguments

`X`: number

`α`: order (any non-negative number)

#### Note

If `x` or `α` are not numeric, #VALUE! is returned. If `α` < 0, #NUM! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible if only integer orders `α` are used.

#### See also

`BESSELI`, `BESSELJ`, `BESSELY`.

BESSELY

### `BESSELY`

Bessel function of the second kind of order `α` at `x`

    BESSELY(X,α)

#### Arguments

`X`: number

`α`: order (any non-negative integer)

#### Note

If `x` or `α` are not numeric, #VALUE! is returned. If `α` < 0, #NUM! is returned. If `α` is not an integer, it is truncated.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BESSELI`, `BESSELJ`, `BESSELK`.

BIN2DEC

### `BIN2DEC`

decimal representation of the binary number `x`

    BIN2DEC(x)

#### Arguments

`x`: a binary number, either as a string or as a number involving only the digits 0 and 1

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DEC2BIN`, `BIN2OCT`, `BIN2HEX`.

BIN2HEX

### `BIN2HEX`

hexadecimal representation of the binary number `x`

    BIN2HEX(x,places)

#### Arguments

`x`: a binary number, either as a string or as a number involving only the digits 0 and 1

`places`: number of digits

#### Description

If `places` is given, `BIN2HEX` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `BIN2HEX` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`HEX2BIN`, `BIN2OCT`, `BIN2DEC`.

BIN2OCT

### `BIN2OCT`

octal representation of the binary number `x`

    BIN2OCT(x,places)

#### Arguments

`x`: a binary number, either as a string or as a number involving only the digits 0 and 1

`places`: number of digits

#### Description

If `places` is given, `BIN2OCT` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `BIN2OCT` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`OCT2BIN`, `BIN2DEC`, `BIN2HEX`.

CONVERT

### `CONVERT`

a converted measurement

    CONVERT(x,from,to)

#### Arguments

`x`: number

`from`: unit (string)

`to`: unit (string)

#### Description

`CONVERT` returns a conversion from one measurement system to another. `x` is a value in `from` units that is to be converted into `to` units.

`from` and `to` can be any of the following:

Weight and mass:

'g' Gram

'sg' Slug

'lbm' Pound

'u' U (atomic mass)

'ozm' Ounce

Distance:

'm' Meter

'mi' Statute mile

'Nmi' Nautical mile

'in' Inch

'ft' Foot

'yd' Yard

'ang' Angstrom

'Pica' Pica Points

'picapt' Pica Points

'pica' Pica

Time:

'yr' Year

'day' Day

'hr' Hour

'mn' Minute

'sec' Second

Pressure:

'Pa' Pascal

'atm' Atmosphere

'mmHg' mm of Mercury

Force:

'N' Newton

'dyn' Dyne

'lbf' Pound force

Energy:

'J' Joule

'e' Erg

'c' Thermodynamic calorie

'cal' IT calorie

'eV' Electron volt

'HPh' Horsepower-hour

'Wh' Watt-hour

'flb' Foot-pound

'BTU' BTU

Power:

'HP' Horsepower

'W' Watt

Magnetism:

'T' Tesla

'ga' Gauss

Temperature:

'C' Degree Celsius

'F' Degree Fahrenheit

'K' Degree Kelvin

Liquid measure:

'tsp' Teaspoon

'tbs' Tablespoon

'oz' Fluid ounce

'cup' Cup

'pt' Pint

'qt' Quart

'gal' Gallon

'l' Liter

For metric units any of the following prefixes can be used:

'Y' yotta 1E+24

'Z' zetta 1E+21

'E' exa 1E+18

'P' peta 1E+15

'T' tera 1E+12

'G' giga 1E+09

'M' mega 1E+06

'k' kilo 1E+03

'h' hecto 1E+02

'e' deca (deka) 1E+01

'd' deci 1E-01

'c' centi 1E-02

'm' milli 1E-03

'u' micro 1E-06

'n' nano 1E-09

'p' pico 1E-12

'f' femto 1E-15

'a' atto 1E-18

'z' zepto 1E-21

'y' yocto 1E-24

#### Note

If `from` and `to` are different types, `CONVERT` returns #N/A!

#### Microsoft Excel Compatibility

This function is Excel compatible (except "picapt").

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

DEC2BIN

### `DEC2BIN`

binary representation of the decimal number `x`

    DEC2BIN(x,places)

#### Arguments

`x`: integer (− 513 < `x` < 512)

`places`: number of digits

#### Description

If `places` is given and `x` is non-negative, `DEC2BIN` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `DEC2BIN` returns #NUM!

If `places` is given and `x` is negative, `places` is ignored.

#### Note

If `x` < − 512 or `x` > 511, `DEC2BIN` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`BIN2DEC`, `DEC2OCT`, `DEC2HEX`.

DEC2HEX

### `DEC2HEX`

hexadecimal representation of the decimal number `x`

    DEC2HEX(x,places)

#### Arguments

`x`: integer

`places`: number of digits

#### Description

If `places` is given, `DEC2HEX` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `DEC2HEX` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`HEX2DEC`, `DEC2BIN`, `DEC2OCT`.

DEC2OCT

### `DEC2OCT`

octal representation of the decimal number `x`

    DEC2OCT(x,places)

#### Arguments

`x`: integer

`places`: number of digits

#### Description

If `places` is given, `DEC2OCT` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `DEC2OCT` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`OCT2DEC`, `DEC2BIN`, `DEC2HEX`.

DECIMAL

### `DECIMAL`

decimal representation of `x`

    DECIMAL(x,base)

#### Arguments

`x`: number in base `base`

`base`: base of `x`, (2 ≤ `base` ≤ 36)

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`BASE`.

DELTA

### `DELTA`

Kronecker delta function

    DELTA(x0,x1)

#### Arguments

`x0`: number

`x1`: number, defaults to 0

#### Description

`DELTA` returns 1 if `x1` = `x0` and 0 otherwise.

#### Note

If either argument is non-numeric, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`EXACT`, `GESTEP`.

ERF

### `ERF`

Gauss error function

    ERF(lower,upper)

#### Arguments

`lower`: lower limit of the integral, defaults to 0

`upper`: upper limit of the integral

#### Description

`ERF` returns 2/sqrt(π)* integral from `lower` to `upper` of exp(-t*t) dt

#### Microsoft Excel Compatibility

This function is Excel compatible if two arguments are supplied and neither is negative.

#### See also

`ERFC`.

ERFC

### `ERFC`

Complementary Gauss error function

    ERFC(x)

#### Arguments

`x`: number

#### Description

`ERFC` returns 2/sqrt(π)* integral from `x` to ∞ of exp(-t*t) dt

#### See also

`ERF`.

GESTEP

### `GESTEP`

step function with step at `x1` evaluated at `x0`

    GESTEP(x0,x1)

#### Arguments

`x0`: number

`x1`: number, defaults to 0

#### Description

`GESTEP` returns 1 if `x1` ≤ `x0` and 0 otherwise.

#### Note

If either argument is non-numeric, #VALUE! is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DELTA`.

HEX2BIN

### `HEX2BIN`

binary representation of the hexadecimal number `x`

    HEX2BIN(x,places)

#### Arguments

`x`: a hexadecimal number, either as a string or as a number if no A to F are needed

`places`: number of digits

#### Description

If `places` is given, `HEX2BIN` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `HEX2BIN` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BIN2HEX`, `HEX2OCT`, `HEX2DEC`.

HEX2DEC

### `HEX2DEC`

decimal representation of the hexadecimal number `x`

    HEX2DEC(x)

#### Arguments

`x`: a hexadecimal number, either as a string or as a number if no A to F are needed

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DEC2HEX`, `HEX2BIN`, `HEX2OCT`.

HEX2OCT

### `HEX2OCT`

octal representation of the hexadecimal number `x`

    HEX2OCT(x,places)

#### Arguments

`x`: a hexadecimal number, either as a string or as a number if no A to F are needed

`places`: number of digits

#### Description

If `places` is given, `HEX2OCT` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `HEX2OCT` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`OCT2HEX`, `HEX2BIN`, `HEX2DEC`.

HEXREP

### `HEXREP`

hexadecimal representation of numeric value

    HEXREP(x)

#### Arguments

`x`: number

#### Description

`HEXREP` returns a hexadecimal string representation of `x`.

#### Note

This is a function meant for debugging. The layout of the result may change and even depend on how Gnumeric was compiled.

INVSUMINV

### `INVSUMINV`

the reciprocal of the sum of reciprocals of the arguments

    INVSUMINV(x0,x1,…)

#### Arguments

`x0`: non-negative number

`x1`: non-negative number

#### Description

`INVSUMINV` sum calculates the reciprocal (the inverse) of the sum of reciprocals (inverses) of all its arguments.

#### Note

If any of the arguments is negative, #VALUE! is returned.

If any argument is zero, the result is zero.

#### See also

`HARMEAN`.

OCT2BIN

### `OCT2BIN`

binary representation of the octal number `x`

    OCT2BIN(x,places)

#### Arguments

`x`: a octal number, either as a string or as a number

`places`: number of digits

#### Description

If `places` is given, `OCT2BIN` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `OCT2BIN` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BIN2OCT`, `OCT2DEC`, `OCT2HEX`.

OCT2DEC

### `OCT2DEC`

decimal representation of the octal number `x`

    OCT2DEC(x)

#### Arguments

`x`: a octal number, either as a string or as a number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DEC2OCT`, `OCT2BIN`, `OCT2HEX`.

OCT2HEX

### `OCT2HEX`

hexadecimal representation of the octal number `x`

    OCT2HEX(x,places)

#### Arguments

`x`: a octal number, either as a string or as a number

`places`: number of digits

#### Description

If `places` is given, `OCT2HEX` pads the result with zeros to achieve exactly `places` digits. If this is not possible, `OCT2HEX` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`HEX2OCT`, `OCT2BIN`, `OCT2DEC`.

## Erlang

DIMCIRC

### `DIMCIRC`

number of circuits required

    DIMCIRC(traffic,gos)

#### Arguments

`traffic`: number of calls

`gos`: grade of service

#### Description

`DIMCIRC` returns the number of circuits required given `traffic` calls with grade of service `gos`.

#### See also

`OFFCAP`, `OFFTRAF`, `PROBBLOCK`.

OFFCAP

### `OFFCAP`

traffic capacity

    OFFCAP(circuits,gos)

#### Arguments

`circuits`: number of circuits

`gos`: grade of service

#### Description

`OFFCAP` returns the traffic capacity given `circuits` circuits with grade of service `gos`.

#### See also

`DIMCIRC`, `OFFTRAF`, `PROBBLOCK`.

OFFTRAF

### `OFFTRAF`

predicted number of offered calls

    OFFTRAF(traffic,circuits)

#### Arguments

`traffic`: number of carried calls

`circuits`: number of circuits

#### Description

`OFFTRAF` returns the predicted number of offered calls given `traffic` carried calls (taken from measurements) on `circuits` circuits.

#### Note

`traffic` cannot exceed `circuits`.

#### See also

`PROBBLOCK`, `DIMCIRC`, `OFFCAP`.

PROBBLOCK

### `PROBBLOCK`

probability of blocking

    PROBBLOCK(traffic,circuits)

#### Arguments

`traffic`: number of calls

`circuits`: number of circuits

#### Description

`PROBBLOCK` returns probability of blocking when `traffic` calls load into `circuits` circuits.

#### Note

`traffic` cannot exceed `circuits`.

#### See also

`OFFTRAF`, `DIMCIRC`, `OFFCAP`.

## Finance

ACCRINT

### `ACCRINT`

accrued interest

    ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis,calc_method)

#### Arguments

`issue`: date of issue

`first_interest`: date of first interest payment

`settlement`: settlement date

`rate`: nominal annual interest rate

`par`: par value, defaults to $1000

`frequency`: number of interest payments per year

`basis`: calendar basis, defaults to 0

`calc_method`: calculation method, defaults to TRUE

#### Description

If `first_interest` < `settlement` and `calc_method` is TRUE, then `ACCRINT` returns the sum of the interest accrued in all coupon periods from `issue` date until `settlement` date.

If `first_interest` < `settlement` and `calc_method` is FALSE, then `ACCRINT` returns the sum of the interest accrued in all coupon periods from `first_interest` date until `settlement` date.

Otherwise `ACCRINT` returns the sum of the interest accrued in all coupon periods from `issue` date until `settlement` date.

#### Note

`frequency` must be one of 1, 2 or 4, but the exact value does not affect the result. `issue` must precede both `first_interest` and `settlement`. `frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`ACCRINTM`.

ACCRINTM

### `ACCRINTM`

accrued interest

    ACCRINTM(issue,maturity,rate,par,basis)

#### Arguments

`issue`: date of issue

`maturity`: maturity date

`rate`: nominal annual interest rate

`par`: par value

`basis`: calendar basis

#### Description

`ACCRINTM` calculates the accrued interest from `issue` to `maturity`.

#### Note

`par` defaults to $1000. If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`ACCRINT`.

AMORDEGRC

### `AMORDEGRC`

depreciation of an asset using French accounting conventions

    AMORDEGRC(cost,purchase_date,first_period,salvage,period,rate,basis)

#### Arguments

`cost`: initial cost of asset

`purchase_date`: date of purchase

`first_period`: end of first period

`salvage`: value after depreciation

`period`: subject period

`rate`: depreciation rate

`basis`: calendar basis

#### Description

`AMORDEGRC` calculates the depreciation of an asset using French accounting conventions. Assets purchased in the middle of a period take prorated depreciation into account. This is similar to AMORLINC, except that a depreciation coefficient is applied in the calculation depending on the life of the assets.

The depreciation coefficient used is:

1.0 for an expected lifetime less than 3 years,

1.5 for an expected lifetime of at least 3 years but less than 5 years,

2.0 for an expected lifetime of at least 5 years but at most 6 years,

2.5 for an expected lifetime of more than 6 years.

#### Note

Special depreciation rules are applied for the last two periods resulting in a possible total depreciation exceeding the difference of `cost` - `salvage`. Named for AMORtissement DEGRessif Comptabilite. If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`AMORLINC`.

AMORLINC

### `AMORLINC`

depreciation of an asset using French accounting conventions

    AMORLINC(cost,purchase_date,first_period,salvage,period,rate,basis)

#### Arguments

`cost`: initial cost of asset

`purchase_date`: date of purchase

`first_period`: end of first period

`salvage`: value after depreciation

`period`: subject period

`rate`: depreciation rate

`basis`: calendar basis

#### Description

`AMORLINC` calculates the depreciation of an asset using French accounting conventions. Assets purchased in the middle of a period take prorated depreciation into account.

#### Note

Named for AMORtissement LINeaire Comptabilite. If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`AMORDEGRC`.

COUPDAYBS

### `COUPDAYBS`

number of days from coupon period to settlement

    COUPDAYBS(settlement,maturity,frequency,basis,eom)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`frequency`: number of interest payments per year

`basis`: calendar basis

`eom`: end-of-month flag

#### Description

`COUPDAYBS` calculates the number of days from the beginning of the coupon period to the settlement date.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`COUPDAYS`.

COUPDAYS

### `COUPDAYS`

number of days in the coupon period of the settlement date

    COUPDAYS(settlement,maturity,frequency,basis,eom)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`frequency`: number of interest payments per year

`basis`: calendar basis

`eom`: end-of-month flag

#### Description

`COUPDAYS` calculates the number of days in the coupon period of the settlement date.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`COUPDAYBS`, `COUPDAYSNC`.

COUPDAYSNC

### `COUPDAYSNC`

number of days from the settlement date to the next coupon period

    COUPDAYSNC(settlement,maturity,frequency,basis,eom)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`frequency`: number of interest payments per year

`basis`: calendar basis

`eom`: end-of-month flag

#### Description

`COUPDAYSNC` calculates number of days from the settlement date to the next coupon period.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`COUPDAYS`, `COUPDAYBS`.

COUPNCD

### `COUPNCD`

the next coupon date after settlement

    COUPNCD(settlement,maturity,frequency,basis,eom)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`frequency`: number of interest payments per year

`basis`: calendar basis

`eom`: end-of-month flag

#### Description

`COUPNCD` calculates the coupon date following settlement.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`COUPPCD`, `COUPDAYS`, `COUPDAYBS`.

COUPNUM

### `COUPNUM`

number of coupons

    COUPNUM(settlement,maturity,frequency,basis,eom)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`frequency`: number of interest payments per year

`basis`: calendar basis

`eom`: end-of-month flag

#### Description

`COUPNUM` calculates the number of coupons to be paid between the settlement and maturity dates, rounded up.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`COUPNCD`, `COUPPCD`.

COUPPCD

### `COUPPCD`

the last coupon date before settlement

    COUPPCD(settlement,maturity,frequency,basis,eom)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`frequency`: number of interest payments per year

`basis`: calendar basis

`eom`: end-of-month flag

#### Description

`COUPPCD` calculates the coupon date preceding settlement.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`COUPNCD`, `COUPDAYS`, `COUPDAYBS`.

CUM_BIV_NORM_DIST

### `CUM_BIV_NORM_DIST`

cumulative bivariate normal distribution

    CUM_BIV_NORM_DIST(a,b,rho)

#### Arguments

`a`: limit for first random variable

`b`: limit for second random variable

`rho`: correlation of the two random variables

#### Description

`CUM_BIV_NORM_DIST` calculates the probability that two standard normal distributed random variables with correlation `rho` are respectively each less than `a` and `b`.

CUMIPMT

### `CUMIPMT`

cumulative interest payment

    CUMIPMT(rate,nper,pv,start_period,end_period,type)

#### Arguments

`rate`: interest rate per period

`nper`: number of periods

`pv`: present value

`start_period`: first period to accumulate for

`end_period`: last period to accumulate for

`type`: payment type

#### Description

`CUMIPMT` calculates the cumulative interest paid on a loan from `start_period` to `end_period`.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`IPMT`.

CUMPRINC

### `CUMPRINC`

cumulative principal

    CUMPRINC(rate,nper,pv,start_period,end_period,type)

#### Arguments

`rate`: interest rate per period

`nper`: number of periods

`pv`: present value

`start_period`: first period to accumulate for

`end_period`: last period to accumulate for

`type`: payment type

#### Description

`CUMPRINC` calculates the cumulative principal paid on a loan from `start_period` to `end_period`.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`PPMT`.

DB

### `DB`

depreciation of an asset

    DB(cost,salvage,life,period,month)

#### Arguments

`cost`: initial cost of asset

`salvage`: value after depreciation

`life`: number of periods

`period`: subject period

`month`: number of months in first year of depreciation

#### Description

`DB` calculates the depreciation of an asset for a given period using the fixed-declining balance method.

#### See also

`DDB`, `SLN`, `SYD`.

DDB

### `DDB`

depreciation of an asset

    DDB(cost,salvage,life,period,factor)

#### Arguments

`cost`: initial cost of asset

`salvage`: value after depreciation

`life`: number of periods

`period`: subject period

`factor`: factor at which the balance declines

#### Description

`DDB` calculates the depreciation of an asset for a given period using the double-declining balance method.

#### See also

`DB`, `SLN`, `SYD`.

DISC

### `DISC`

discount rate

    DISC(settlement,maturity,par,redemption,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`par`: price per $100 face value

`redemption`: amount received at maturity

`basis`: calendar basis

#### Description

`DISC` calculates the discount rate for a security.

#### Note

`redemption` is the redemption value per $100 face value. If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`PRICEMAT`.

DOLLARDE

### `DOLLARDE`

convert to decimal dollar amount

    DOLLARDE(fractional_dollar,fraction)

#### Arguments

`fractional_dollar`: amount to convert

`fraction`: denominator

#### Description

`DOLLARDE` converts a fractional dollar amount into a decimal amount. This is the inverse of the DOLLARFR function.

#### See also

`DOLLARFR`.

DOLLARFR

### `DOLLARFR`

convert to dollar fraction

    DOLLARFR(decimal_dollar,fraction)

#### Arguments

`decimal_dollar`: amount to convert

`fraction`: denominator

#### Description

`DOLLARFR` converts a decimal dollar amount into a fractional amount which is represented as the digits after the decimal point. For example, 2/8 would be represented as .2 while 3/16 would be represented as .03. This is the inverse of the DOLLARDE function.

#### See also

`DOLLARDE`.

DURATION

### `DURATION`

the (Macaulay) duration of a security

    DURATION(settlement,maturity,coupon,yield,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`coupon`: annual coupon rate

`yield`: annual yield of security

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`DURATION` calculates the (Macaulay) duration of a security.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`MDURATION`, `G_DURATION`.

EFFECT

### `EFFECT`

effective interest rate

    EFFECT(rate,nper)

#### Arguments

`rate`: nominal annual interest rate

`nper`: number of periods used for compounding

#### Description

`EFFECT` calculates the effective interest rate using the formula (1+`rate`/`nper`)^`nper`-1.

#### See also

`NOMINAL`.

EURO

### `EURO`

equivalent of 1 EUR

    EURO(currency)

#### Arguments

`currency`: three-letter currency code

#### Description

`EURO` calculates the national currency amount corresponding to 1 EUR for any of the national currencies that were replaced by the Euro on its introduction.

#### Note

`currency` must be one of ATS (Austria), BEF (Belgium), CYP (Cyprus), DEM (Germany), EEK (Estonia), ESP (Spain), EUR (Euro), FIM (Finland), FRF (France), GRD (Greece), IEP (Ireland), ITL (Italy), LUF (Luxembourg), MTL (Malta), NLG (The Netherlands), PTE (Portugal), SIT (Slovenia), or SKK (Slovakia). This function is not likely to be useful anymore.

#### See also

`EUROCONVERT`.

EUROCONVERT

### `EUROCONVERT`

pre-Euro amount from one currency to another

    EUROCONVERT(n,source,target,full_precision,triangulation_precision)

#### Arguments

`n`: amount

`source`: three-letter source currency code

`target`: three-letter target currency code

`full_precision`: whether to provide the full precision; defaults to false

`triangulation_precision`: number of digits (at least 3) to be rounded to after conversion of the source currency to euro; defaults to no rounding

#### Description

`EUROCONVERT` converts `n` units of currency `source` to currency `target`. The rates used are the official ones used on the introduction of the Euro.

#### Note

If `full_precision` is true, the result is not rounded; if it false the result is rounded to 0 or 2 decimals depending on the target currency; defaults to false. `source` and `target` must be one of the currencies listed for the EURO function. This function is not likely to be useful anymore.

#### See also

`EURO`.

FV

### `FV`

future value

    FV(rate,nper,pmt,pv,type)

#### Arguments

`rate`: effective interest rate per period

`nper`: number of periods

`pmt`: payment at each period

`pv`: present value

`type`: payment type

#### Description

`FV` calculates the future value of `pv` moved `nper` periods into the future, assuming a periodic payment of `pmt` and an interest rate of `rate` per period.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`PV`.

FVSCHEDULE

### `FVSCHEDULE`

future value

    FVSCHEDULE(principal,schedule)

#### Arguments

`principal`: initial value

`schedule`: range of interest rates

#### Description

`FVSCHEDULE` calculates the future value of `principal` after applying a range of interest rates with compounding.

#### See also

`FV`.

G_DURATION

### `G_DURATION`

the duration of a investment

    G_DURATION(rate,pv,fv)

#### Arguments

`rate`: effective annual interest rate

`pv`: present value

`fv`: future value

#### Description

`G_DURATION` calculates the number of periods needed for an investment to attain a desired value.

#### OpenDocument Format (ODF) Compatibility

`G_DURATION` is the OpenFormula function PDURATION.

#### See also

`FV`, `PV`, `DURATION`, `MDURATION`.

INTRATE

### `INTRATE`

interest rate

    INTRATE(settlement,maturity,investment,redemption,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`investment`: amount paid on settlement

`redemption`: amount received at maturity

`basis`: calendar basis

#### Description

`INTRATE` calculates the interest of a fully vested security.

#### Note

If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`RECEIVED`.

IPMT

### `IPMT`

interest payment for period

    IPMT(rate,per,nper,pv,fv,type)

#### Arguments

`rate`: effective annual interest rate

`per`: period number

`nper`: number of periods

`pv`: present value

`fv`: future value

`type`: payment type

#### Description

`IPMT` calculates the interest part of an annuity's payment for period number `per`.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`PPMT`.

IRR

### `IRR`

internal rate of return

    IRR(values,guess)

#### Arguments

`values`: cash flow

`guess`: an estimate of what the result should be

#### Description

`IRR` calculates the internal rate of return of a cash flow with periodic payments. `values` lists the payments (negative values) and receipts (positive values) for each period.

#### Note

The optional `guess` is needed because there can be more than one valid result. It defaults to 10%.

#### See also

`XIRR`.

ISPMT

### `ISPMT`

interest payment for period

    ISPMT(rate,per,nper,pv)

#### Arguments

`rate`: effective annual interest rate

`per`: period number

`nper`: number of periods

`pv`: present value

#### Description

`ISPMT` calculates the interest payment for period number `per`.

#### See also

`PV`.

MDURATION

### `MDURATION`

the modified (Macaulay) duration of a security

    MDURATION(settlement,maturity,coupon,yield,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`coupon`: annual coupon rate

`yield`: annual yield of security

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`MDURATION` calculates the modified (Macaulay) duration of a security.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`DURATION`, `G_DURATION`.

MIRR

### `MIRR`

modified internal rate of return

    MIRR(values,finance_rate,reinvest_rate)

#### Arguments

`values`: cash flow

`finance_rate`: interest rate for financing cost

`reinvest_rate`: interest rate for reinvestments

#### Description

`MIRR` calculates the modified internal rate of return of a periodic cash flow.

#### See also

`IRR`, `XIRR`.

NOMINAL

### `NOMINAL`

nominal interest rate

    NOMINAL(rate,nper)

#### Arguments

`rate`: effective annual interest rate

`nper`: number of periods used for compounding

#### Description

`NOMINAL` calculates the nominal interest rate from the effective rate.

#### See also

`EFFECT`.

NPER

### `NPER`

number of periods

    NPER(rate,pmt,pv,fv,type)

#### Arguments

`rate`: effective annual interest rate

`pmt`: payment at each period

`pv`: present value

`fv`: future value

`type`: payment type

#### Description

`NPER` calculates the number of periods of an investment based on periodic constant payments and a constant interest rate.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`PV`, `FV`.

NPV

### `NPV`

net present value

    NPV(rate,value1,value2,…)

#### Arguments

`rate`: effective interest rate per period

`value1`: cash flow for period 1

`value2`: cash flow for period 2

#### Description

`NPV` calculates the net present value of a cash flow.

#### See also

`PV`.

ODDFPRICE

### `ODDFPRICE`

price of a security that has an odd first period

    ODDFPRICE(settlement,maturity,issue,first_interest,rate,yield,redemption,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`issue`: date of issue

`first_interest`: first interest date

`rate`: nominal annual interest rate

`yield`: annual yield of security

`redemption`: amount received at maturity

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`ODDFPRICE` calculates the price per $100 face value of a security that pays periodic interest, but has an odd first period.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`ODDLPRICE`, `ODDFYIELD`.

ODDFYIELD

### `ODDFYIELD`

yield of a security that has an odd first period

    ODDFYIELD(settlement,maturity,issue,first_interest,rate,price,redemption,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`issue`: date of issue

`first_interest`: first interest date

`rate`: nominal annual interest rate

`price`: price of security

`redemption`: amount received at maturity

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`ODDFYIELD` calculates the yield of a security that pays periodic interest, but has an odd first period.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`ODDFPRICE`, `ODDLYIELD`.

ODDLPRICE

### `ODDLPRICE`

price of a security that has an odd last period

    ODDLPRICE(settlement,maturity,last_interest,rate,yield,redemption,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`last_interest`: last interest date

`rate`: nominal annual interest rate

`yield`: annual yield of security

`redemption`: amount received at maturity

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`ODDLPRICE` calculates the price per $100 face value of a security that pays periodic interest, but has an odd last period.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`YIELD`, `DURATION`.

ODDLYIELD

### `ODDLYIELD`

yield of a security that has an odd last period

    ODDLYIELD(settlement,maturity,last_interest,rate,price,redemption,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`last_interest`: last interest date

`rate`: nominal annual interest rate

`price`: price of security

`redemption`: amount received at maturity

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`ODDLYIELD` calculates the yield of a security that pays periodic interest, but has an odd last period.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`YIELD`, `DURATION`.

OPT_2_ASSET_CORRELATION

### `OPT_2_ASSET_CORRELATION`

theoretical price of options on 2 assets with correlation `rho`

    OPT_2_ASSET_CORRELATION(call_put_flag,spot1,spot2,strike1,strike2,time,cost_of_carry1,cost_of_carry2,rate,volatility1,volatility2,rho)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot1`: spot price of the underlying asset of the first option

`spot2`: spot price of the underlying asset of the second option

`strike1`: strike prices of the first option

`strike2`: strike prices of the second option

`time`: time to maturity in years

`cost_of_carry1`: net cost of holding the underlying asset of the first option (for common stocks, the risk free rate less the dividend yield)

`cost_of_carry2`: net cost of holding the underlying asset of the second option (for common stocks, the risk free rate less the dividend yield)

`rate`: annualized risk-free interest rate

`volatility1`: annualized volatility in price of the underlying asset of the first option

`volatility2`: annualized volatility in price of the underlying asset of the second option

`rho`: correlation between the two underlying assets

#### Description

`OPT_2_ASSET_CORRELATION` models the theoretical price of options on 2 assets with correlation `rho`. The payoff for a call is max(`spot2` - `strike2`,0) if `spot1` > `strike1` or 0 otherwise. The payoff for a put is max (`strike2` - `spot2`, 0) if `spot1` < `strike1` or 0 otherwise.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_AMER_EXCHANGE

### `OPT_AMER_EXCHANGE`

theoretical price of an American option to exchange assets

    OPT_AMER_EXCHANGE(spot1,spot2,qty1,qty2,time,rate,cost_of_carry1,cost_of_carry2,volatility1,volatility2,rho)

#### Arguments

`spot1`: spot price of asset 1

`spot2`: spot price of asset 2

`qty1`: quantity of asset 1

`qty2`: quantity of asset 2

`time`: time to maturity in years

`rate`: annualized risk-free interest rate

`cost_of_carry1`: net cost of holding asset 1 (for common stocks, the risk free rate less the dividend yield)

`cost_of_carry2`: net cost of holding asset 2 (for common stocks, the risk free rate less the dividend yield)

`volatility1`: annualized volatility in price of asset 1

`volatility2`: annualized volatility in price of asset 2

`rho`: correlation between the prices of the two assets

#### Description

`OPT_AMER_EXCHANGE` models the theoretical price of an American option to exchange one asset with quantity `qty2` and spot price `spot2` for another with quantity `qty1` and spot price `spot1`.

#### See also

`OPT_EURO_EXCHANGE`, `OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_BAW_AMER

### `OPT_BAW_AMER`

theoretical price of an option according to the Barone Adesie & Whaley approximation

    OPT_BAW_AMER(call_put_flag,spot,strike,time,rate,cost_of_carry,volatility)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in days

`rate`: annualized risk-free interest rate

`cost_of_carry`: net cost of holding the underlying asset

`volatility`: annualized volatility of the asset

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_BINOMIAL

### `OPT_BINOMIAL`

theoretical price of either an American or European style option using a binomial tree

    OPT_BINOMIAL(amer_euro_flag,call_put_flag,num_time_steps,spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`amer_euro_flag`: 'a' for an American style option or 'e' for a European style option

`call_put_flag`: 'c' for a call and 'p' for a put

`num_time_steps`: number of time steps used in the valuation

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: annualized risk-free interest rate

`volatility`: annualized volatility of the asset

`cost_of_carry`: net cost of holding the underlying asset

#### Note

A larger `num_time_steps` yields greater accuracy but `OPT_BINOMIAL` is slower to calculate.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_BJER_STENS

### `OPT_BJER_STENS`

theoretical price of American options according to the Bjerksund & Stensland approximation technique

    OPT_BJER_STENS(call_put_flag,spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in days

`rate`: annualized risk-free interest rate

`volatility`: annualized volatility of the asset

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_BS

### `OPT_BS`

price of a European option

    OPT_BS(call_put_flag,spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_BS` uses the Black-Scholes model to calculate the price of a European option struck at `strike` on an asset with spot price `spot`.

#### Note

The returned value will be expressed in the same units as `strike` and `spot`.

#### See also

`OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_VEGA`, `OPT_BS_GAMMA`.

OPT_BS_CARRYCOST

### `OPT_BS_CARRYCOST`

elasticity of a European option

    OPT_BS_CARRYCOST(call_put_flag,spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_BS_CARRYCOST` uses the Black-Scholes model to calculate the 'elasticity' of a European option struck at `strike` on an asset with spot price `spot`. The elasticity of an option is the rate of change of its price with respect to its `cost_of_carry`.

#### Note

Elasticity is expressed as the rate of change of the option value, per 100% volatility.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_BS_DELTA

### `OPT_BS_DELTA`

delta of a European option

    OPT_BS_DELTA(call_put_flag,spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_BS_DELTA` uses the Black-Scholes model to calculate the 'delta' of a European option struck at `strike` on an asset with spot price `spot`.

#### Note

The returned value will be expressed in the same units as `strike` and `spot`.

#### See also

`OPT_BS`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_VEGA`, `OPT_BS_GAMMA`.

OPT_BS_GAMMA

### `OPT_BS_GAMMA`

gamma of a European option

    OPT_BS_GAMMA(spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_BS_GAMMA` uses the Black-Scholes model to calculate the 'gamma' of a European option struck at `strike` on an asset with spot price `spot`. The gamma of an option is the second derivative of its price with respect to the price of the underlying asset.

#### Note

Gamma is expressed as the rate of change of delta per unit change in `spot`. Gamma is the same for calls and puts.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_VEGA`.

OPT_BS_RHO

### `OPT_BS_RHO`

rho of a European option

    OPT_BS_RHO(call_put_flag,spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_BS_RHO` uses the Black-Scholes model to calculate the 'rho' of a European option struck at `strike` on an asset with spot price `spot`. The rho of an option is the rate of change of its price with respect to the risk free interest rate.

#### Note

Rho is expressed as the rate of change of the option value, per 100% change in `rate`.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_THETA`, `OPT_BS_VEGA`, `OPT_BS_GAMMA`.

OPT_BS_THETA

### `OPT_BS_THETA`

theta of a European option

    OPT_BS_THETA(call_put_flag,spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_BS_THETA` uses the Black-Scholes model to calculate the 'theta' of a European option struck at `strike` on an asset with spot price `spot`. The theta of an option is the rate of change of its price with respect to time to expiry.

#### Note

Theta is expressed as the negative of the rate of change of the option value, per 365.25 days.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_VEGA`, `OPT_BS_GAMMA`.

OPT_BS_VEGA

### `OPT_BS_VEGA`

vega of a European option

    OPT_BS_VEGA(spot,strike,time,rate,volatility,cost_of_carry)

#### Arguments

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_BS_VEGA` uses the Black-Scholes model to calculate the 'vega' of a European option struck at `strike` on an asset with spot price `spot`. The vega of an option is the rate of change of its price with respect to volatility.

#### Note

Vega is the same for calls and puts. Vega is expressed as the rate of change of option value, per 100% volatility.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_COMPLEX_CHOOSER

### `OPT_COMPLEX_CHOOSER`

theoretical price of a complex chooser option

    OPT_COMPLEX_CHOOSER(spot,strike_call,strike_put,time,time_call,time_put,rate,cost_of_carry,volatility)

#### Arguments

`spot`: spot price

`strike_call`: strike price, if exercised as a call option

`strike_put`: strike price, if exercised as a put option

`time`: time in years until the holder chooses a put or a call option

`time_call`: time in years to maturity of the call option if chosen

`time_put`: time in years to maturity of the put option if chosen

`rate`: annualized risk-free interest rate

`cost_of_carry`: net cost of holding the underlying asset

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_EURO_EXCHANGE

### `OPT_EURO_EXCHANGE`

theoretical price of a European option to exchange assets

    OPT_EURO_EXCHANGE(spot1,spot2,qty1,qty2,time,rate,cost_of_carry1,cost_of_carry2,volatility1,volatility2,rho)

#### Arguments

`spot1`: spot price of asset 1

`spot2`: spot price of asset 2

`qty1`: quantity of asset 1

`qty2`: quantity of asset 2

`time`: time to maturity in years

`rate`: annualized risk-free interest rate

`cost_of_carry1`: net cost of holding asset 1 (for common stocks, the risk free rate less the dividend yield)

`cost_of_carry2`: net cost of holding asset 2 (for common stocks, the risk free rate less the dividend yield)

`volatility1`: annualized volatility in price of asset 1

`volatility2`: annualized volatility in price of asset 2

`rho`: correlation between the prices of the two assets

#### Description

`OPT_EURO_EXCHANGE` models the theoretical price of a European option to exchange one asset with quantity `qty2` and spot price `spot2` for another with quantity `qty1` and spot price `spot1`.

#### See also

`OPT_AMER_EXCHANGE`, `OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_EXEC

### `OPT_EXEC`

theoretical price of executive stock options

    OPT_EXEC(call_put_flag,spot,strike,time,rate,volatility,cost_of_carry,lambda)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in days

`rate`: annualized risk-free interest rate

`volatility`: annualized volatility of the asset

`cost_of_carry`: net cost of holding the underlying asset

`lambda`: jump rate for executives

#### Note

The model assumes executives forfeit their options if they leave the company.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_EXTENDIBLE_WRITER

### `OPT_EXTENDIBLE_WRITER`

theoretical price of extendible writer options

    OPT_EXTENDIBLE_WRITER(call_put_flag,spot,strike1,strike2,time1,time2,rate,cost_of_carry,volatility)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike1`: strike price at which the option is struck

`strike2`: strike price at which the option is re-struck if out of the money at `time1`

`time1`: initial maturity of the option in years

`time2`: extended maturity in years if chosen

`rate`: annualized risk-free interest rate

`cost_of_carry`: net cost of holding the underlying asset

`volatility`: annualized volatility of the asset

#### Description

`OPT_EXTENDIBLE_WRITER` models the theoretical price of extendible writer options. These are options that have their maturity extended to `time2` if the option is out of the money at `time1`.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_FIXED_STRK_LKBK

### `OPT_FIXED_STRK_LKBK`

theoretical price of a fixed-strike lookback option

    OPT_FIXED_STRK_LKBK(call_put_flag,spot,spot_min,spot_max,strike,time,rate,cost_of_carry,volatility)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`spot_min`: minimum spot price of the underlying asset so far observed

`spot_max`: maximum spot price of the underlying asset so far observed

`strike`: strike price

`time`: time to maturity in years

`rate`: annualized risk-free interest rate

`cost_of_carry`: net cost of holding the underlying asset

`volatility`: annualized volatility of the asset

#### Description

`OPT_FIXED_STRK_LKBK` determines the theoretical price of a fixed-strike lookback option where the holder of the option may exercise on expiry at the most favourable price observed during the options life of the underlying asset.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_FLOAT_STRK_LKBK

### `OPT_FLOAT_STRK_LKBK`

theoretical price of floating-strike lookback option

    OPT_FLOAT_STRK_LKBK(call_put_flag,spot,spot_min,spot_max,time,rate,cost_of_carry,volatility)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`spot_min`: minimum spot price of the underlying asset so far observed

`spot_max`: maximum spot price of the underlying asset so far observed

`time`: time to maturity in years

`rate`: annualized risk-free interest rate

`cost_of_carry`: net cost of holding the underlying asset

`volatility`: annualized volatility of the asset

#### Description

`OPT_FLOAT_STRK_LKBK` determines the theoretical price of a floating-strike lookback option where the holder of the option may exercise on expiry at the most favourable price observed during the options life of the underlying asset.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_FORWARD_START

### `OPT_FORWARD_START`

theoretical price of forward start options

    OPT_FORWARD_START(call_put_flag,spot,alpha,time_start,time,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`alpha`: fraction setting the strike price at the future date `time_start`

`time_start`: time until the option starts in days

`time`: time to maturity in days

`rate`: annualized risk-free interest rate

`volatility`: annualized volatility of the asset

`cost_of_carry`: net cost of holding the underlying asset

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_FRENCH

### `OPT_FRENCH`

theoretical price of a European option adjusted for trading day volatility

    OPT_FRENCH(call_put_flag,spot,strike,time,ttime,rate,volatility,cost_of_carry)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: ratio of the number of calendar days to exercise and the number of calendar days in the year

`ttime`: ratio of the number of trading days to exercise and the number of trading days in the year

`rate`: risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`cost_of_carry`: net cost of holding the underlying asset (for common stocks, the risk free rate less the dividend yield), defaults to 0

#### Description

`OPT_FRENCH` values the theoretical price of a European option adjusted for trading day volatility, struck at `strike` on an asset with spot price `spot`.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_GARMAN_KOHLHAGEN

### `OPT_GARMAN_KOHLHAGEN`

theoretical price of a European currency option

    OPT_GARMAN_KOHLHAGEN(call_put_flag,spot,strike,time,domestic_rate,foreign_rate,volatility)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: number of days to exercise

`domestic_rate`: domestic risk-free interest rate to the exercise date in percent

`foreign_rate`: foreign risk-free interest rate to the exercise date in percent

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

#### Description

`OPT_GARMAN_KOHLHAGEN` values the theoretical price of a European currency option struck at `strike` on an asset with spot price `spot`.

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_JUMP_DIFF

### `OPT_JUMP_DIFF`

theoretical price of an option according to the Jump Diffusion process

    OPT_JUMP_DIFF(call_put_flag,spot,strike,time,rate,volatility,lambda,gamma)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time`: time to maturity in years

`rate`: the annualized rate of interest

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

`lambda`: expected number of 'jumps' per year

`gamma`: proportion of volatility explained by the 'jumps'

#### Description

`OPT_JUMP_DIFF` models the theoretical price of an option according to the Jump Diffusion process (Merton).

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_MILTERSEN_SCHWARTZ

### `OPT_MILTERSEN_SCHWARTZ`

theoretical price of options on commodities futures according to Miltersen & Schwartz

    OPT_MILTERSEN_SCHWARTZ(call_put_flag,p_t,f_t,strike,t1,t2,v_s,v_e,v_f,rho_se,rho_sf,rho_ef,kappa_e,kappa_f)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`p_t`: zero coupon bond with expiry at option maturity

`f_t`: futures price

`strike`: strike price

`t1`: time to maturity of the option

`t2`: time to maturity of the underlying commodity futures contract

`v_s`: volatility of the spot commodity price

`v_e`: volatility of the future convenience yield

`v_f`: volatility of the forward rate of interest

`rho_se`: correlation between the spot commodity price and the convenience yield

`rho_sf`: correlation between the spot commodity price and the forward interest rate

`rho_ef`: correlation between the forward interest rate and the convenience yield

`kappa_e`: speed of mean reversion of the convenience yield

`kappa_f`: speed of mean reversion of the forward interest rate

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_ON_OPTIONS

### `OPT_ON_OPTIONS`

theoretical price of options on options

    OPT_ON_OPTIONS(type_flag,spot,strike1,strike2,time1,time2,rate,cost_of_carry,volatility)

#### Arguments

`type_flag`: 'cc' for calls on calls, 'cp' for calls on puts, and so on for 'pc', and 'pp'

`spot`: spot price

`strike1`: strike price at which the option being valued is struck

`strike2`: strike price at which the underlying option is struck

`time1`: time in years to maturity of the option

`time2`: time in years to the maturity of the underlying option

`rate`: annualized risk-free interest rate

`cost_of_carry`: net cost of holding the underlying asset of the underlying option

`volatility`: annualized volatility in price of the underlying asset of the underlying option

#### Note

For common stocks, `cost_of_carry` is the risk free rate less the dividend yield. `time2` ≥ `time1`

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_RGW

### `OPT_RGW`

theoretical price of an American option according to the Roll-Geske-Whaley approximation

    OPT_RGW(spot,strike,time_payout,time_exp,rate,d,volatility)

#### Arguments

`spot`: spot price

`strike`: strike price

`time_payout`: time to dividend payout

`time_exp`: time to expiration

`rate`: annualized interest rate

`d`: amount of the dividend to be paid expressed in currency

`volatility`: annualized volatility of the asset in percent for the period through to the exercise date

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_SIMPLE_CHOOSER

### `OPT_SIMPLE_CHOOSER`

theoretical price of a simple chooser option

    OPT_SIMPLE_CHOOSER(call_put_flag,spot,strike,time1,time2,cost_of_carry,volatility)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`time1`: time in years until the holder chooses a put or a call option

`time2`: time in years until the chosen option expires

`cost_of_carry`: net cost of holding the underlying asset

`volatility`: annualized volatility of the asset

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_SPREAD_APPROX

### `OPT_SPREAD_APPROX`

theoretical price of a European option on the spread between two futures contracts

    OPT_SPREAD_APPROX(call_put_flag,fut_price1,fut_price2,strike,time,rate,volatility1,volatility2,rho)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`fut_price1`: price of the first futures contract

`fut_price2`: price of the second futures contract

`strike`: strike price

`time`: time to maturity in years

`rate`: annualized risk-free interest rate

`volatility1`: annualized volatility in price of the first underlying futures contract

`volatility2`: annualized volatility in price of the second underlying futures contract

`rho`: correlation between the two futures contracts

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

OPT_TIME_SWITCH

### `OPT_TIME_SWITCH`

theoretical price of time switch options

    OPT_TIME_SWITCH(call_put_flag,spot,strike,a,time,m,dt,rate,cost_of_carry,volatility)

#### Arguments

`call_put_flag`: 'c' for a call and 'p' for a put

`spot`: spot price

`strike`: strike price

`a`: amount received for each time period

`time`: time to maturity in years

`m`: number of time units the option has already met the condition

`dt`: agreed upon discrete time period expressed as a fraction of a year

`rate`: annualized risk-free interest rate

`cost_of_carry`: net cost of holding the underlying asset

`volatility`: annualized volatility of the asset

#### Description

`OPT_TIME_SWITCH` models the theoretical price of time switch options. (Pechtl 1995). The holder receives `a` * `dt` for each period that the asset price was greater than `strike` (for a call) or below it (for a put).

#### See also

`OPT_BS`, `OPT_BS_DELTA`, `OPT_BS_RHO`, `OPT_BS_THETA`, `OPT_BS_GAMMA`.

PMT

### `PMT`

payment for annuity

    PMT(rate,nper,pv,fv,type)

#### Arguments

`rate`: effective annual interest rate

`nper`: number of periods

`pv`: present value

`fv`: future value

`type`: payment type

#### Description

`PMT` calculates the payment amount for an annuity.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`PV`, `FV`, `RATE`, `ISPMT`.

PPMT

### `PPMT`

interest payment for period

    PPMT(rate,per,nper,pv,fv,type)

#### Arguments

`rate`: effective annual interest rate

`per`: period number

`nper`: number of periods

`pv`: present value

`fv`: future value

`type`: payment type

#### Description

`PPMT` calculates the principal part of an annuity's payment for period number `per`.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`IPMT`.

PRICE

### `PRICE`

price of a security

    PRICE(settlement,maturity,rate,yield,redemption,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`rate`: nominal annual interest rate

`yield`: annual yield of security

`redemption`: amount received at maturity

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`PRICE` calculates the price per $100 face value of a security that pays periodic interest.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`YIELD`, `DURATION`.

PRICEDISC

### `PRICEDISC`

discounted price

    PRICEDISC(settlement,maturity,discount,redemption,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`discount`: annual rate at which to discount

`redemption`: amount received at maturity

`basis`: calendar basis

#### Description

`PRICEDISC` calculates the price per $100 face value of a bond that does not pay interest at maturity.

#### Note

If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`PRICEMAT`.

PRICEMAT

### `PRICEMAT`

price at maturity

    PRICEMAT(settlement,maturity,issue,discount,yield,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`issue`: date of issue

`discount`: annual rate at which to discount

`yield`: annual yield of security

`basis`: calendar basis

#### Description

`PRICEMAT` calculates the price per $100 face value of a bond that pays interest at maturity.

#### Note

If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`PRICEDISC`.

PV

### `PV`

present value

    PV(rate,nper,pmt,fv,type)

#### Arguments

`rate`: effective interest rate per period

`nper`: number of periods

`pmt`: payment at each period

`fv`: future value

`type`: payment type

#### Description

`PV` calculates the present value of `fv` which is `nper` periods into the future, assuming a periodic payment of `pmt` and an interest rate of `rate` per period.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`FV`.

RATE

### `RATE`

rate of investment

    RATE(nper,pmt,pv,fv,type,guess)

#### Arguments

`nper`: number of periods

`pmt`: payment at each period

`pv`: present value

`fv`: future value

`type`: payment type

`guess`: an estimate of what the result should be

#### Description

`RATE` calculates the rate of return.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period. The optional `guess` is needed because there can be more than one valid result. It defaults to 10%.

#### See also

`PV`, `FV`.

RECEIVED

### `RECEIVED`

amount to be received at maturity

    RECEIVED(settlement,maturity,investment,rate,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`investment`: amount paid on settlement

`rate`: nominal annual interest rate

`basis`: calendar basis

#### Description

`RECEIVED` calculates the amount to be received when a security matures.

#### Note

If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`INTRATE`.

RRI

### `RRI`

equivalent interest rate for an investment increasing in value

    RRI(p,pv,fv)

#### Arguments

`p`: number of periods

`pv`: present value

`fv`: future value

#### Description

`RRI` determines an equivalent interest rate for an investment that increases in value. The interest is compounded after each complete period.

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period. Note that `p` need not be an integer but for fractional value the calculated rate is only approximate.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`PV`, `FV`, `RATE`.

SLN

### `SLN`

depreciation of an asset

    SLN(cost,salvage,life)

#### Arguments

`cost`: initial cost of asset

`salvage`: value after depreciation

`life`: number of periods

#### Description

`SLN` calculates the depreciation of an asset using the straight-line method.

#### See also

`DB`, `DDB`, `SYD`.

SYD

### `SYD`

sum-of-years depreciation

    SYD(cost,salvage,life,period)

#### Arguments

`cost`: initial cost of asset

`salvage`: value after depreciation

`life`: number of periods

`period`: subject period

#### Description

`SYD` calculates the depreciation of an asset using the sum-of-years method.

#### See also

`DB`, `DDB`, `SLN`.

TBILLEQ

### `TBILLEQ`

bond-equivalent yield for a treasury bill

    TBILLEQ(settlement,maturity,discount)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`discount`: annual rate at which to discount

#### Description

`TBILLEQ` calculates the bond-equivalent yield for a treasury bill.

#### See also

`TBILLPRICE`, `TBILLYIELD`.

TBILLPRICE

### `TBILLPRICE`

price of a treasury bill

    TBILLPRICE(settlement,maturity,discount)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`discount`: annual rate at which to discount

#### Description

`TBILLPRICE` calculates the price per $100 face value for a treasury bill.

#### See also

`TBILLEQ`, `TBILLYIELD`.

TBILLYIELD

### `TBILLYIELD`

yield of a treasury bill

    TBILLYIELD(settlement,maturity,price)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`price`: price

#### Description

`TBILLYIELD` calculates the yield of a treasury bill.

#### See also

`TBILLEQ`, `TBILLPRICE`.

VDB

### `VDB`

depreciation of an asset

    VDB(cost,salvage,life,start_period,end_period,factor,no_switch)

#### Arguments

`cost`: initial cost of asset

`salvage`: value after depreciation

`life`: number of periods

`start_period`: first period to accumulate for

`end_period`: last period to accumulate for

`factor`: factor at which the balance declines

`no_switch`: do not switch to straight-line depreciation

#### Description

`VDB` calculates the depreciation of an asset for a given period range using the variable-rate declining balance method.

#### Note

If `no_switch` is FALSE, the calculation switches to straight-line depreciation when depreciation is greater than the declining balance calculation.

#### See also

`DB`, `DDB`.

XIRR

### `XIRR`

internal rate of return

    XIRR(values,dates,guess)

#### Arguments

`values`: cash flow

`dates`: dates of cash flow

`guess`: an estimate of what the result should be

#### Description

`XIRR` calculates the annualized internal rate of return of a cash flow at arbitrary points in time. `values` lists the payments (negative values) and receipts (positive values) with one value for each entry in `dates`.

#### Note

The optional `guess` is needed because there can be more than one valid result. It defaults to 10%.

#### See also

`IRR`.

XNPV

### `XNPV`

net present value

    XNPV(rate,values,dates)

#### Arguments

`rate`: effective annual interest rate

`values`: cash flow

`dates`: dates of cash flow

#### Description

`XNPV` calculates the net present value of a cash flow at irregular times

#### Note

If `type` is 0, the default, payment is at the end of each period. If `type` is 1, payment is at the beginning of each period.

#### See also

`NPV`.

YIELD

### `YIELD`

yield of a security

    YIELD(settlement,maturity,rate,price,redemption,frequency,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`rate`: nominal annual interest rate

`price`: price of security

`redemption`: amount received at maturity

`frequency`: number of interest payments per year

`basis`: calendar basis

#### Description

`YIELD` calculates the yield of a security that pays periodic interest.

#### Note

`frequency` may be 1 (annual), 2 (semi-annual), or 4 (quarterly). If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`PRICE`, `DURATION`.

YIELDDISC

### `YIELDDISC`

yield of a discounted security

    YIELDDISC(settlement,maturity,price,redemption,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`price`: price of security

`redemption`: amount received at maturity

`basis`: calendar basis

#### Description

`YIELDDISC` calculates the yield of a discounted security.

#### Note

If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`PRICE`, `DURATION`.

YIELDMAT

### `YIELDMAT`

yield of a security

    YIELDMAT(settlement,maturity,issue,rate,price,basis)

#### Arguments

`settlement`: settlement date

`maturity`: maturity date

`issue`: date of issue

`rate`: nominal annual interest rate

`price`: price of security

`basis`: calendar basis

#### Description

`YIELDMAT` calculates the yield of a security for which the interest is paid at maturity date.

#### Note

If `basis` is 0, then the US 30/360 method is used. If `basis` is 1, then actual number of days is used. If `basis` is 2, then actual number of days is used within a month, but years are considered only 360 days. If `basis` is 3, then actual number of days is used within a month, but years are always considered 365 days. If `basis` is 4, then the European 30/360 method is used.

#### See also

`YIELDDISC`, `YIELD`.

## Gnumeric

GNUMERIC_VERSION

### `GNUMERIC_VERSION`

the current version of Gnumeric

    GNUMERIC_VERSION()

#### Description

`GNUMERIC_VERSION` returns the version of gnumeric as a string.

## Information

CELL

### `CELL`

information of `type` about `cell`

    CELL(type,cell)

#### Arguments

`type`: string specifying the type of information requested

`cell`: cell reference

#### Description

`type` specifies the type of information you want to obtain:

address Returns the given cell reference as text.

col Returns the number of the column in `cell`.

color Returns 0.

contents Returns the contents of the cell in `cell`.

column Returns the number of the column in `cell`.

columnwidth Returns the column width.

coord Returns the absolute address of `cell`.

datatype same as type

filename Returns the name of the file of `cell`.

format Returns the code of the format of the cell.

formulatype same as type

locked Returns 1 if `cell` is locked.

parentheses Returns 1 if `cell` contains a negative value

and its format displays it with parentheses.

prefix Returns a character indicating the horizontal

alignment of `cell`.

prefixcharacter same as prefix

protect Returns 1 if `cell` is locked.

row Returns the number of the row in `cell`.

sheetname Returns the name of the sheet of `cell`.

type Returns "l" if `cell` contains a string,

"v" if it contains some other value, and

"b" if `cell` is blank.

value Returns the contents of the cell in `cell`.

width Returns the column width.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`INDIRECT`.

COUNTBLANK

### `COUNTBLANK`

the number of blank cells in `range`

    COUNTBLANK(range)

#### Arguments

`range`: a cell range

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COUNT`.

ERROR

### `ERROR`

the error with the given `name`

    ERROR(name)

#### Arguments

`name`: string

#### See also

`ISERROR`.

ERROR.TYPE

### `ERROR.TYPE`

the type of `error`

    ERROR.TYPE(error)

#### Arguments

`error`: an error

#### Description

`ERROR.TYPE` returns an error number corresponding to the given error value. The error numbers for error values are:

#DIV/0! 2

#VALUE! 3

#REF! 4

#NAME? 5

#NUM! 6

#N/A 7

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISERROR`.

EXPRESSION

### `EXPRESSION`

expression in `cell` as a string

    EXPRESSION(cell)

#### Arguments

`cell`: a cell reference

#### Note

If `cell` contains no expression, `EXPRESSION` returns empty.

#### See also

`TEXT`.

GET.FORMULA

### `GET.FORMULA`

the formula in `cell` as a string

    GET.FORMULA(cell)

#### Arguments

`cell`: the referenced cell

#### OpenDocument Format (ODF) Compatibility

`GET.FORMULA` is the OpenFormula function FORMULA.

#### See also

`EXPRESSION`, `ISFORMULA`.

GET.LINK

### `GET.LINK`

the target of the hyperlink attached to `cell` as a string

    GET.LINK(cell)

#### Arguments

`cell`: the referenced cell

#### Note

The value return is not updated automatically when the link attached to `cell` changes but requires a recalculation.

#### See also

`HYPERLINK`.

GETENV

### `GETENV`

the value of execution environment variable `name`

    GETENV(name)

#### Arguments

`name`: the name of the environment variable

#### Note

If a variable called `name` does not exist, #N/A! will be returned. Variable names are case sensitive.

INFO

### `INFO`

information about the current operating environment according to `type`

    INFO(type)

#### Arguments

`type`: string giving the type of information requested

#### Description

`INFO` returns information about the current operating environment according to `type`:

memavail Returns the amount of memory available, bytes.

memused Returns the amount of memory used (bytes).

numfile Returns the number of active worksheets.

osversion Returns the operating system version.

recalc Returns the recalculation mode (automatic).

release Returns the version of Gnumeric as text.

system Returns the name of the environment.

totmem Returns the amount of total memory available.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CELL`.

ISBLANK

### `ISBLANK`

TRUE if `value` is blank

    ISBLANK(value)

#### Arguments

`value`: a value

#### Description

This function checks if a value is blank. Empty cells are blank, but empty strings are not.

#### Microsoft Excel Compatibility

This function is Excel compatible.

ISERR

### `ISERR`

TRUE if `value` is any error value except #N/A

    ISERR(value)

#### Arguments

`value`: a value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISERROR`.

ISERROR

### `ISERROR`

TRUE if `value` is any error value

    ISERROR(value)

#### Arguments

`value`: a value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISERR`, `ISNA`.

ISEVEN

### `ISEVEN`

TRUE if `n` is even

    ISEVEN(n)

#### Arguments

`n`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISODD`.

ISFORMULA

### `ISFORMULA`

TRUE if `cell` contains a formula

    ISFORMULA(cell)

#### Arguments

`cell`: the referenced cell

#### OpenDocument Format (ODF) Compatibility

`ISFORMULA` is OpenFormula compatible.

#### See also

`GET.FORMULA`.

ISLOGICAL

### `ISLOGICAL`

TRUE if `value` is a logical value

    ISLOGICAL(value)

#### Arguments

`value`: a value

#### Description

This function checks if a value is either TRUE or FALSE.

#### Microsoft Excel Compatibility

This function is Excel compatible.

ISNA

### `ISNA`

TRUE if `value` is the #N/A error value

    ISNA(value)

#### Arguments

`value`: a value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`NA`.

ISNONTEXT

### `ISNONTEXT`

TRUE if `value` is not text

    ISNONTEXT(value)

#### Arguments

`value`: a value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISTEXT`.

ISNUMBER

### `ISNUMBER`

TRUE if `value` is a number

    ISNUMBER(value)

#### Arguments

`value`: a value

#### Description

This function checks if a value is a number. Neither TRUE nor FALSE are numbers for this purpose.

#### Microsoft Excel Compatibility

This function is Excel compatible.

ISODD

### `ISODD`

TRUE if `n` is odd

    ISODD(n)

#### Arguments

`n`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISEVEN`.

ISREF

### `ISREF`

TRUE if `value` is a reference

    ISREF(value,…)

#### Arguments

`value`: a value

#### Description

This function checks if a value is a cell reference.

#### Microsoft Excel Compatibility

This function is Excel compatible.

ISTEXT

### `ISTEXT`

TRUE if `value` is text

    ISTEXT(value)

#### Arguments

`value`: a value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISNONTEXT`.

N

### `N`

text
converted to a number

    N(text)

#### Arguments

`text`: string

#### Note

If `text` contains non-numerical text, 0 is returned.

#### Microsoft Excel Compatibility

This function is Excel compatible.

NA

### `NA`

the error value #N/A

    NA()

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ISNA`.

TYPE

### `TYPE`

a number indicating the data type of `value`

    TYPE(value)

#### Arguments

`value`: a value

#### Description

`TYPE` returns a number indicating the data type of `value`:

1 = number

2 = text

4 = boolean

16 = error

64 = array

#### Microsoft Excel Compatibility

This function is Excel compatible.

## Logic

AND

### `AND`

logical conjunction

    AND(b0,b1,…)

#### Arguments

`b0`: logical value

`b1`: logical value

#### Description

`AND` calculates the logical conjunction of its arguments `b0`,`b1`,...

#### Note

If an argument is numerical, zero is considered FALSE and anything else TRUE. Strings and empty values are ignored. If no logical values are provided, then the error #VALUE! is returned. This function is strict: if any argument is an error, the result will be the first such error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`OR`, `NOT`, `IF`.

FALSE

### `FALSE`

the value `FALSE`

    FALSE()

#### Description

`FALSE` returns the value `FALSE`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TRUE`, `IF`.

IF

### `IF`

conditional expression

    IF(cond,trueval,falseval)

#### Arguments

`cond`: condition

`trueval`: value to use if condition is true

`falseval`: value to use if condition is false

#### Description

This function first evaluates the condition. If the result is true, it will then evaluate and return the second argument. Otherwise, it will evaluate and return the last argument.

#### See also

`AND`, `OR`, `XOR`, `NOT`, `IFERROR`.

IFERROR

### `IFERROR`

test for error

    IFERROR(x,y)

#### Arguments

`x`: value to test for error

`y`: alternate value

#### Description

This function returns the first value, unless that is an error, in which case it returns the second.

#### See also

`IF`, `ISERROR`.

IFNA

### `IFNA`

test for #NA! error

    IFNA(x,y)

#### Arguments

`x`: value to test for #NA! error

`y`: alternate value

#### Description

This function returns the first value, unless that is #NA!, in which case it returns the second.

#### See also

`IF`, `ISERROR`.

NOT

### `NOT`

logical negation

    NOT(b)

#### Arguments

`b`: logical value

#### Description

`NOT` calculates the logical negation of its argument.

#### Note

If the argument is numerical, zero is considered FALSE and anything else TRUE. Strings and empty values are ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AND`, `OR`, `IF`.

OR

### `OR`

logical disjunction

    OR(b0,b1,…)

#### Arguments

`b0`: logical value

`b1`: logical value

#### Description

`OR` calculates the logical disjunction of its arguments `b0`,`b1`,...

#### Note

If an argument is numerical, zero is considered FALSE and anything else TRUE. Strings and empty values are ignored. If no logical values are provided, then the error #VALUE! is returned. This function is strict: if any argument is an error, the result will be the first such error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AND`, `XOR`, `NOT`, `IF`.

TRUE

### `TRUE`

the value `TRUE`

    TRUE()

#### Description

`TRUE` returns the value `TRUE`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FALSE`, `IF`.

XOR

### `XOR`

logical exclusive disjunction

    XOR(b0,b1,…)

#### Arguments

`b0`: logical value

`b1`: logical value

#### Description

`XOR` calculates the logical exclusive disjunction of its arguments `b0`,`b1`,...

#### Note

If an argument is numerical, zero is considered FALSE and anything else TRUE. Strings and empty values are ignored. If no logical values are provided, then the error #VALUE! is returned. This function is strict: if any argument is an error, the result will be the first such error.

#### See also

`OR`, `AND`, `NOT`, `IF`.

## Lookup

ADDRESS

### `ADDRESS`

cell address as text

    ADDRESS(row_num,col_num,abs_num,a1,text)

#### Arguments

`row_num`: row number

`col_num`: column number

`abs_num`: 1 for an absolute, 2 for a row absolute and column relative, 3 for a row relative and column absolute, and 4 for a relative reference; defaults to 1

`a1`: if TRUE, an A1-style reference is provided, otherwise an R1C1-style reference; defaults to TRUE

`text`: name of the worksheet, defaults to no sheet

#### Note

If `row_num` or `col_num` is less than one, `ADDRESS` returns #VALUE! If `abs_num` is greater than 4 `ADDRESS` returns #VALUE!

#### See also

`COLUMNNUMBER`.

AREAS

### `AREAS`

number of areas in `reference`

    AREAS(reference,…)

#### Arguments

`reference`: range

#### See also

`ADDRESS`, `INDEX`, `INDIRECT`, `OFFSET`.

ARRAY

### `ARRAY`

vertical array of the arguments

    ARRAY(v,…)

#### Arguments

`v`: value

#### See also

`TRANSPOSE`.

CHOOSE

### `CHOOSE`

the (`index`+1)th argument

    CHOOSE(index,value1,value2,…)

#### Arguments

`index`: positive number

`value1`: first value

`value2`: second value

#### Description

`CHOOSE` returns its (`index`+1)th argument.

#### Note

`index` is truncated to an integer. If `index` < 1 or the truncated `index` > number of values, `CHOOSE` returns #VALUE!

#### See also

`IF`.

COLUMN

### `COLUMN`

vector of column numbers

    COLUMN(x)

#### Arguments

`x`: reference, defaults to the position of the current expression

#### Description

`COLUMN` function returns a Nx1 array containing the sequence of integers from the first column to the last column of `x`.

#### Note

If `x` is neither an array nor a reference nor a range, returns #VALUE!

#### See also

`COLUMNS`, `ROW`, `ROWS`.

COLUMNNUMBER

### `COLUMNNUMBER`

column number for the given column called `name`

    COLUMNNUMBER(name)

#### Arguments

`name`: column name such as "IV"

#### Note

If `name` is invalid, `COLUMNNUMBER` returns #VALUE!

#### See also

`ADDRESS`.

COLUMNS

### `COLUMNS`

number of columns in `reference`

    COLUMNS(reference)

#### Arguments

`reference`: array or area

#### Note

If `reference` is neither an array nor a reference nor a range, `COLUMNS` returns #VALUE!

#### See also

`COLUMN`, `ROW`, `ROWS`.

FLIP

### `FLIP`

matrix
flipped

    FLIP(matrix,vertical)

#### Arguments

`matrix`: range

`vertical`: if true, `matrix` is flipped vertically, otherwise horizontally; defaults to TRUE

#### See also

`TRANSPOSE`.

HLOOKUP

### `HLOOKUP`

search the first row of `range` for `value`

    HLOOKUP(value,range,row,approximate,as_index)

#### Arguments

`value`: search value

`range`: range to search

`row`: 1-based row offset indicating the return values

`approximate`: if false, an exact match of `value` must be found; defaults to TRUE

`as_index`: if true, the 0-based column offset is returned; defaults to FALSE

#### Description

`HLOOKUP` function finds the row in `range` that has a first cell similar to `value`. If `approximate` is not true it finds the column with an exact equality. If `approximate` is true, it finds the last column with first value less than or equal to `value`. If `as_index` is true the 0-based column offset is returned.

#### Note

If `approximate` is true, then the values must be sorted in order of ascending value. `HLOOKUP` returns #REF! if `row` falls outside `range`.

#### See also

`VLOOKUP`.

HYPERLINK

### `HYPERLINK`

second or first arguments

    HYPERLINK(link_location,label)

#### Arguments

`link_location`: string

`label`: string, optional

#### Description

`HYPERLINK` function currently returns its 2nd argument, or if that is omitted the 1st argument.

INDEX

### `INDEX`

reference to a cell in the given `array`

    INDEX(array,row,col,area)

#### Arguments

`array`: cell or inline array

`row`: desired row, defaults to 1

`col`: desired column, defaults to 1

`area`: from which area to select a cell, defaults to 1

#### Description

`INDEX` gives a reference to a cell in the given `array`. The cell is selected by `row` and `col`, which count the rows and columns in the array.

#### Note

If the reference falls outside the range of `array`, `INDEX` returns #REF!

INDIRECT

### `INDIRECT`

contents of the cell pointed to by the `ref_text` string

    INDIRECT(ref_text,format)

#### Arguments

`ref_text`: textual reference

`format`: if true, `ref_text` is given in A1-style, otherwise it is given in R1C1 style; defaults to true

#### Note

If `ref_text` is not a valid reference in the style determined by `format`, `INDIRECT` returns #REF!

#### See also

`AREAS`, `INDEX`, `CELL`.

LOOKUP

### `LOOKUP`

contents of `vector2` at the corresponding location to `value` in `vector1`

    LOOKUP(value,vector1,vector2)

#### Arguments

`value`: value to look up

`vector1`: range to search:

`vector2`: range of return values

#### Description

If `vector1` has more rows than columns, `LOOKUP` searches the first row of `vector1`, otherwise the first column. If `vector2` is omitted the return value is taken from the last row or column of `vector1`.

#### Note

If `LOOKUP` can't find `value` it uses the largest value less than `value`. The data must be sorted. If `value` is smaller than the first value it returns #N/A. If the corresponding location does not exist in `vector2`, it returns #N/A.

#### See also

`VLOOKUP`, `HLOOKUP`.

MATCH

### `MATCH`

the index of `seek` in `vector`

    MATCH(seek,vector,type)

#### Arguments

`seek`: value to find

`vector`: n by 1 or 1 by n range to be searched

`type`: +1 (the default) to find the largest value ≤ `seek`, 0 to find the first value = `seek`, or-1 to find the smallest value ≥ `seek`

#### Description

`MATCH` searches `vector` for `seek` and returns the 1-based index.

#### Note

For `type` = -1 the data must be sorted in descending order; for `type` = +1 the data must be sorted in ascending order. If `seek` could not be found, #N/A is returned. If `vector` is neither n by 1 nor 1 by n, #N/A is returned.

#### See also

`LOOKUP`.

OFFSET

### `OFFSET`

an offset cell range

    OFFSET(range,row,col,height,width)

#### Arguments

`range`: reference or range

`row`: number of rows to offset `range`

`col`: number of columns to offset `range`

`height`: height of the offset range, defaults to height of `range`

`width`: width of the offset range, defaults to width of `range`

#### Description

`OFFSET` returns the cell range starting at offset (`row`,`col`) from `range` of height `height` and width `width`.

#### Note

If `range` is neither a reference nor a range, `OFFSET` returns #VALUE!

#### See also

`COLUMN`, `COLUMNS`, `ROWS`, `INDEX`, `INDIRECT`, `ADDRESS`.

ROW

### `ROW`

vector of row numbers

    ROW(x)

#### Arguments

`x`: reference, defaults to the position of the current expression

#### Description

`ROW` function returns a 1xN array containing the sequence of integers from the first row to the last row of `x`.

#### Note

If `x` is neither an array nor a reference nor a range, returns #VALUE!

#### See also

`COLUMN`, `COLUMNS`, `ROWS`.

ROWS

### `ROWS`

number of rows in `reference`

    ROWS(reference)

#### Arguments

`reference`: array, reference, or range

#### Note

If `reference` is neither an array nor a reference nor a range, `ROWS` returns #VALUE!

#### See also

`COLUMN`, `COLUMNS`, `ROW`.

SHEET

### `SHEET`

sheet number of `reference`

    SHEET(reference)

#### Arguments

`reference`: reference or literal sheet name, defaults to the current sheet

#### Note

If `reference` is neither a reference nor a literal sheet name, `SHEET` returns #VALUE!

#### See also

`SHEETS`, `ROW`, `COLUMNNUMBER`.

SHEETS

### `SHEETS`

number of sheets in `reference`

    SHEETS(reference)

#### Arguments

`reference`: array, reference, or range, defaults to the maximum range

#### Note

If `reference` is neither an array nor a reference nor a range, `SHEETS` returns #VALUE!

#### See also

`COLUMNS`, `ROWS`.

SORT

### `SORT`

sorted list of numbers as vertical array

    SORT(ref,order)

#### Arguments

`ref`: list of numbers

`order`: 0 (descending order) or 1 (ascending order); defaults to 0

#### Note

Strings, booleans, and empty cells are ignored.

#### See also

`ARRAY`.

TRANSPOSE

### `TRANSPOSE`

the transpose of `matrix`

    TRANSPOSE(matrix)

#### Arguments

`matrix`: range

#### See also

`FLIP`, `MMULT`.

VLOOKUP

### `VLOOKUP`

search the first column of `range` for `value`

    VLOOKUP(value,range,column,approximate,as_index)

#### Arguments

`value`: search value

`range`: range to search

`column`: 1-based column offset indicating the return values

`approximate`: if false, an exact match of `value` must be found; defaults to TRUE

`as_index`: if true, the 0-based row offset is returned; defaults to FALSE

#### Description

`VLOOKUP` function finds the row in `range` that has a first cell similar to `value`. If `approximate` is not true it finds the row with an exact equality. If `approximate` is true, it finds the last row with first value less than or equal to `value`. If `as_index` is true the 0-based row offset is returned.

#### Note

If `approximate` is true, then the values must be sorted in order of ascending value. `VLOOKUP` returns #REF! if `column` falls outside `range`.

#### See also

`HLOOKUP`.

## Mathematics

ABS

### `ABS`

absolute value

    ABS(x)

#### Arguments

`x`: number

#### Description

`ABS` gives the absolute value of `x`, i.e. the non-negative number of the same magnitude as `x`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CEIL`, `CEILING`, `FLOOR`, `INT`, `MOD`.

ACOS

### `ACOS`

the arc cosine of `x`

    ACOS(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COS`, `SIN`, `DEGREES`, `RADIANS`.

ACOSH

### `ACOSH`

the hyperbolic arc cosine of `x`

    ACOSH(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ACOS`, `ASINH`.

ACOT

### `ACOT`

inverse cotangent of `x`

    ACOT(x)

#### Arguments

`x`: value

#### See also

`COT`, `TAN`.

ACOTH

### `ACOTH`

the inverse hyperbolic cotangent of `x`

    ACOTH(x)

#### Arguments

`x`: number

#### See also

`COTH`, `TANH`.

ARABIC

### `ARABIC`

the Roman numeral `roman` as number

    ARABIC(roman)

#### Arguments

`roman`: Roman numeral

#### Description

Any Roman symbol to the left of a larger symbol (directly or indirectly) reduces the final value by the symbol amount, otherwise, it increases the final amount by the symbol's amount.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`ROMAN`.

ASIN

### `ASIN`

the arc sine of `x`

    ASIN(x)

#### Arguments

`x`: number

#### Description

`ASIN` calculates the arc sine of `x`; that is the value whose sine is `x`.

#### Note

If `x` falls outside the range -1 to 1, `ASIN` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SIN`, `COS`, `ASINH`, `DEGREES`, `RADIANS`.

ASINH

### `ASINH`

the inverse hyperbolic sine of `x`

    ASINH(x)

#### Arguments

`x`: number

#### Description

`ASINH` calculates the inverse hyperbolic sine of `x`; that is the value whose hyperbolic sine is `x`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ASIN`, `ACOSH`, `SIN`, `COS`.

ATAN

### `ATAN`

the arc tangent of `x`

    ATAN(x)

#### Arguments

`x`: number

#### Description

`ATAN` calculates the arc tangent of `x`; that is the value whose tangent is `x`.

#### Note

The result will be between −π/2 and +π/2.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TAN`, `COS`, `SIN`, `DEGREES`, `RADIANS`.

ATAN2

### `ATAN2`

the arc tangent of the ratio `y`/`x`

    ATAN2(x,y)

#### Arguments

`x`: x-coordinate

`y`: y-coordinate

#### Description

`ATAN2` calculates the direction from the origin to the point (`x`,`y`) as an angle from the x-axis in radians.

#### Note

The result will be between −π and +π. The order of the arguments may be unexpected.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`ATAN`, `ATANH`, `COS`, `SIN`.

ATANH

### `ATANH`

the inverse hyperbolic tangent of `x`

    ATANH(x)

#### Arguments

`x`: number

#### Description

`ATANH` calculates the inverse hyperbolic tangent of `x`; that is the value whose hyperbolic tangent is `x`.

#### Note

If the absolute value of `x` is greater than 1.0, `ATANH` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ATAN`, `COS`, `SIN`.

AVERAGEIF

### `AVERAGEIF`

average of the cells in `actual range` for which the corresponding cells in the range meet the given `criteria`

    AVERAGEIF(range,criteria,actual_range)

#### Arguments

`range`: cell area

`criteria`: condition for a cell to be included

`actual_range`: cell area, defaults to `range`

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SUMIF`, `COUNTIF`.

BETA

### `BETA`

Euler beta function

    BETA(x,y)

#### Arguments

`x`: number

`y`: number

#### Description

`BETA` function returns the value of the Euler beta function extended to all real numbers except 0 and negative integers.

#### Note

If `x`, `y`, or (`x` + `y`) are non-positive integers, `BETA` returns #NUM!

#### See also

`BETALN`, `GAMMALN`.

BETALN

### `BETALN`

natural logarithm of the absolute value of the Euler beta function

    BETALN(x,y)

#### Arguments

`x`: number

`y`: number

#### Description

`BETALN` function returns the natural logarithm of the absolute value of the Euler beta function extended to all real numbers except 0 and negative integers.

#### Note

If `x`, `y`, or (`x` + `y`) are non-positive integers, `BETALN` returns #NUM!

#### See also

`BETA`, `GAMMALN`.

CEIL

### `CEIL`

smallest integer larger than or equal to `x`

    CEIL(x)

#### Arguments

`x`: number

#### Description

`CEIL`(`x`) is the smallest integer that is at least as large as `x`.

#### OpenDocument Format (ODF) Compatibility

This function is the OpenFormula function CEILING(`x`).

#### See also

`CEILING`, `FLOOR`, `ABS`, `INT`, `MOD`.

CEILING

### `CEILING`

nearest multiple of `significance` whose absolute value is at least ABS(`x`)

    CEILING(x,significance)

#### Arguments

`x`: number

`significance`: base multiple (defaults to 1 for `x` > 0 and -1 for `x` <0)

#### Description

`CEILING`(`x`,`significance`) is the nearest multiple of `significance` whose absolute value is at least ABS(`x`).

#### Note

If `x` or `significance` is non-numeric, `CEILING` returns a #VALUE! error. If `x` and `significance` have different signs, `CEILING` returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`CEILING`(`x`) is exported to ODF as `CEILING`(`x`,SIGN(`x`),1). `CEILING`(`x`,`significance`) is the OpenFormula function `CEILING`(`x`,`significance`,1).

#### See also

`CEIL`, `FLOOR`, `ABS`, `INT`, `MOD`.

CHOLESKY

### `CHOLESKY`

the Cholesky decomposition of the symmetric positive-definite `matrix`

    CHOLESKY(matrix)

#### Arguments

`matrix`: a symmetric positive definite matrix

#### Note

If the Cholesky-Banachiewicz algorithm applied to `matrix` fails, Cholesky returns #NUM! If `matrix` does not contain an equal number of columns and rows, `CHOLESKY` returns #VALUE!

#### See also

`MINVERSE`, `MMULT`, `MDETERM`.

COMBIN

### `COMBIN`

binomial coefficient

    COMBIN(n,k)

#### Arguments

`n`: non-negative integer

`k`: non-negative integer

#### Description

`COMBIN` returns the binomial coefficient "`n` choose `k`", the number of `k`-combinations of an `n`-element set without repetition.

#### Note

If `n` is less than `k` `COMBIN` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

COMBINA

### `COMBINA`

the number of `k`-combinations of an `n`-element set with repetition

    COMBINA(n,k)

#### Arguments

`n`: non-negative integer

`k`: non-negative integer

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`COMBIN`.

COS

### `COS`

the cosine of `x`

    COS(x)

#### Arguments

`x`: angle in radians

#### Description

This function is Excel compatible.

#### See also

`SIN`, `TAN`, `SINH`, `COSH`, `TANH`, `RADIANS`, `DEGREES`.

COSH

### `COSH`

the hyperbolic cosine of `x`

    COSH(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SIN`, `TAN`, `SINH`, `COSH`, `TANH`.

COT

### `COT`

the cotangent of `x`

    COT(x)

#### Arguments

`x`: number

#### See also

`TAN`, `ACOT`.

COTH

### `COTH`

the hyperbolic cotangent of `x`

    COTH(x)

#### Arguments

`x`: number

#### See also

`TANH`, `ACOTH`.

COUNTIF

### `COUNTIF`

count of the cells meeting the given `criteria`

    COUNTIF(range,criteria)

#### Arguments

`range`: cell area

`criteria`: condition for a cell to be counted

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COUNT`, `SUMIF`.

CSC

### `CSC`

the cosecant of `x`

    CSC(x)

#### Arguments

`x`: angle in radians

#### Microsoft Excel Compatibility

This function is not Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`SIN`, `COS`, `TAN`, `SEC`, `SINH`, `COSH`, `TANH`, `RADIANS`, `DEGREES`.

CSCH

### `CSCH`

the hyperbolic cosecant of `x`

    CSCH(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is not Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`SIN`, `COS`, `TAN`, `CSC`, `SEC`, `SINH`, `COSH`, `TANH`.

DEGREES

### `DEGREES`

equivalent degrees to `x` radians

    DEGREES(x)

#### Arguments

`x`: angle in radians

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`RADIANS`, `PI`.

EIGEN

### `EIGEN`

eigenvalues and eigenvectors of the symmetric `matrix`

    EIGEN(matrix)

#### Arguments

`matrix`: a symmetric matrix

#### Note

If `matrix` is not symmetric, `EIGEN` returns #NUM! If `matrix` does not contain an equal number of columns and rows, `EIGEN` returns #VALUE!

EVEN

### `EVEN`

x
rounded away from 0 to the next even integer

    EVEN(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ODD`.

EXP

### `EXP`

e raised to the power of `x`

    EXP(x)

#### Arguments

`x`: number

#### Note

e is the base of the natural logarithm.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LOG`, `LOG2`, `LOG10`.

EXPM1

### `EXPM1`

EXP(`x`)-1

    EXPM1(x)

#### Arguments

`x`: number

#### Note

This function has a higher resulting precision than evaluating EXP(`x`)-1.

#### See also

`EXP`, `LN1P`.

FACT

### `FACT`

the factorial of `x`, i.e. `x`!

    FACT(x)

#### Arguments

`x`: number

#### Note

The domain of this function has been extended using the GAMMA function.

#### Microsoft Excel Compatibility

This function is Excel compatible.

FACTDOUBLE

### `FACTDOUBLE`

double factorial

    FACTDOUBLE(x)

#### Arguments

`x`: non-negative integer

#### Description

`FACTDOUBLE` function returns the double factorial `x`!!

#### Note

If `x` is not an integer, it is truncated. If `x` is negative, `FACTDOUBLE` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FACT`.

FIB

### `FIB`

Fibonacci numbers

    FIB(n)

#### Arguments

`n`: positive integer

#### Description

`FIB`(`n`) is the `n`th Fibonacci number.

#### Note

If `n` is not an integer, it is truncated. If it is negative or zero `FIB` returns #NUM!

FLOOR

### `FLOOR`

nearest multiple of `significance` whose absolute value is at most ABS(`x`)

    FLOOR(x,significance)

#### Arguments

`x`: number

`significance`: base multiple (defaults to 1 for `x` > 0 and -1 for `x` <0)

#### Description

`FLOOR`(`x`,`significance`) is the nearest multiple of `significance` whose absolute value is at most ABS(`x`)

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`FLOOR`(`x`) is exported to ODF as `FLOOR`(`x`,SIGN(`x`),1). `FLOOR`(`x`,`significance`) is the OpenFormula function `FLOOR`(`x`,`significance`,1).

#### See also

`CEIL`, `CEILING`, `ABS`, `INT`, `MOD`.

G_PRODUCT

### `G_PRODUCT`

product of all the values and cells referenced

    G_PRODUCT(x1,x2,…)

#### Arguments

`x1`: number

`x2`: number

#### Note

Empty cells are ignored and the empty product is 1.

#### See also

`SUM`, `COUNT`.

GAMMA

### `GAMMA`

the Gamma function

    GAMMA(x)

#### Arguments

`x`: number

#### See also

`GAMMALN`.

GAMMALN

### `GAMMALN`

natural logarithm of the Gamma function

    GAMMALN(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`GAMMA`.

GCD

### `GCD`

the greatest common divisor

    GCD(n0,n1,…)

#### Arguments

`n0`: positive integer

`n1`: positive integer

#### Description

`GCD` calculates the greatest common divisor of the given numbers `n0`,`n1`,..., the greatest integer that is a divisor of each argument.

#### Note

If any of the arguments is not an integer, it is truncated.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LCM`.

GD

### `GD`

Gudermannian function

    GD(x)

#### Arguments

`x`: value

#### See also

`TAN`, `TANH`.

HYPOT

### `HYPOT`

the square root of the sum of the squares of the arguments

    HYPOT(n0,n1,…)

#### Arguments

`n0`: number

`n1`: number

#### See also

`MIN`, `MAX`.

INT

### `INT`

largest integer not larger than `x`

    INT(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CEIL`, `CEILING`, `FLOOR`, `ABS`, `MOD`.

LCM

### `LCM`

the least common multiple

    LCM(n0,n1,…)

#### Arguments

`n0`: positive integer

`n1`: positive integer

#### Description

`LCM` calculates the least common multiple of the given numbers `n0`,`n1`,..., the smallest integer that is a multiple of each argument.

#### Note

If any of the arguments is not an integer, it is truncated.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`GCD`.

LN

### `LN`

the natural logarithm of `x`

    LN(x)

#### Arguments

`x`: positive number

#### Note

If `x` ≤ 0, `LN` returns #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`EXP`, `LOG2`, `LOG10`.

LN1P

### `LN1P`

LN(1+`x`)

    LN1P(x)

#### Arguments

`x`: positive number

#### Description

`LN1P` calculates LN(1+`x`) but yielding a higher precision than evaluating LN(1+`x`).

#### Note

If `x` ≤ -1, LN returns #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`EXP`, `LN`, `EXPM1`.

LOG

### `LOG`

logarithm of `x` with base `base`

    LOG(x,base)

#### Arguments

`x`: positive number

`base`: base of the logarithm, defaults to 10

#### Note

`base` must be positive and not equal to 1. If `x` ≤ 0, `LOG` returns #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LN`, `LOG2`, `LOG10`.

LOG10

### `LOG10`

the base-10 logarithm of `x`

    LOG10(x)

#### Arguments

`x`: positive number

#### Note

If `x` ≤ 0, `LOG10` returns #NUM!

#### See also

`EXP`, `LOG2`, `LOG`.

LOG2

### `LOG2`

the base-2 logarithm of `x`

    LOG2(x)

#### Arguments

`x`: positive number

#### Note

If `x` ≤ 0, `LOG2` returns #NUM!

#### See also

`EXP`, `LOG10`, `LOG`.

MDETERM

### `MDETERM`

the determinant of the matrix `matrix`

    MDETERM(matrix)

#### Arguments

`matrix`: a square matrix

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MMULT`, `MINVERSE`.

MINVERSE

### `MINVERSE`

the inverse matrix of `matrix`

    MINVERSE(matrix)

#### Arguments

`matrix`: a square matrix

#### Note

If `matrix` is not invertible, `MINVERSE` returns #NUM! If `matrix` does not contain an equal number of columns and rows, `MINVERSE` returns #VALUE!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MMULT`, `MDETERM`.

MMULT

### `MMULT`

the matrix product of `mat1` and `mat2`

    MMULT(mat1,mat2)

#### Arguments

`mat1`: a matrix

`mat2`: a matrix

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TRANSPOSE`, `MINVERSE`.

MOD

### `MOD`

the remainder of `x` under division by `n`

    MOD(x,n)

#### Arguments

`x`: integer

`n`: integer

#### Description

`MOD` function returns the remainder when `x` is divided by `n`.

#### Note

If `n` is 0, `MOD` returns #DIV/0!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CEIL`, `CEILING`, `FLOOR`, `ABS`, `INT`, `ABS`.

MROUND

### `MROUND`

x
rounded to a multiple of `m`

    MROUND(x,m)

#### Arguments

`x`: number

`m`: number

#### Note

If `x` and `m` have different sign, `MROUND` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ROUNDDOWN`, `ROUND`, `ROUNDUP`.

MULTINOMIAL

### `MULTINOMIAL`

multinomial coefficient (`x1`+⋯+`xn`) choose (`x1`,…,`xn`)

    MULTINOMIAL(x1,x2,xn,…)

#### Arguments

`x1`: first number

`x2`: second number

`xn`: nth number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COMBIN`, `SUM`.

MUNIT

### `MUNIT`

the `n` by `n` identity matrix

    MUNIT(n)

#### Arguments

`n`: size of the matrix

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`MMULT`, `MDETERM`, `MINVERSE`.

ODD

### `ODD`

x
rounded away from 0 to the next odd integer

    ODD(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`EVEN`.

ODF.SUMPRODUCT

### `ODF.SUMPRODUCT`

multiplies components and adds the results

    ODF.SUMPRODUCT(,…)

#### Description

Multiplies corresponding data entries in the given arrays or ranges, and then returns the sum of those products.

#### Note

If an entry is not numeric or logical, the value zero is used instead. If arrays or range arguments do not have the same dimensions, return #VALUE! error. This function differs from SUMPRODUCT by considering booleans.

#### Microsoft Excel Compatibility

This function is not Excel compatible. Use SUMPRODUCT instead.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`SUMPRODUCT`, `SUM`, `PRODUCT`, `G_PRODUCT`.

PI

### `PI`

the constant 𝜋

    PI()

#### Microsoft Excel Compatibility

This function is Excel compatible, but it returns 𝜋 with a better precision.

#### See also

`SQRTPI`.

POWER

### `POWER`

the value of `x` raised to the power `y` raised to the power of 1/`z`

    POWER(x,y,z)

#### Arguments

`x`: number

`y`: number

`z`: number

#### Note

If both `x` and `y` equal 0, `POWER` returns #NUM! If `x` = 0 and `y` < 0, `POWER` returns #DIV/0! If `x` < 0 and `y` is not an integer, `POWER` returns #NUM! `z` defaults to 1 If `z` is not a positive integer, `POWER` returns #NUM! If `x` < 0, `y` is odd, and `z` is even, `POWER` returns #NUM!

#### See also

`EXP`.

PRODUCT

### `PRODUCT`

product of the given values

    PRODUCT(values,…)

#### Arguments

`values`: a list of values to multiply

#### Description

`PRODUCT` computes the product of all the values and cells referenced in the argument list.

#### Note

If all cells are empty, the result will be 0.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`SUM`, `COUNT`, `G_PRODUCT`.

QUOTIENT

### `QUOTIENT`

integer portion of a division

    QUOTIENT(numerator,denominator)

#### Arguments

`numerator`: integer

`denominator`: non-zero integer

#### Description

`QUOTIENT` yields the integer portion of the division `numerator`/`denominator`.

`QUOTIENT` (`numerator`,`denominator`)⨉`denominator`+MOD(`numerator`,`denominator`)=`numerator`

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MOD`.

RADIANS

### `RADIANS`

the number of radians equivalent to `x` degrees

    RADIANS(x)

#### Arguments

`x`: angle in degrees

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`PI`, `DEGREES`.

ROMAN

### `ROMAN`

n
as a roman numeral text

    ROMAN(n,type)

#### Arguments

`n`: non-negative integer

`type`: 0,1,2,3,or 4, defaults to 0

#### Description

`ROMAN` returns the arabic number `n` as a roman numeral text.

If `type` is 0 or it is omitted, `ROMAN` returns classic roman numbers.

Type 1 is more concise than classic type, type 2 is more concise than type 1, and type 3 is more concise than type 2. Type 4 is a simplified type.

#### Microsoft Excel Compatibility

This function is Excel compatible.

ROUND

### `ROUND`

rounded `x`

    ROUND(x,d)

#### Arguments

`x`: number

`d`: integer, defaults to 0

#### Description

If `d` is greater than zero, `x` is rounded to the given number of digits.

If `d` is zero, `x` is rounded to the next integer.

If `d` is less than zero, `x` is rounded to the left of the decimal point

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ROUNDDOWN`, `ROUNDUP`.

ROUNDDOWN

### `ROUNDDOWN`

x
rounded towards 0

    ROUNDDOWN(x,d)

#### Arguments

`x`: number

`d`: integer, defaults to 0

#### Description

If `d` is greater than zero, `x` is rounded toward 0 to the given number of digits.

If `d` is zero, `x` is rounded toward 0 to the next integer.

If `d` is less than zero, `x` is rounded toward 0 to the left of the decimal point

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ROUND`, `ROUNDUP`.

ROUNDUP

### `ROUNDUP`

x
rounded away from 0

    ROUNDUP(x,d)

#### Arguments

`x`: number

`d`: integer, defaults to 0

#### Description

If `d` is greater than zero, `x` is rounded away from 0 to the given number of digits.

If `d` is zero, `x` is rounded away from 0 to the next integer.

If `d` is less than zero, `x` is rounded away from 0 to the left of the decimal point

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ROUND`, `ROUNDDOWN`, `INT`.

SEC

### `SEC`

Secant

    SEC(x)

#### Arguments

`x`: angle in radians

#### Microsoft Excel Compatibility

This function is not Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`SEC`(`x`) is exported to OpenFormula as 1/COS(`x`).

#### See also

`SIN`, `COS`, `TAN`, `CSC`, `SINH`, `COSH`, `TANH`, `RADIANS`, `DEGREES`.

SECH

### `SECH`

the hyperbolic secant of `x`

    SECH(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is not Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`SECH`(`x`) is exported to OpenFormula as 1/COSH(`x`).

#### See also

`SIN`, `COS`, `TAN`, `CSC`, `SEC`, `SINH`, `COSH`, `TANH`.

SERIESSUM

### `SERIESSUM`

sum of a power series at `x`

    SERIESSUM(x,n,m,coeff)

#### Arguments

`x`: number where to evaluate the power series

`n`: non-negative integer, exponent of the lowest term of the series

`m`: increment to each exponent

`coeff`: coefficients of the power series

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COUNT`, `SUM`.

SIGN

### `SIGN`

sign of `x`

    SIGN(x)

#### Arguments

`x`: number

#### Description

`SIGN` returns 1 if the `x` is positive and it returns -1 if `x` is negative.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`ABS`.

SIN

### `SIN`

the sine of `x`

    SIN(x)

#### Arguments

`x`: angle in radians

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COS`, `TAN`, `CSC`, `SEC`, `SINH`, `COSH`, `TANH`, `RADIANS`, `DEGREES`.

SINH

### `SINH`

the hyperbolic sine of `x`

    SINH(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SIN`, `COSH`, `ASINH`.

SQRT

### `SQRT`

square root of `x`

    SQRT(x)

#### Arguments

`x`: non-negative number

#### Note

If `x` is negative, `SQRT` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`POWER`.

SQRTPI

### `SQRTPI`

the square root of `x` times 𝜋

    SQRTPI(x)

#### Arguments

`x`: non-negative number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`PI`.

SUM

### `SUM`

sum of the given values

    SUM(values,…)

#### Arguments

`values`: a list of values to add

#### Description

`SUM` computes the sum of all the values and cells referenced in the argument list.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`AVERAGE`, `COUNT`.

SUMA

### `SUMA`

sum of all values and cells referenced

    SUMA(area0,area1,…)

#### Arguments

`area0`: first cell area

`area1`: second cell area

#### Description

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1).

#### See also

`AVERAGE`, `SUM`, `COUNT`.

SUMIF

### `SUMIF`

sum of the cells in `actual_range` for which the corresponding cells in the range meet the given `criteria`

    SUMIF(range,criteria,actual_range)

#### Arguments

`range`: cell area

`criteria`: condition for a cell to be summed

`actual_range`: cell area, defaults to `range`

#### Note

If the `actual_range` has a size that differs from the size of `range`, `actual_range` is resized (retaining the top-left corner) to match the size of `range`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SUM`, `COUNTIF`.

SUMPRODUCT

### `SUMPRODUCT`

multiplies components and adds the results

    SUMPRODUCT(,…)

#### Description

Multiplies corresponding data entries in the given arrays or ranges, and then returns the sum of those products.

#### Note

If an entry is not numeric, the value zero is used instead. If arrays or range arguments do not have the same dimensions, return #VALUE! error. This function ignores logicals, so using `SUMPRODUCT`(A1:A5>0) will not work. Instead use `SUMPRODUCT`(--(A1:A5>0))

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is not OpenFormula compatible. Use ODF.`SUMPRODUCT` instead.

#### See also

`SUM`, `PRODUCT`, `G_PRODUCT`, `ODF.SUMPRODUCT`.

SUMSQ

### `SUMSQ`

sum of the squares of all values and cells referenced

    SUMSQ(area0,area1,…)

#### Arguments

`area0`: first cell area

`area1`: second cell area

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SUM`, `COUNT`.

SUMX2MY2

### `SUMX2MY2`

sum of the difference of squares

    SUMX2MY2(array0,array1)

#### Arguments

`array0`: first cell area

`array1`: second cell area

#### Description

`SUMX2MY2` function returns the sum of the difference of squares of corresponding values in two arrays. The equation of `SUMX2MY2` is SUM(x^2-y^2).

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SUMSQ`, `SUMX2PY2`.

SUMX2PY2

### `SUMX2PY2`

sum of the sum of squares

    SUMX2PY2(array0,array1)

#### Arguments

`array0`: first cell area

`array1`: second cell area

#### Description

`SUMX2PY2` function returns the sum of the sum of squares of corresponding values in two arrays. The equation of `SUMX2PY2` is SUM(x^2+y^2).

#### Note

If `array0` and `array1` have different number of data points, `SUMX2PY2` returns #N/A.

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SUMSQ`, `SUMX2MY2`.

SUMXMY2

### `SUMXMY2`

sum of the squares of differences

    SUMXMY2(array0,array1)

#### Arguments

`array0`: first cell area

`array1`: second cell area

#### Description

`SUMXMY2` function returns the sum of the squares of the differences of corresponding values in two arrays. The equation of `SUMXMY2` is SUM((x-y)^2).

#### Note

If `array0` and `array1` have different number of data points, `SUMXMY2` returns #N/A.

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SUMSQ`, `SUMX2MY2`, `SUMX2PY2`.

TAN

### `TAN`

the tangent of `x`

    TAN(x)

#### Arguments

`x`: angle in radians

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TANH`, `COS`, `COSH`, `SIN`, `SINH`, `DEGREES`, `RADIANS`.

TANH

### `TANH`

the hyperbolic tangent of `x`

    TANH(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TAN`, `SIN`, `SINH`, `COS`, `COSH`.

TRUNC

### `TRUNC`

x
truncated to `d` digits

    TRUNC(x,d)

#### Arguments

`x`: number

`d`: non-negative integer, defaults to 0

#### Note

If `d` is omitted or negative then it defaults to zero. If it is not an integer then it is truncated to an integer.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`INT`.

## Number Theory

ISPRIME

### `ISPRIME`

whether `n` is prime

    ISPRIME(n)

#### Arguments

`n`: positive integer

#### Description

`ISPRIME` returns TRUE if `n` is prime and FALSE otherwise.

#### See also

`NT_D`, `NT_SIGMA`.

ITHPRIME

### `ITHPRIME`

i
th prime

    ITHPRIME(i)

#### Arguments

`i`: positive integer

#### Description

`ITHPRIME` finds the `i`th prime.

#### See also

`NT_D`, `NT_SIGMA`.

NT_D

### `NT_D`

number of divisors

    NT_D(n)

#### Arguments

`n`: positive integer

#### Description

`NT_D` calculates the number of divisors of `n`.

#### See also

`ITHPRIME`, `NT_PHI`, `NT_SIGMA`.

NT_MU

### `NT_MU`

Möbius mu function

    NT_MU(n)

#### Arguments

`n`: positive integer

#### Description

`NT_MU` function (Möbius mu function) returns 0 if `n` is divisible by the square of a prime. Otherwise, if `n` has an odd number of different prime factors, `NT_MU` returns -1, and if `n` has an even number of different prime factors, it returns 1. If `n` = 1, `NT_MU` returns 1.

#### See also

`ITHPRIME`, `NT_PHI`, `NT_SIGMA`, `NT_D`.

NT_PHI

### `NT_PHI`

Euler's totient function

    NT_PHI(n)

#### Arguments

`n`: positive integer

#### Note

Euler's totient function gives the number of integers less than or equal to `n` that are relatively prime (coprime) to `n`.

#### See also

`NT_D`, `ITHPRIME`, `NT_SIGMA`.

NT_PI

### `NT_PI`

number of primes upto `n`

    NT_PI(n)

#### Arguments

`n`: positive integer

#### Description

`NT_PI` returns the number of primes less than or equal to `n`.

#### See also

`ITHPRIME`, `NT_PHI`, `NT_D`, `NT_SIGMA`.

NT_SIGMA

### `NT_SIGMA`

sigma function

    NT_SIGMA(n)

#### Arguments

`n`: positive integer

#### Description

`NT_SIGMA` calculates the sum of the divisors of `n`.

#### See also

`NT_D`, `ITHPRIME`, `NT_PHI`.

PFACTOR

### `PFACTOR`

smallest prime factor

    PFACTOR(n)

#### Arguments

`n`: positive integer

#### Description

`PFACTOR` finds the smallest prime factor of its argument.

#### Note

The argument `n` must be at least 2. Otherwise a #VALUE! error is returned.

#### See also

`ITHPRIME`.

## Random Numbers

RAND

### `RAND`

a random number between zero and one

    RAND()

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`RANDBETWEEN`.

RANDBERNOULLI

### `RANDBERNOULLI`

random variate from a Bernoulli distribution

    RANDBERNOULLI(p)

#### Arguments

`p`: probability of success

#### Note

If `p` < 0 or `p` > 1 `RANDBERNOULLI` returns #NUM!

#### See also

`RAND`, `RANDBETWEEN`.

RANDBETA

### `RANDBETA`

random variate from a Beta distribution

    RANDBETA(a,b)

#### Arguments

`a`: parameter of the Beta distribution

`b`: parameter of the Beta distribution

#### See also

`RAND`, `RANDGAMMA`.

RANDBETWEEN

### `RANDBETWEEN`

a random integer number between and including `bottom` and `top`

    RANDBETWEEN(bottom,top)

#### Arguments

`bottom`: lower limit

`top`: upper limit

#### Note

If `bottom` > `top`, `RANDBETWEEN` returns #NUM!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`RAND`, `RANDUNIFORM`.

RANDBINOM

### `RANDBINOM`

random variate from a binomial distribution

    RANDBINOM(p,n)

#### Arguments

`p`: probability of success in a single trial

`n`: number of trials

#### Note

If `p` < 0 or `p` > 1 `RANDBINOM` returns #NUM! If `n` < 0 `RANDBINOM` returns #NUM!

#### See also

`RAND`, `RANDBETWEEN`.

RANDCAUCHY

### `RANDCAUCHY`

random variate from a Cauchy or Lorentz distribution

    RANDCAUCHY(a)

#### Arguments

`a`: scale parameter of the distribution

#### Note

If `a` < 0 `RANDCAUCHY` returns #NUM!

#### See also

`RAND`.

RANDCHISQ

### `RANDCHISQ`

random variate from a Chi-square distribution

    RANDCHISQ(df)

#### Arguments

`df`: degrees of freedom

#### See also

`RAND`, `RANDGAMMA`.

RANDDISCRETE

### `RANDDISCRETE`

random variate from a finite discrete distribution

    RANDDISCRETE(val_range,prob_range)

#### Arguments

`val_range`: possible values of the random variable

`prob_range`: probabilities of the corresponding values in `val_range`, defaults to equal probabilities

#### Description

`RANDDISCRETE` returns one of the values in the `val_range`. The probabilities for each value are given in the `prob_range`.

#### Note

If the sum of all values in `prob_range` is not one, `RANDDISCRETE` returns #NUM! If `val_range` and `prob_range` are not the same size, `RANDDISCRETE` returns #NUM! If `val_range` or `prob_range` is not a range, `RANDDISCRETE` returns #VALUE!

#### See also

`RANDBETWEEN`, `RAND`.

RANDEXP

### `RANDEXP`

random variate from an exponential distribution

    RANDEXP(b)

#### Arguments

`b`: parameter of the exponential distribution

#### See also

`RAND`, `RANDBETWEEN`.

RANDEXPPOW

### `RANDEXPPOW`

random variate from an exponential power distribution

    RANDEXPPOW(a,b)

#### Arguments

`a`: scale parameter of the exponential power distribution

`b`: exponent of the exponential power distribution

#### Description

For `b` = 1 the exponential power distribution reduces to the Laplace distribution.

For `b` = 2 the exponential power distribution reduces to the normal distribution with σ = a/sqrt(2)

#### See also

`RAND`.

RANDFDIST

### `RANDFDIST`

random variate from an F distribution

    RANDFDIST(df1,df2)

#### Arguments

`df1`: numerator degrees of freedom

`df2`: denominator degrees of freedom

#### See also

`RAND`, `RANDGAMMA`.

RANDGAMMA

### `RANDGAMMA`

random variate from a Gamma distribution

    RANDGAMMA(a,b)

#### Arguments

`a`: parameter of the Gamma distribution

`b`: parameter of the Gamma distribution

#### Note

If `a` ≤ 0, `RANDGAMMA` returns #NUM!

#### See also

`RAND`.

RANDGEOM

### `RANDGEOM`

random variate from a geometric distribution

    RANDGEOM(p)

#### Arguments

`p`: probability of success in a single trial

#### Note

If `p` < 0 or `p` > 1 `RANDGEOM` returns #NUM!

#### See also

`RAND`.

RANDGUMBEL

### `RANDGUMBEL`

random variate from a Gumbel distribution

    RANDGUMBEL(a,b,type)

#### Arguments

`a`: parameter of the Gumbel distribution

`b`: parameter of the Gumbel distribution

`type`: type of the Gumbel distribution, defaults to 1

#### Note

If `type` is neither 1 nor 2, `RANDGUMBEL` returns #NUM!

#### See also

`RAND`.

RANDHYPERG

### `RANDHYPERG`

random variate from a hypergeometric distribution

    RANDHYPERG(n1,n2,t)

#### Arguments

`n1`: number of objects of type 1

`n2`: number of objects of type 2

`t`: total number of objects selected

#### See also

`RAND`.

RANDLANDAU

### `RANDLANDAU`

random variate from the Landau distribution

    RANDLANDAU()

#### See also

`RAND`.

RANDLAPLACE

### `RANDLAPLACE`

random variate from a Laplace distribution

    RANDLAPLACE(a)

#### Arguments

`a`: parameter of the Laplace distribution

#### See also

`RAND`.

RANDLEVY

### `RANDLEVY`

random variate from a Lévy distribution

    RANDLEVY(c,α,β)

#### Arguments

`c`: parameter of the Lévy distribution

`α`: parameter of the Lévy distribution

`β`: parameter of the Lévy distribution, defaults to 0

#### Description

For `α` = 1, `β`=0, the Lévy distribution reduces to the Cauchy (or Lorentzian) distribution.

For `α` = 2, `β`=0, the Lévy distribution reduces to the normal distribution.

#### Note

If `α` ≤ 0 or `α` > 2, `RANDLEVY` returns #NUM! If `β` < -1 or `β` > 1, `RANDLEVY` returns #NUM!

#### See also

`RAND`.

RANDLOG

### `RANDLOG`

random variate from a logarithmic distribution

    RANDLOG(p)

#### Arguments

`p`: probability

#### Note

If `p` < 0 or `p` > 1 `RANDLOG` returns #NUM!

#### See also

`RAND`.

RANDLOGISTIC

### `RANDLOGISTIC`

random variate from a logistic distribution

    RANDLOGISTIC(a)

#### Arguments

`a`: parameter of the logistic distribution

#### See also

`RAND`.

RANDLOGNORM

### `RANDLOGNORM`

random variate from a lognormal distribution

    RANDLOGNORM(ζ,σ)

#### Arguments

`ζ`: parameter of the lognormal distribution

`σ`: standard deviation of the distribution

#### Note

If `σ` < 0, `RANDLOGNORM` returns #NUM!

#### See also

`RAND`.

RANDNEGBINOM

### `RANDNEGBINOM`

random variate from a negative binomial distribution

    RANDNEGBINOM(p,n)

#### Arguments

`p`: probability of success in a single trial

`n`: number of failures

#### Note

If `p` < 0 or `p` > 1 `RANDNEGBINOM` returns #NUM! If `n` < 1 `RANDNEGBINOM` returns #NUM!

#### See also

`RAND`, `RANDBETWEEN`.

RANDNORM

### `RANDNORM`

random variate from a normal distribution

    RANDNORM(μ,σ)

#### Arguments

`μ`: mean of the distribution

`σ`: standard deviation of the distribution

#### Note

If `σ` < 0, `RANDNORM` returns #NUM!

#### See also

`RAND`.

RANDNORMTAIL

### `RANDNORMTAIL`

random variate from the upper tail of a normal distribution with mean 0

    RANDNORMTAIL(a,σ)

#### Arguments

`a`: lower limit of the tail

`σ`: standard deviation of the normal distribution

#### Note

The method is based on Marsaglia's famous rectangle-wedge-tail algorithm (Ann Math Stat 32, 894-899 (1961)), with this aspect explained in Knuth, v2, 3rd ed, p139, 586 (exercise 11).

#### See also

`RAND`.

RANDPARETO

### `RANDPARETO`

random variate from a Pareto distribution

    RANDPARETO(a,b)

#### Arguments

`a`: parameter of the Pareto distribution

`b`: parameter of the Pareto distribution

#### See also

`RAND`.

RANDPOISSON

### `RANDPOISSON`

random variate from a Poisson distribution

    RANDPOISSON(λ)

#### Arguments

`λ`: parameter of the Poisson distribution

#### Note

If `λ` < 0 `RANDPOISSON` returns #NUM!

#### See also

`RAND`, `RANDBETWEEN`.

RANDRAYLEIGH

### `RANDRAYLEIGH`

random variate from a Rayleigh distribution

    RANDRAYLEIGH(σ)

#### Arguments

`σ`: scale parameter of the Rayleigh distribution

#### See also

`RAND`.

RANDRAYLEIGHTAIL

### `RANDRAYLEIGHTAIL`

random variate from the tail of a Rayleigh distribution

    RANDRAYLEIGHTAIL(a,σ)

#### Arguments

`a`: lower limit of the tail

`σ`: scale parameter of the Rayleigh distribution

#### See also

`RAND`, `RANDRAYLEIGH`.

RANDSNORM

### `RANDSNORM`

random variate from a skew-normal distribution

    RANDSNORM(𝛼,𝜉,𝜔)

#### Arguments

`𝛼`: shape parameter of the skew-normal distribution, defaults to 0

`𝜉`: location parameter of the skew-normal distribution, defaults to 0

`𝜔`: scale parameter of the skew-normal distribution, defaults to 1

#### Description

The random variates are drawn from a skew-normal distribution with shape parameter `𝛼`. When `𝛼`=0, the skewness vanishes, and we obtain the standard normal density; as 𝛼 increases (in absolute value), the skewness of the distribution increases; when `𝛼` approaches infinity the density converges to the so-called half-normal (or folded normal) density function; if the sign of `𝛼` changes, the density is reflected on the opposite side of the vertical axis.

#### Note

The mean of a skew-normal distribution with location parameter `𝜉`=0 is not 0. The standard deviation of a skew-normal distribution with scale parameter `𝜔`=1 is not 1. The skewness of a skew-normal distribution is in general not `𝛼`. If `𝜔` < 0, `RANDSNORM` returns #NUM!

#### See also

`RANDNORM`, `RANDSTDIST`.

RANDSTDIST

### `RANDSTDIST`

random variate from a skew-t distribution

    RANDSTDIST(df,𝛼)

#### Arguments

`df`: degrees of freedom

`𝛼`: shape parameter of the skew-t distribution, defaults to 0

#### Note

The mean of a skew-t distribution is not 0. The standard deviation of a skew-t distribution is not 1. The skewness of a skew-t distribution is in general not `𝛼`.

#### See also

`RANDTDIST`, `RANDSNORM`.

RANDTDIST

### `RANDTDIST`

random variate from a Student t distribution

    RANDTDIST(df)

#### Arguments

`df`: degrees of freedom

#### See also

`RAND`.

RANDUNIFORM

### `RANDUNIFORM`

random variate from the uniform distribution from `a` to `b`

    RANDUNIFORM(a,b)

#### Arguments

`a`: lower limit of the uniform distribution

`b`: upper limit of the uniform distribution

#### Note

If `a` > `b` `RANDUNIFORM` returns #NUM!

#### See also

`RANDBETWEEN`, `RAND`.

RANDWEIBULL

### `RANDWEIBULL`

random variate from a Weibull distribution

    RANDWEIBULL(a,b)

#### Arguments

`a`: parameter of the Weibull distribution

`b`: parameter of the Weibull distribution

#### See also

`RAND`.

SIMTABLE

### `SIMTABLE`

one of the values in the given argument list depending on the round number of the simulation tool

    SIMTABLE(d1,d2,…)

#### Arguments

`d1`: first value

`d2`: second value

#### Description

`SIMTABLE` returns one of the values in the given argument list depending on the round number of the simulation tool. When the simulation tool is not activated, `SIMTABLE` returns `d1`.

With the simulation tool and the `SIMTABLE` function you can test given decision variables. Each `SIMTABLE` function contains the possible values of a simulation variable. In most valid simulation models you should have the same number of values `dN` for all decision variables. If the simulation is run more rounds than there are values defined, `SIMTABLE` returns #N/A! error (e.g. if A1 contains `=`SIMTABLE`(1)' and A2 `=`SIMTABLE`(1,2)', A1 yields #N/A! error on the second round).

The successive use of the simulation tool also requires that you give to the tool at least one input variable having RAND() or any other RAND<distribution name>() function in it. On each round, the simulation tool iterates for the given number of rounds over all the input variables to reevaluate them. On each iteration, the values of the output variables are stored, and when the round is completed, descriptive statistical information is created according to the values.

## Statistics

ADTEST

### `ADTEST`

Anderson-Darling Test of Normality

    ADTEST(x)

#### Arguments

`x`: array of sample values

#### Description

This function returns an array with the first row giving the p-value of the Anderson-Darling Test, the second row the test statistic of the test, and the third the number of observations in the sample.

#### Note

If there are less than 8 sample values, `ADTEST` returns #VALUE!

#### See also

`CHITEST`, `CVMTEST`, `LKSTEST`, `SFTEST`.

AVEDEV

### `AVEDEV`

average of the absolute deviations of a data set

    AVEDEV(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`STDEV`.

AVERAGE

### `AVERAGE`

average of all the numeric values and cells

    AVERAGE(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`SUM`, `COUNT`.

AVERAGEA

### `AVERAGEA`

average of all the values and cells

    AVERAGEA(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`.

BERNOULLI

### `BERNOULLI`

probability mass function of a Bernoulli distribution

    BERNOULLI(k,p)

#### Arguments

`k`: integer

`p`: probability of success

#### Note

If `k` != 0 and `k` != 1 this function returns a #NUM! error. If `p` < 0 or `p` > 1 this function returns a #NUM! error.

#### See also

`RANDBERNOULLI`.

BETADIST

### `BETADIST`

cumulative distribution function of the beta distribution

    BETADIST(x,alpha,beta,a,b)

#### Arguments

`x`: number

`alpha`: scale parameter

`beta`: scale parameter

`a`: optional lower bound, defaults to 0

`b`: optional upper bound, defaults to 1

#### Note

If `x` < `a` or `x` > `b` this function returns a #NUM! error. If `alpha` <= 0 or `beta` <= 0, this function returns a #NUM! error. If `a` >= `b` this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BETAINV`.

BETAINV

### `BETAINV`

inverse of the cumulative distribution function of the beta distribution

    BETAINV(p,alpha,beta,a,b)

#### Arguments

`p`: probability

`alpha`: scale parameter

`beta`: scale parameter

`a`: optional lower bound, defaults to 0

`b`: optional upper bound, defaults to 1

#### Note

If `p` < 0 or `p` > 1 this function returns a #NUM! error. If `alpha` <= 0 or `beta` <= 0, this function returns a #NUM! error. If `a` >= `b` this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BETADIST`.

BINOM.DIST.RANGE

### `BINOM.DIST.RANGE`

probability of the binomial distribution over an interval

    BINOM.DIST.RANGE(trials,p,start,end)

#### Arguments

`trials`: number of trials

`p`: probability of success in each trial

`start`: start of the interval

`end`: end of the interval, defaults to `start`

#### Note

If `start`, `end` or `trials` are non-integer they are truncated. If `trials` < 0 this function returns a #NUM! error. If `p` < 0 or `p` > 1 this function returns a #NUM! error. If `start` > `end` this function returns 0.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`BINOMDIST`, `R.PBINOM`.

BINOMDIST

### `BINOMDIST`

probability mass or cumulative distribution function of the binomial distribution

    BINOMDIST(n,trials,p,cumulative)

#### Arguments

`n`: number of successes

`trials`: number of trials

`p`: probability of success in each trial

`cumulative`: whether to evaluate the mass function or the cumulative distribution function

#### Note

If `n` or `trials` are non-integer they are truncated. If `n` < 0 or `trials` < 0 this function returns a #NUM! error. If `n` > `trials` this function returns a #NUM! error. If `p` < 0 or `p` > 1 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`POISSON`.

CAUCHY

### `CAUCHY`

probability density or cumulative distribution function of the Cauchy, Lorentz or Breit-Wigner distribution

    CAUCHY(x,a,cumulative)

#### Arguments

`x`: number

`a`: scale parameter

`cumulative`: whether to evaluate the density function or the cumulative distribution function

#### Note

If `a` < 0 this function returns a #NUM! error. If `cumulative` is neither TRUE nor FALSE this function returns a #VALUE! error.

#### See also

`RANDCAUCHY`.

CHIDIST

### `CHIDIST`

survival function of the chi-squared distribution

    CHIDIST(x,dof)

#### Arguments

`x`: number

`dof`: number of degrees of freedom

#### Description

The survival function is 1 minus the cumulative distribution function.

#### Note

If `dof` is non-integer it is truncated. If `dof` < 1 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`CHIDIST`(`x`,`dof`) is the OpenFormula function LEGACY.`CHIDIST`(`x`,`dof`).

#### See also

`CHIINV`, `CHITEST`.

CHIINV

### `CHIINV`

inverse of the survival function of the chi-squared distribution

    CHIINV(p,dof)

#### Arguments

`p`: probability

`dof`: number of degrees of freedom

#### Description

The survival function is 1 minus the cumulative distribution function.

#### Note

If `p` < 0 or `p` > 1 or `dof` < 1 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`CHIINV`(`p`,`dof`) is the OpenFormula function LEGACY.CHIDIST(`p`,`dof`).

#### See also

`CHIDIST`, `CHITEST`.

CHITEST

### `CHITEST`

p value of the Goodness of Fit Test

    CHITEST(actual_range,theoretical_range)

#### Arguments

`actual_range`: observed data

`theoretical_range`: expected values

#### Note

If the actual range is not an n by 1 or 1 by n range, but an n by m range, then `CHITEST` uses (n-1) times (m-1) as degrees of freedom. This is useful if the expected values were calculated from the observed value in a test of independence or test of homogeneity.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`CHITEST` is the OpenFormula function LEGACY.`CHITEST`.

#### See also

`CHIDIST`, `CHIINV`.

CONFIDENCE

### `CONFIDENCE`

margin of error of a confidence interval for the population mean

    CONFIDENCE(alpha,stddev,size)

#### Arguments

`alpha`: significance level

`stddev`: population standard deviation

`size`: sample size

#### Note

This function requires the usually unknown population standard deviation. If `size` is non-integer it is truncated. If `size` < 0 this function returns a #NUM! error. If `size` is 0 this function returns a #DIV/0! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`.

CORREL

### `CORREL`

Pearson correlation coefficient of two data sets

    CORREL(array1,array2)

#### Arguments

`array1`: first data set

`array2`: second data set

#### Description

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COVAR`, `FISHER`, `FISHERINV`.

COUNT

### `COUNT`

total number of integer or floating point arguments passed

    COUNT(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`.

COUNTA

### `COUNTA`

number of arguments passed not including empty cells

    COUNTA(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `COUNT`, `DCOUNT`, `DCOUNTA`, `PRODUCT`, `SUM`.

COVAR

### `COVAR`

covariance of two data sets

    COVAR(array1,array2)

#### Arguments

`array1`: first data set

`array2`: set data set

#### Description

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CORREL`, `FISHER`, `FISHERINV`.

COVARIANCE.S

### `COVARIANCE.S`

sample covariance of two data sets

    COVARIANCE.S(array1,array2)

#### Arguments

`array1`: first data set

`array2`: set data set

#### Description

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COVAR`, `CORREL`.

CRITBINOM

### `CRITBINOM`

right-tailed critical value of the binomial distribution

    CRITBINOM(trials,p,alpha)

#### Arguments

`trials`: number of trials

`p`: probability of success in each trial

`alpha`: significance level (area of the tail)

#### Note

If `trials` is a non-integer it is truncated. If `trials` < 0 this function returns a #NUM! error. If `p` < 0 or `p` > 1 this function returns a #NUM! error. If `alpha` < 0 or `alpha` > 1 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BINOMDIST`.

CRONBACH

### `CRONBACH`

Cronbach's alpha

    CRONBACH(ref1,ref2,…)

#### Arguments

`ref1`: first data set

`ref2`: second data set

#### See also

`VAR`.

CVMTEST

### `CVMTEST`

Cramér-von Mises Test of Normality

    CVMTEST(x)

#### Arguments

`x`: array of sample values

#### Description

This function returns an array with the first row giving the p-value of the Cramér-von Mises Test, the second row the test statistic of the test, and the third the number of observations in the sample.

#### Note

If there are less than 8 sample values, `CVMTEST` returns #VALUE!

#### See also

`CHITEST`, `ADTEST`, `LKSTEST`, `SFTEST`.

DEVSQ

### `DEVSQ`

sum of squares of deviations of a data set

    DEVSQ(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`STDEV`.

EXPONDIST

### `EXPONDIST`

probability density or cumulative distribution function of the exponential distribution

    EXPONDIST(x,y,cumulative)

#### Arguments

`x`: number

`y`: scale parameter

`cumulative`: whether to evaluate the density function or the cumulative distribution function

#### Description

If `cumulative` is false it will return: `y` * exp (-`y`*`x`),otherwise it will return 1 - exp (-`y`*`x`).

#### Note

If `x` < 0 or `y` <= 0 this will return an error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`POISSON`.

EXPPOWDIST

### `EXPPOWDIST`

the probability density function of the Exponential Power distribution

    EXPPOWDIST(x,a,b)

#### Arguments

`x`: number

`a`: scale parameter

`b`: scale parameter

#### Description

This distribution has been recommended for lifetime analysis when a U-shaped hazard function is desired. This corresponds to rapid failure once the product starts to wear out after a period of steady or even improving reliability.

#### See also

`RANDEXPPOW`.

FDIST

### `FDIST`

survival function of the F distribution

    FDIST(x,dof_of_num,dof_of_denom)

#### Arguments

`x`: number

`dof_of_num`: numerator degrees of freedom

`dof_of_denom`: denominator degrees of freedom

#### Description

The survival function is 1 minus the cumulative distribution function.

#### Note

If `x` < 0 this function returns a #NUM! error. If `dof_of_num` < 1 or `dof_of_denom` < 1, this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`FDIST` is the OpenFormula function LEGACY.`FDIST`.

#### See also

`FINV`.

FINV

### `FINV`

inverse of the survival function of the F distribution

    FINV(p,dof_of_num,dof_of_denom)

#### Arguments

`p`: probability

`dof_of_num`: numerator degrees of freedom

`dof_of_denom`: denominator degrees of freedom

#### Description

The survival function is 1 minus the cumulative distribution function.

#### Note

If `p` < 0 or `p` > 1 this function returns a #NUM! error. If `dof_of_num` < 1 or `dof_of_denom` < 1 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`FINV` is the OpenFormula function LEGACY.`FINV`.

#### See also

`FDIST`.

FISHER

### `FISHER`

Fisher transformation

    FISHER(x)

#### Arguments

`x`: number

#### Note

If `x` is not a number, this function returns a #VALUE! error. If `x` <= -1 or `x` >= 1, this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FISHERINV`, `ATANH`.

FISHERINV

### `FISHERINV`

inverse of the Fisher transformation

    FISHERINV(x)

#### Arguments

`x`: number

#### Note

If `x` is a non-number this function returns a #VALUE! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FISHER`, `TANH`.

FORECAST

### `FORECAST`

estimates a future value according to existing values using simple linear regression

    FORECAST(x,known_ys,known_xs)

#### Arguments

`x`: x-value whose matching y-value should be forecast

`known_ys`: known y-values

`known_xs`: known x-values

#### Description

This function estimates a future value according to existing values using simple linear regression.

#### Note

If `known_xs` or `known_ys` contains no data entries or different number of data entries, this function returns a #N/A error. If the variance of the `known_xs` is zero, this function returns a #DIV/0 error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`INTERCEPT`, `TREND`.

FREQUENCY

### `FREQUENCY`

frequency table

    FREQUENCY(data_array,bins_array)

#### Arguments

`data_array`: data values

`bins_array`: array of cutoff values

#### Description

The results are given as an array.

If the `bins_array` is empty, this function returns the number of data points in `data_array`.

#### Microsoft Excel Compatibility

This function is Excel compatible.

FTEST

### `FTEST`

p-value for the two-tailed hypothesis test comparing the variances of two populations

    FTEST(array1,array2)

#### Arguments

`array1`: sample from the first population

`array2`: sample from the second population

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FDIST`, `FINV`.

GAMMADIST

### `GAMMADIST`

probability density or cumulative distribution function of the gamma distribution

    GAMMADIST(x,alpha,beta,cumulative)

#### Arguments

`x`: number

`alpha`: scale parameter

`beta`: scale parameter

`cumulative`: whether to evaluate the density function or the cumulative distribution function

#### Note

If `x` < 0 this function returns a #NUM! error. If `alpha` <= 0 or `beta` <= 0, this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`GAMMAINV`.

GAMMAINV

### `GAMMAINV`

inverse of the cumulative gamma distribution

    GAMMAINV(p,alpha,beta)

#### Arguments

`p`: probability

`alpha`: scale parameter

`beta`: scale parameter

#### Note

If `p` < 0 or `p` > 1 this function returns a #NUM! error. If `alpha` <= 0 or `beta` <= 0 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`GAMMADIST`.

GEOMDIST

### `GEOMDIST`

probability mass or cumulative distribution function of the geometric distribution

    GEOMDIST(k,p,cumulative)

#### Arguments

`k`: number of trials

`p`: probability of success in any trial

`cumulative`: whether to evaluate the mass function or the cumulative distribution function

#### Note

If `k` < 0 this function returns a #NUM! error. If `p` < 0 or `p` > 1 this function returns a #NUM! error. If `cumulative` is neither TRUE nor FALSE this function returns a #VALUE! error.

#### See also

`RANDGEOM`.

GEOMEAN

### `GEOMEAN`

geometric mean

    GEOMEAN(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

The geometric mean is equal to the Nth root of the product of the N values.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `HARMEAN`, `MEDIAN`, `MODE`, `TRIMMEAN`.

GROWTH

### `GROWTH`

exponential growth prediction

    GROWTH(known_ys,known_xs,new_xs,affine)

#### Arguments

`known_ys`: known y-values

`known_xs`: known x-values; defaults to the array {1, 2, 3, …}

`new_xs`: x-values for which to estimate the y-values; defaults to `known_xs`

`affine`: if true, the model contains a constant term, defaults to true

#### Description

`GROWTH` function applies the “least squares” method to fit an exponential curve to your data and predicts the exponential growth by using this curve.

`GROWTH` returns an array having one column and a row for each data point in `new_xs`.

#### Note

If `known_ys` and `known_xs` have unequal number of data points, this function returns a #NUM! error.

#### See also

`LOGEST`, `GROWTH`, `TREND`.

HARMEAN

### `HARMEAN`

harmonic mean

    HARMEAN(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

The harmonic mean of N data points is N divided by the sum of the reciprocals of the data points).

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `GEOMEAN`, `MEDIAN`, `MODE`, `TRIMMEAN`.

HYPGEOMDIST

### `HYPGEOMDIST`

probability mass or cumulative distribution function of the hypergeometric distribution

    HYPGEOMDIST(x,n,M,N,cumulative)

#### Arguments

`x`: number of successes

`n`: sample size

`M`: number of possible successes in the population

`N`: population size

`cumulative`: whether to evaluate the mass function or the cumulative distribution function

#### Note

If `x`,`n`,`M` or `N` is a non-integer it is truncated. If `x`,`n`,`M` or `N` < 0 this function returns a #NUM! error. If `x` > `M` or `n` > `N` this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BINOMDIST`, `POISSON`.

INTERCEPT

### `INTERCEPT`

the intercept of a linear regression line

    INTERCEPT(known_ys,known_xs)

#### Arguments

`known_ys`: known y-values

`known_xs`: known x-values

#### Note

If `known_xs` or `known_ys` contains no data entries or different number of data entries, this function returns a #N/A error. If the variance of the `known_xs` is zero, this function returns #DIV/0 error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FORECAST`, `TREND`.

KURT

### `KURT`

unbiased estimate of the kurtosis of a data set

    KURT(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Strings and empty cells are simply ignored.

#### Note

This is only meaningful if the underlying distribution really has a fourth moment. The kurtosis is offset by three such that a normal distribution will have zero kurtosis. If fewer than four numbers are given or all of them are equal this function returns a #DIV/0! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `VAR`, `SKEW`, `KURTP`.

KURTP

### `KURTP`

population kurtosis of a data set

    KURTP(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Strings and empty cells are simply ignored.

#### Note

If fewer than two numbers are given or all of them are equal this function returns a #DIV/0! error.

#### See also

`AVERAGE`, `VARP`, `SKEWP`, `KURT`.

LANDAU

### `LANDAU`

approximate probability density function of the Landau distribution

    LANDAU(x)

#### Arguments

`x`: number

#### See also

`RANDLANDAU`.

LAPLACE

### `LAPLACE`

probability density function of the Laplace distribution

    LAPLACE(x,a)

#### Arguments

`x`: number

`a`: mean

#### See also

`RANDLAPLACE`.

LARGE

### `LARGE`

k
-th largest value in a data set

    LARGE(data,k)

#### Arguments

`data`: data set

`k`: which value to find

#### Note

If data set is empty this function returns a #NUM! error. If `k` <= 0 or `k` is greater than the number of data items given this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`PERCENTILE`, `PERCENTRANK`, `QUARTILE`, `SMALL`.

LINEST

### `LINEST`

multiple linear regression coefficients and statistics

    LINEST(known_ys,known_xs,affine,stats)

#### Arguments

`known_ys`: vector of values of dependent variable

`known_xs`: array of values of independent variables, defaults to a single vector {1,…,n}

`affine`: if true, the model contains a constant term, defaults to true

`stats`: if true, some additional statistics are provided, defaults to false

#### Description

This function returns an array with the first row giving the regression coefficients for the independent variables x_m, x_(m-1),…,x_2, x_1 followed by the y-intercept if `affine` is true.

If `stats` is true, the second row contains the corresponding standard errors of the regression coefficients.In this case, the third row contains the R^2 value and the standard error for the predicted value. The fourth row contains the observed F value and its degrees of freedom. Finally, the fifth row contains the regression sum of squares and the residual sum of squares.

If `affine` is false, R^2 is the uncentered version of the coefficient of determination; that is the proportion of the sum of squares explained by the model.

#### Note

If the length of `known_ys` does not match the corresponding length of `known_xs`, this function returns a #NUM! error.

#### See also

`LOGEST`, `TREND`.

LKSTEST

### `LKSTEST`

Lilliefors (Kolmogorov-Smirnov) Test of Normality

    LKSTEST(x)

#### Arguments

`x`: array of sample values

#### Description

This function returns an array with the first row giving the p-value of the Lilliefors (Kolmogorov-Smirnov) Test, the second row the test statistic of the test, and the third the number of observations in the sample.

#### Note

If there are less than 5 sample values, `LKSTEST` returns #VALUE!

#### See also

`CHITEST`, `ADTEST`, `SFTEST`, `CVMTEST`.

LOGEST

### `LOGEST`

exponential least square fit

    LOGEST(known_ys,known_xs,affine,stat)

#### Arguments

`known_ys`: known y-values

`known_xs`: known x-values; default to an array {1, 2, 3, …}

`affine`: if true, the model contains a constant term, defaults to true

`stat`: if true, extra statistical information will be returned; defaults to FALSE

#### Description

`LOGEST` function applies the “least squares” method to fit an exponential curve of the form y = b * m{1}^x{1} * m{2}^x{2}... to your data.

`LOGEST` returns an array { m{n},m{n-1}, ...,m{1},b }.

#### Note

Extra statistical information is written below the regression line coefficients in the result array. Extra statistical information consists of four rows of data. In the first row the standard error values for the coefficients m1, (m2, ...), b are represented. The second row contains the square of R and the standard error for the y estimate. The third row contains the F-observed value and the degrees of freedom. The last row contains the regression sum of squares and the residual sum of squares. If `known_ys` and `known_xs` have unequal number of data points, this function returns a #NUM! error.

#### See also

`GROWTH`, `TREND`.

LOGFIT

### `LOGFIT`

logarithmic least square fit (using a trial and error method)

    LOGFIT(known_ys,known_xs)

#### Arguments

`known_ys`: known y-values

`known_xs`: known x-values

#### Description

`LOGFIT` function applies the “least squares” method to fit the logarithmic equation y = a + b * ln(sign * (x - c)) , sign = +1 or -1 to your data. The graph of the equation is a logarithmic curve moved horizontally by c and possibly mirrored across the y-axis (if sign = -1).

`LOGFIT` returns an array having five columns and one row. `Sign' is given in the first column, `a', `b', and `c' are given in columns 2 to 4. Column 5 holds the sum of squared residuals.

#### Note

An error is returned when there are less than 3 different x's or y's, or when the shape of the point cloud is too different from a ``logarithmic'' one. You can use the above formula = a + b * ln(sign * (x - c)) or rearrange it to = (exp((y - a) / b)) / sign + c to compute unknown y's or x's, respectively. This is non-linear fitting by trial-and-error. The accuracy of `c' is: width of x-range -> rounded to the next smaller (10^integer), times 0.000001. There might be cases in which the returned fit is not the best possible.

#### See also

`LOGREG`, `LINEST`, `LOGEST`.

LOGINV

### `LOGINV`

inverse of the cumulative distribution function of the lognormal distribution

    LOGINV(p,mean,stddev)

#### Arguments

`p`: probability

`mean`: mean

`stddev`: standard deviation

#### Note

If `p` < 0 or `p` > 1 or `stddev` <= 0 this function returns #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`EXP`, `LN`, `LOG`, `LOG10`, `LOGNORMDIST`.

LOGISTIC

### `LOGISTIC`

probability density function of the logistic distribution

    LOGISTIC(x,a)

#### Arguments

`x`: number

`a`: scale parameter

#### See also

`RANDLOGISTIC`.

LOGNORMDIST

### `LOGNORMDIST`

cumulative distribution function of the lognormal distribution

    LOGNORMDIST(x,mean,stddev)

#### Arguments

`x`: number

`mean`: mean

`stddev`: standard deviation

#### Note

If `stddev` = 0 `LOGNORMDIST` returns a #DIV/0! error. If `x` <= 0, `mean` < 0 or `stddev` <= 0 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`NORMDIST`.

LOGREG

### `LOGREG`

the logarithmic regression

    LOGREG(known_ys,known_xs,affine,stat)

#### Arguments

`known_ys`: known y-values

`known_xs`: known x-values; defaults to the array {1, 2, 3, …}

`affine`: if true, the model contains a constant term, defaults to true

`stat`: if true, extra statistical information will be returned; defaults to FALSE

#### Description

`LOGREG` function transforms your x's to z=ln(x) and applies the “least squares” method to fit the linear equation y = m * z + b to your y's and z's --- equivalent to fitting the equation y = m * ln(x) + b to y's and x's. `LOGREG` returns an array having two columns and one row. m is given in the first column and b in the second.

Any extra statistical information is written below m and b in the result array. This extra statistical information consists of four rows of data: In the first row the standard error values for the coefficients m, b are given. The second row contains the square of R and the standard error for the y estimate. The third row contains the F-observed value and the degrees of freedom. The last row contains the regression sum of squares and the residual sum of squares.The default of `stat` is FALSE.

#### Note

If `known_ys` and `known_xs` have unequal number of data points, this function returns a #NUM! error.

#### See also

`LOGFIT`, `LINEST`, `LOGEST`.

MAX

### `MAX`

largest value, with negative numbers considered smaller than positive numbers

    MAX(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MIN`, `ABS`.

MAXA

### `MAXA`

largest value, with negative numbers considered smaller than positive numbers

    MAXA(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MAX`, `MINA`.

MEDIAN

### `MEDIAN`

median of a data set

    MEDIAN(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Strings and empty cells are simply ignored.

#### Note

If even numbers are given `MEDIAN` returns the average of the two numbers in the center.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `COUNT`, `COUNTA`, `DAVERAGE`, `MODE`, `SSMEDIAN`, `SUM`.

MIN

### `MIN`

smallest value, with negative numbers considered smaller than positive numbers

    MIN(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MAX`, `ABS`.

MINA

### `MINA`

smallest value, with negative numbers considered smaller than positive numbers

    MINA(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MIN`, `MAXA`.

MODE

### `MODE`

first most common number in the dataset

    MODE(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Strings and empty cells are simply ignored.

If the data set does not contain any duplicates this function returns a #N/A error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `MEDIAN`.

NEGBINOMDIST

### `NEGBINOMDIST`

probability mass function of the negative binomial distribution

    NEGBINOMDIST(f,t,p)

#### Arguments

`f`: number of failures

`t`: threshold number of successes

`p`: probability of a success

#### Note

If `f` or `t` is a non-integer it is truncated. If (`f` + `t` -1) <= 0 this function returns a #NUM! error. If `p` < 0 or `p` > 1 this functions returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BINOMDIST`, `COMBIN`, `FACT`, `HYPGEOMDIST`, `PERMUT`.

NORMDIST

### `NORMDIST`

probability density or cumulative distribution function of a normal distribution

    NORMDIST(x,mean,stddev,cumulative)

#### Arguments

`x`: number

`mean`: mean of the distribution

`stddev`: standard deviation of the distribution

`cumulative`: whether to evaluate the density function or the cumulative distribution function

#### Note

If `stddev` is 0 this function returns a #DIV/0! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`POISSON`.

NORMINV

### `NORMINV`

inverse of the cumulative distribution function of a normal distribution

    NORMINV(p,mean,stddev)

#### Arguments

`p`: probability

`mean`: mean of the distribution

`stddev`: standard deviation of the distribution

#### Note

If `p` < 0 or `p` > 1 or `stddev` <= 0 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`NORMDIST`, `NORMSDIST`, `NORMSINV`, `STANDARDIZE`, `ZTEST`.

NORMSDIST

### `NORMSDIST`

cumulative density function of the standard normal distribution

    NORMSDIST(x)

#### Arguments

`x`: number

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`NORMSDIST` is the OpenFormula function LEGACY.`NORMSDIST`.

#### See also

`NORMDIST`.

NORMSINV

### `NORMSINV`

inverse of the cumulative density function of the standard normal distribution

    NORMSINV(p)

#### Arguments

`p`: given probability

#### Note

If `p` < 0 or `p` > 1 this function returns #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

`NORMSINV` is the OpenFormula function LEGACY.`NORMSINV`.

#### See also

`NORMDIST`, `NORMINV`, `NORMSDIST`, `STANDARDIZE`, `ZTEST`.

PARETO

### `PARETO`

probability density function of the Pareto distribution

    PARETO(x,a,b)

#### Arguments

`x`: number

`a`: exponent

`b`: scale parameter

#### See also

`RANDPARETO`.

PEARSON

### `PEARSON`

Pearson correlation coefficient of the paired set of data

    PEARSON(array1,array2)

#### Arguments

`array1`: first component values

`array2`: second component values

#### Description

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`INTERCEPT`, `LINEST`, `RSQ`, `SLOPE`, `STEYX`.

PERCENTILE

### `PERCENTILE`

determines the 100*`k`-th percentile of the given data points

    PERCENTILE(array,k)

#### Arguments

`array`: data points

`k`: which percentile to calculate

#### Note

If `array` is empty, this function returns a #NUM! error. If `k` < 0 or `k` > 1, this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`QUARTILE`.

PERCENTRANK

### `PERCENTRANK`

rank of a data point in a data set

    PERCENTRANK(array,x,significance)

#### Arguments

`array`: range of numeric values

`x`: data point to be ranked

`significance`: number of significant digits, defaults to 3

#### Note

If `array` contains no data points, this function returns a #NUM! error. If `significance` is less than one, this function returns a #NUM! error. If `x` exceeds the largest value or is less than the smallest value in `array`, this function returns a #NUM! error. If `x` does not match any of the values in `array` or `x` matches more than once, this function interpolates the returned value.

#### See also

`LARGE`, `MAX`, `MEDIAN`, `MIN`, `PERCENTILE`, `QUARTILE`, `SMALL`.

PERMUT

### `PERMUT`

number of `k`-permutations of a `n`-set

    PERMUT(n,k)

#### Arguments

`n`: size of the base set

`k`: number of elements in each permutation

#### Note

If `n` = 0 this function returns a #NUM! error. If `n` < `k` this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COMBIN`.

PERMUTATIONA

### `PERMUTATIONA`

the number of permutations of `y` objects chosen from `x` objects with repetition allowed

    PERMUTATIONA(x,y)

#### Arguments

`x`: total number of objects

`y`: number of selected objects

#### Note

If both `x` and `y` equal 0, `PERMUTATIONA` returns 1. If `x` < 0 or `y` < 0, `PERMUTATIONA` returns #NUM! If `x` or `y` are not integers, they are truncated

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`POWER`.

POISSON

### `POISSON`

probability mass or cumulative distribution function of the Poisson distribution

    POISSON(x,mean,cumulative)

#### Arguments

`x`: number of events

`mean`: mean of the distribution

`cumulative`: whether to evaluate the mass function or the cumulative distribution function

#### Note

If `x` is a non-integer it is truncated. If `x` < 0 this function returns a #NUM! error. If `mean` <= 0 `POISSON` returns the #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`NORMDIST`, `WEIBULL`.

PROB

### `PROB`

probability of an interval for a discrete (and finite) probability distribution

    PROB(x_range,prob_range,lower_limit,upper_limit)

#### Arguments

`x_range`: possible values

`prob_range`: probabilities of the corresponding values

`lower_limit`: lower interval limit

`upper_limit`: upper interval limit, defaults to `lower_limit`

#### Note

If the sum of the probabilities in `prob_range` is not equal to 1 this function returns a #NUM! error. If any value in `prob_range` is <=0 or > 1, this function returns a #NUM! error. If `x_range` and `prob_range` contain a different number of data entries, this function returns a #N/A error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`BINOMDIST`, `CRITBINOM`.

QUARTILE

### `QUARTILE`

the `k`-th quartile of the data points

    QUARTILE(array,quart)

#### Arguments

`array`: data points

`quart`: a number from 0 to 4, indicating which quartile to calculate

#### Note

If `array` is empty, this function returns a #NUM! error. If `quart` < 0 or `quart` > 4, this function returns a #NUM! error. If `quart` = 0, the smallest value of `array` to be returned. If `quart` is not an integer, it is truncated.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LARGE`, `MAX`, `MEDIAN`, `MIN`, `PERCENTILE`, `SMALL`.

R.DBETA

### `R.DBETA`

probability density function of the beta distribution

    R.DBETA(x,a,b,give_log)

#### Arguments

`x`: observation

`a`: the first shape parameter of the distribution

`b`: the second scale parameter of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the beta distribution.

#### See also

`R.PBETA`, `R.QBETA`.

R.DBINOM

### `R.DBINOM`

probability density function of the binomial distribution

    R.DBINOM(x,n,psuc,give_log)

#### Arguments

`x`: observation

`n`: the number of trials

`psuc`: the probability of success in each trial

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the binomial distribution.

#### See also

`R.PBINOM`, `R.QBINOM`.

R.DCAUCHY

### `R.DCAUCHY`

probability density function of the Cauchy distribution

    R.DCAUCHY(x,location,scale,give_log)

#### Arguments

`x`: observation

`location`: the center of the distribution

`scale`: the scale parameter of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the Cauchy distribution.

#### See also

`R.PCAUCHY`, `R.QCAUCHY`.

R.DCHISQ

### `R.DCHISQ`

probability density function of the chi-square distribution

    R.DCHISQ(x,df,give_log)

#### Arguments

`x`: observation

`df`: the number of degrees of freedom of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the chi-square distribution.

#### OpenDocument Format (ODF) Compatibility

A two argument invocation `R.DCHISQ`(`x`,`df`) is exported to OpenFormula as CHISQDIST(`x`,`df`,FALSE()).

#### See also

`R.PCHISQ`, `R.QCHISQ`.

R.DEXP

### `R.DEXP`

probability density function of the exponential distribution

    R.DEXP(x,scale,give_log)

#### Arguments

`x`: observation

`scale`: the scale parameter of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the exponential distribution.

#### See also

`R.PEXP`, `R.QEXP`.

R.DF

### `R.DF`

probability density function of the F distribution

    R.DF(x,n1,n2,give_log)

#### Arguments

`x`: observation

`n1`: the first number of degrees of freedom of the distribution

`n2`: the second number of degrees of freedom of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the F distribution.

#### See also

`R.PF`, `R.QF`.

R.DGAMMA

### `R.DGAMMA`

probability density function of the gamma distribution

    R.DGAMMA(x,shape,scale,give_log)

#### Arguments

`x`: observation

`shape`: the shape parameter of the distribution

`scale`: the scale parameter of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the gamma distribution.

#### See also

`R.PGAMMA`, `R.QGAMMA`.

R.DGEOM

### `R.DGEOM`

probability density function of the geometric distribution

    R.DGEOM(x,psuc,give_log)

#### Arguments

`x`: observation

`psuc`: the probability of success in each trial

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the geometric distribution.

#### See also

`R.PGEOM`, `R.QGEOM`.

R.DHYPER

### `R.DHYPER`

probability density function of the hypergeometric distribution

    R.DHYPER(x,r,b,n,give_log)

#### Arguments

`x`: observation

`r`: the number of red balls

`b`: the number of black balls

`n`: the number of balls drawn

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the hypergeometric distribution.

#### See also

`R.PHYPER`, `R.QHYPER`.

R.DLNORM

### `R.DLNORM`

probability density function of the log-normal distribution

    R.DLNORM(x,logmean,logsd,give_log)

#### Arguments

`x`: observation

`logmean`: mean of the underlying normal distribution

`logsd`: standard deviation of the underlying normal distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the log-normal distribution.

#### See also

`R.PLNORM`, `R.QLNORM`.

R.DNBINOM

### `R.DNBINOM`

probability density function of the negative binomial distribution

    R.DNBINOM(x,n,psuc,give_log)

#### Arguments

`x`: observation

`n`: the number of trials

`psuc`: the probability of success in each trial

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the negative binomial distribution.

#### See also

`R.PNBINOM`, `R.QNBINOM`.

R.DNORM

### `R.DNORM`

probability density function of the normal distribution

    R.DNORM(x,mu,sigma,give_log)

#### Arguments

`x`: observation

`mu`: mean of the distribution

`sigma`: standard deviation of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the normal distribution.

#### See also

`R.PNORM`, `R.QNORM`.

R.DPOIS

### `R.DPOIS`

probability density function of the Poisson distribution

    R.DPOIS(x,lambda,give_log)

#### Arguments

`x`: observation

`lambda`: the mean of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the Poisson distribution.

#### See also

`R.PPOIS`, `R.QPOIS`.

R.DSNORM

### `R.DSNORM`

probability density function of the skew-normal distribution

    R.DSNORM(x,shape,location,scale,give_log)

#### Arguments

`x`: observation

`shape`: the shape parameter of the distribution

`location`: the location parameter of the distribution

`scale`: the scale parameter of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the skew-normal distribution.

#### See also

`R.PSNORM`.

R.DST

### `R.DST`

probability density function of the skew-t distribution

    R.DST(x,n,shape,give_log)

#### Arguments

`x`: observation

`n`: the number of degrees of freedom of the distribution

`shape`: the shape parameter of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the skew-t distribution.

R.DT

### `R.DT`

probability density function of the Student t distribution

    R.DT(x,n,give_log)

#### Arguments

`x`: observation

`n`: the number of degrees of freedom of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the Student t distribution.

#### See also

`R.PT`, `R.QT`.

R.DWEIBULL

### `R.DWEIBULL`

probability density function of the Weibull distribution

    R.DWEIBULL(x,shape,scale,give_log)

#### Arguments

`x`: observation

`shape`: the shape parameter of the distribution

`scale`: the scale parameter of the distribution

`give_log`: if true, log of the result will be returned instead

#### Description

This function returns the probability density function of the Weibull distribution.

#### See also

`R.PWEIBULL`, `R.QWEIBULL`.

R.PBETA

### `R.PBETA`

cumulative distribution function of the beta distribution

    R.PBETA(x,a,b,lower_tail,log_p)

#### Arguments

`x`: observation

`a`: the first shape parameter of the distribution

`b`: the second scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the beta distribution.

#### See also

`R.DBETA`, `R.QBETA`.

R.PBINOM

### `R.PBINOM`

cumulative distribution function of the binomial distribution

    R.PBINOM(x,n,psuc,lower_tail,log_p)

#### Arguments

`x`: observation

`n`: the number of trials

`psuc`: the probability of success in each trial

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the binomial distribution.

#### See also

`R.DBINOM`, `R.QBINOM`.

R.PCAUCHY

### `R.PCAUCHY`

cumulative distribution function of the Cauchy distribution

    R.PCAUCHY(x,location,scale,lower_tail,log_p)

#### Arguments

`x`: observation

`location`: the center of the distribution

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the Cauchy distribution.

#### See also

`R.DCAUCHY`, `R.QCAUCHY`.

R.PCHISQ

### `R.PCHISQ`

cumulative distribution function of the chi-square distribution

    R.PCHISQ(x,df,lower_tail,log_p)

#### Arguments

`x`: observation

`df`: the number of degrees of freedom of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the chi-square distribution.

#### OpenDocument Format (ODF) Compatibility

A two argument invocation `R.PCHISQ`(`x`,`df`) is exported to OpenFormula as CHISQDIST(`x`,`df`).

#### See also

`R.DCHISQ`, `R.QCHISQ`.

R.PEXP

### `R.PEXP`

cumulative distribution function of the exponential distribution

    R.PEXP(x,scale,lower_tail,log_p)

#### Arguments

`x`: observation

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the exponential distribution.

#### See also

`R.DEXP`, `R.QEXP`.

R.PF

### `R.PF`

cumulative distribution function of the F distribution

    R.PF(x,n1,n2,lower_tail,log_p)

#### Arguments

`x`: observation

`n1`: the first number of degrees of freedom of the distribution

`n2`: the second number of degrees of freedom of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the F distribution.

#### See also

`R.DF`, `R.QF`.

R.PGAMMA

### `R.PGAMMA`

cumulative distribution function of the gamma distribution

    R.PGAMMA(x,shape,scale,lower_tail,log_p)

#### Arguments

`x`: observation

`shape`: the shape parameter of the distribution

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the gamma distribution.

#### See also

`R.DGAMMA`, `R.QGAMMA`.

R.PGEOM

### `R.PGEOM`

cumulative distribution function of the geometric distribution

    R.PGEOM(x,psuc,lower_tail,log_p)

#### Arguments

`x`: observation

`psuc`: the probability of success in each trial

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the geometric distribution.

#### See also

`R.DGEOM`, `R.QGEOM`.

R.PHYPER

### `R.PHYPER`

cumulative distribution function of the hypergeometric distribution

    R.PHYPER(x,r,b,n,lower_tail,log_p)

#### Arguments

`x`: observation

`r`: the number of red balls

`b`: the number of black balls

`n`: the number of balls drawn

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the hypergeometric distribution.

#### See also

`R.DHYPER`, `R.QHYPER`.

R.PLNORM

### `R.PLNORM`

cumulative distribution function of the log-normal distribution

    R.PLNORM(x,logmean,logsd,lower_tail,log_p)

#### Arguments

`x`: observation

`logmean`: mean of the underlying normal distribution

`logsd`: standard deviation of the underlying normal distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the log-normal distribution.

#### See also

`R.DLNORM`, `R.QLNORM`.

R.PNBINOM

### `R.PNBINOM`

cumulative distribution function of the negative binomial distribution

    R.PNBINOM(x,n,psuc,lower_tail,log_p)

#### Arguments

`x`: observation

`n`: the number of trials

`psuc`: the probability of success in each trial

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the negative binomial distribution.

#### See also

`R.DNBINOM`, `R.QNBINOM`.

R.PNORM

### `R.PNORM`

cumulative distribution function of the normal distribution

    R.PNORM(x,mu,sigma,lower_tail,log_p)

#### Arguments

`x`: observation

`mu`: mean of the distribution

`sigma`: standard deviation of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the normal distribution.

#### See also

`R.DNORM`, `R.QNORM`.

R.PPOIS

### `R.PPOIS`

cumulative distribution function of the Poisson distribution

    R.PPOIS(x,lambda,lower_tail,log_p)

#### Arguments

`x`: observation

`lambda`: the mean of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the Poisson distribution.

#### See also

`R.DPOIS`, `R.QPOIS`.

R.PSNORM

### `R.PSNORM`

cumulative distribution function of the skew-normal distribution

    R.PSNORM(x,shape,location,scale,lower_tail,log_p)

#### Arguments

`x`: observation

`shape`: the shape parameter of the distribution

`location`: the location parameter of the distribution

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the skew-normal distribution.

#### See also

`R.DSNORM`.

R.PT

### `R.PT`

cumulative distribution function of the Student t distribution

    R.PT(x,n,lower_tail,log_p)

#### Arguments

`x`: observation

`n`: the number of degrees of freedom of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the Student t distribution.

#### See also

`R.DT`, `R.QT`.

R.PWEIBULL

### `R.PWEIBULL`

cumulative distribution function of the Weibull distribution

    R.PWEIBULL(x,shape,scale,lower_tail,log_p)

#### Arguments

`x`: observation

`shape`: the shape parameter of the distribution

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the cumulative distribution function of the Weibull distribution.

#### See also

`R.DWEIBULL`, `R.QWEIBULL`.

R.QBETA

### `R.QBETA`

probability quantile function of the beta distribution

    R.QBETA(p,a,b,lower_tail,log_p)

#### Arguments

`p`: probability

`a`: the first shape parameter of the distribution

`b`: the second scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the beta distribution.

#### See also

`R.DBETA`, `R.PBETA`.

R.QBINOM

### `R.QBINOM`

probability quantile function of the binomial distribution

    R.QBINOM(x,n,psuc,lower_tail,log_p)

#### Arguments

`x`: observation

`n`: the number of trials

`psuc`: the probability of success in each trial

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the binomial distribution.

#### See also

`R.DBINOM`, `R.PBINOM`.

R.QCAUCHY

### `R.QCAUCHY`

probability quantile function of the Cauchy distribution

    R.QCAUCHY(p,location,scale,lower_tail,log_p)

#### Arguments

`p`: probability

`location`: the center of the distribution

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the Cauchy distribution.

#### See also

`R.DCAUCHY`, `R.PCAUCHY`.

R.QCHISQ

### `R.QCHISQ`

probability quantile function of the chi-square distribution

    R.QCHISQ(p,df,lower_tail,log_p)

#### Arguments

`p`: probability

`df`: the number of degrees of freedom of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the chi-square distribution.

#### OpenDocument Format (ODF) Compatibility

A two argument invocation `R.QCHISQ`(`p`,`df`) is exported to OpenFormula as CHISQINV(`p`,`df`).

#### See also

`R.DCHISQ`, `R.PCHISQ`.

R.QEXP

### `R.QEXP`

probability quantile function of the exponential distribution

    R.QEXP(p,scale,lower_tail,log_p)

#### Arguments

`p`: probability

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the exponential distribution.

#### See also

`R.DEXP`, `R.PEXP`.

R.QF

### `R.QF`

probability quantile function of the F distribution

    R.QF(x,n1,n2,lower_tail,log_p)

#### Arguments

`x`: observation

`n1`: the first number of degrees of freedom of the distribution

`n2`: the second number of degrees of freedom of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the F distribution.

#### See also

`R.DF`, `R.PF`.

R.QGAMMA

### `R.QGAMMA`

probability quantile function of the gamma distribution

    R.QGAMMA(p,shape,scale,lower_tail,log_p)

#### Arguments

`p`: probability

`shape`: the shape parameter of the distribution

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the gamma distribution.

#### See also

`R.DGAMMA`, `R.PGAMMA`.

R.QGEOM

### `R.QGEOM`

probability quantile function of the geometric distribution

    R.QGEOM(p,psuc,lower_tail,log_p)

#### Arguments

`p`: probability

`psuc`: the probability of success in each trial

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the geometric distribution.

#### See also

`R.DGEOM`, `R.PGEOM`.

R.QHYPER

### `R.QHYPER`

probability quantile function of the hypergeometric distribution

    R.QHYPER(p,r,b,n,lower_tail,log_p)

#### Arguments

`p`: probability

`r`: the number of red balls

`b`: the number of black balls

`n`: the number of balls drawn

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the hypergeometric distribution.

#### See also

`R.DHYPER`, `R.PHYPER`.

R.QLNORM

### `R.QLNORM`

probability quantile function of the log-normal distribution

    R.QLNORM(x,logmean,logsd,lower_tail,log_p)

#### Arguments

`x`: observation

`logmean`: mean of the underlying normal distribution

`logsd`: standard deviation of the underlying normal distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the log-normal distribution.

#### See also

`R.DLNORM`, `R.PLNORM`.

R.QNBINOM

### `R.QNBINOM`

probability quantile function of the negative binomial distribution

    R.QNBINOM(p,n,psuc,lower_tail,log_p)

#### Arguments

`p`: probability

`n`: the number of trials

`psuc`: the probability of success in each trial

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the negative binomial distribution.

#### See also

`R.DNBINOM`, `R.PNBINOM`.

R.QNORM

### `R.QNORM`

probability quantile function of the normal distribution

    R.QNORM(p,mu,sigma,lower_tail,log_p)

#### Arguments

`p`: probability

`mu`: mean of the distribution

`sigma`: standard deviation of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the normal distribution.

#### See also

`R.DNORM`, `R.PNORM`.

R.QPOIS

### `R.QPOIS`

probability quantile function of the Poisson distribution

    R.QPOIS(p,lambda,lower_tail,log_p)

#### Arguments

`p`: probability

`lambda`: the mean of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the Poisson distribution.

#### See also

`R.DPOIS`, `R.PPOIS`.

R.QT

### `R.QT`

probability quantile function of the Student t distribution

    R.QT(p,n,lower_tail,log_p)

#### Arguments

`p`: probability

`n`: the number of degrees of freedom of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the Student t distribution.

#### See also

`R.DT`, `R.PT`.

R.QWEIBULL

### `R.QWEIBULL`

probability quantile function of the Weibull distribution

    R.QWEIBULL(p,shape,scale,lower_tail,log_p)

#### Arguments

`p`: probability

`shape`: the shape parameter of the distribution

`scale`: the scale parameter of the distribution

`lower_tail`: if true (the default), the lower tail of the distribution is considered

`log_p`: if true, log of the probability is used

#### Description

This function returns the probability quantile function, i.e., the inverse of the cumulative distribution function, of the Weibull distribution.

#### See also

`R.DWEIBULL`, `R.PWEIBULL`.

RANK

### `RANK`

rank of a number in a list of numbers

    RANK(x,ref,order)

#### Arguments

`x`: number whose rank you want to find

`ref`: list of numbers

`order`: 0 (descending order) or non-zero (ascending order); defaults to 0

#### Note

In case of a tie, `RANK` returns the largest possible rank.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`PERCENTRANK`, `RANK.AVG`.

RANK.AVG

### `RANK.AVG`

rank of a number in a list of numbers

    RANK.AVG(x,ref,order)

#### Arguments

`x`: number whose rank you want to find

`ref`: list of numbers

`order`: 0 (descending order) or non-zero (ascending order); defaults to 0

#### Note

In case of a tie, RANK returns the average rank.

#### Microsoft Excel Compatibility

This function is Excel 2010 compatible.

#### See also

`PERCENTRANK`, `RANK`.

RAYLEIGH

### `RAYLEIGH`

probability density function of the Rayleigh distribution

    RAYLEIGH(x,sigma)

#### Arguments

`x`: number

`sigma`: scale parameter

#### See also

`RANDRAYLEIGH`.

RAYLEIGHTAIL

### `RAYLEIGHTAIL`

probability density function of the Rayleigh tail distribution

    RAYLEIGHTAIL(x,a,sigma)

#### Arguments

`x`: number

`a`: lower limit

`sigma`: scale parameter

#### See also

`RANDRAYLEIGHTAIL`.

RSQ

### `RSQ`

square of the Pearson correlation coefficient of the paired set of data

    RSQ(array1,array2)

#### Arguments

`array1`: first component values

`array2`: second component values

#### Description

Strings and empty cells are simply ignored.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CORREL`, `COVAR`, `INTERCEPT`, `LINEST`, `LOGEST`, `PEARSON`, `SLOPE`, `STEYX`, `TREND`.

SFTEST

### `SFTEST`

Shapiro-Francia Test of Normality

    SFTEST(x)

#### Arguments

`x`: array of sample values

#### Description

This function returns an array with the first row giving the p-value of the Shapiro-Francia Test, the second row the test statistic of the test, and the third the number of observations in the sample.

#### Note

If there are less than 5 or more than 5000 sample values, `SFTEST` returns #VALUE!

#### See also

`CHITEST`, `ADTEST`, `LKSTEST`, `CVMTEST`.

SKEW

### `SKEW`

unbiased estimate for skewness of a distribution

    SKEW(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Strings and empty cells are simply ignored.

#### Note

This is only meaningful if the underlying distribution really has a third moment. The skewness of a symmetric (e.g., normal) distribution is zero. If less than three numbers are given, this function returns a #DIV/0! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `VAR`, `SKEWP`, `KURT`.

SKEWP

### `SKEWP`

population skewness of a data set

    SKEWP(number1,number2,…)

#### Arguments

`number1`: first value

`number2`: second value

#### Description

Strings and empty cells are simply ignored.

#### Note

If less than two numbers are given, `SKEWP` returns a #DIV/0! error.

#### See also

`AVERAGE`, `VARP`, `SKEW`, `KURTP`.

SLOPE

### `SLOPE`

the slope of a linear regression line

    SLOPE(known_ys,known_xs)

#### Arguments

`known_ys`: known y-values

`known_xs`: known x-values

#### Note

If `known_xs` or `known_ys` contains no data entries or different number of data entries, this function returns a #N/A error. If the variance of the `known_xs` is zero, this function returns #DIV/0 error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`STDEV`, `STDEVPA`.

SMALL

### `SMALL`

k
-th smallest value in a data set

    SMALL(data,k)

#### Arguments

`data`: data set

`k`: which value to find

#### Note

If data set is empty this function returns a #NUM! error. If `k` <= 0 or `k` is greater than the number of data items given this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`PERCENTILE`, `PERCENTRANK`, `QUARTILE`, `LARGE`.

SSMEDIAN

### `SSMEDIAN`

median for grouped data

    SSMEDIAN(array,interval)

#### Arguments

`array`: data set

`interval`: length of each grouping interval, defaults to 1

#### Description

The data are assumed to be grouped into intervals of width `interval`. Each data point in `array` is the midpoint of the interval containing the true value. The median is calculated by interpolation within the median interval (the interval containing the median value), assuming that the true values within that interval are distributed uniformly:

median = L + `interval`*(N/2 - CF)/F

where:

L = the lower limit of the median interval

N = the total number of data points

CF = the number of data points below the median interval

F = the number of data points in the median interval

#### Note

If `array` is empty, this function returns a #NUM! error. If `interval` <= 0, this function returns a #NUM! error. `SSMEDIAN` does not check whether the data points are at least `interval` apart.

#### See also

`MEDIAN`.

STANDARDIZE

### `STANDARDIZE`

z-score of a value

    STANDARDIZE(x,mean,stddev)

#### Arguments

`x`: value

`mean`: mean of the original distribution

`stddev`: standard deviation of the original distribution

#### Note

If `stddev` is 0 this function returns a #DIV/0! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`.

STDEV

### `STDEV`

sample standard deviation of the given sample

    STDEV(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

`STDEV` is also known as the N-1-standard deviation.

To obtain the population standard deviation of a whole population use STDEVP.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `DSTDEV`, `DSTDEVP`, `STDEVA`, `STDEVPA`, `VAR`.

STDEVA

### `STDEVA`

sample standard deviation of the given sample

    STDEVA(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

`STDEVA` is also known as the N-1-standard deviation.

To obtain the population standard deviation of a whole population use STDEVPA.

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`STDEV`, `STDEVPA`.

STDEVP

### `STDEVP`

population standard deviation of the given population

    STDEVP(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

This is also known as the N-standard deviation

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`STDEV`, `STDEVA`, `STDEVPA`.

STDEVPA

### `STDEVPA`

population standard deviation of an entire population

    STDEVPA(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

This is also known as the N-standard deviation

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`STDEVA`, `STDEVP`.

STEYX

### `STEYX`

standard error of the predicted y-value in the regression

    STEYX(known_ys,known_xs)

#### Arguments

`known_ys`: known y-values

`known_xs`: known x-values

#### Note

If `known_ys` and `known_xs` are empty or have a different number of arguments then this function returns a #N/A error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`PEARSON`, `RSQ`, `SLOPE`.

SUBTOTAL

### `SUBTOTAL`

the subtotal of the given list of arguments

    SUBTOTAL(function_nbr,ref1,ref2,…)

#### Arguments

`function_nbr`: determines which function to use according to the following table:

1 AVERAGE

2 COUNT

3 COUNTA

4 MAX

5 MIN

6 PRODUCT

7 STDEV

8 STDEVP

9 SUM

10 VAR

11 VARP

`ref1`: first value

`ref2`: second value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`COUNT`, `SUM`.

TDIST

### `TDIST`

survival function of the Student t-distribution

    TDIST(x,dof,tails)

#### Arguments

`x`: number

`dof`: number of degrees of freedom

`tails`: 1 or 2

#### Description

The survival function is 1 minus the cumulative distribution function.

This function is Excel compatible for non-negative `x`.

#### Note

If `dof` < 1 this function returns a #NUM! error. If `tails` is neither 1 or 2 this function returns a #NUM! error. The parameterization of this function is different from what is used for, e.g., NORMSDIST. This is a common source of mistakes, but necessary for compatibility.

#### See also

`TINV`, `TTEST`.

TINV

### `TINV`

inverse of the survival function of the Student t-distribution

    TINV(p,dof)

#### Arguments

`p`: probability

`dof`: number of degrees of freedom

#### Description

The survival function is 1 minus the cumulative distribution function.

#### Note

If `p` < 0 or `p` > 1 or `dof` < 1 this function returns a #NUM! error. The parameterization of this function is different from what is used for, e.g., NORMSINV. This is a common source of mistakes, but necessary for compatibility.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TDIST`, `TTEST`.

TREND

### `TREND`

estimates future values of a given data set using a least squares approximation

    TREND(known_ys,known_xs,new_xs,affine)

#### Arguments

`known_ys`: vector of values of dependent variable

`known_xs`: array of values of independent variables, defaults to a single vector {1,…,n}

`new_xs`: array of x-values for which to estimate the y-values; defaults to `known_xs`

`affine`: if true, the model contains a constant term, defaults to true

#### Note

If the length of `known_ys` does not match the corresponding length of `known_xs`, this function returns a #NUM! error.

#### See also

`LINEST`.

TRIMMEAN

### `TRIMMEAN`

mean of the interior of a data set

    TRIMMEAN(ref,fraction)

#### Arguments

`ref`: list of numbers whose mean you want to calculate

`fraction`: fraction of the data set excluded from the mean

#### Description

If `fraction`=0.2 and the data set contains 40 numbers, 8 numbers are trimmed from the data set (40 x 0.2): the 4 largest and the 4 smallest. To avoid a bias, the number of points to be excluded is always rounded down to the nearest even number.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`AVERAGE`, `GEOMEAN`, `HARMEAN`, `MEDIAN`, `MODE`.

TTEST

### `TTEST`

p-value for a hypothesis test comparing the means of two populations using the Student t-distribution

    TTEST(array1,array2,tails,type)

#### Arguments

`array1`: sample from the first population

`array2`: sample from the second population

`tails`: number of tails to consider

`type`: Type of test to perform. 1 indicates a test for paired variables, 2 a test of unpaired variables with equal variances, and 3 a test of unpaired variables with unequal variances

#### Note

If the data sets contain a different number of data points and the test is paired (`type` one), `TTEST` returns the #N/A error. `tails` and `type` are truncated to integers. If `tails` is not one or two, this function returns a #NUM! error. If `type` is any other than one, two, or three, this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FDIST`, `FINV`.

VAR

### `VAR`

sample variance of the given sample

    VAR(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

`VAR` is also known as the N-1-variance.

#### Note

Since the N-1-variance includes Bessel's correction, whereas the N-variance calculated by VARPA or VARP does not, under reasonable conditions the N-1-variance is an unbiased estimator of the variance of the population from which the sample is drawn.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`VARP`, `STDEV`, `VARA`.

VARA

### `VARA`

sample variance of the given sample

    VARA(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

`VARA` is also known as the N-1-variance.

To get the true variance of a complete population use VARPA.

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.

#### Note

Since the N-1-variance includes Bessel's correction, whereas the N-variance calculated by VARPA or VARP does not, under reasonable conditions the N-1-variance is an unbiased estimator of the variance of the population from which the sample is drawn.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`VAR`, `VARPA`.

VARP

### `VARP`

variance of an entire population

    VARP(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

`VARP` is also known as the N-variance.

#### See also

`AVERAGE`, `DVAR`, `DVARP`, `STDEV`, `VAR`.

VARPA

### `VARPA`

variance of an entire population

    VARPA(area1,area2,…)

#### Arguments

`area1`: first cell area

`area2`: second cell area

#### Description

`VARPA` is also known as the N-variance.

Numbers, text and logical values are included in the calculation too. If the cell contains text or the argument evaluates to FALSE, it is counted as value zero (0). If the argument evaluates to TRUE, it is counted as one (1). Note that empty cells are not counted.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`VARA`, `VARP`.

WEIBULL

### `WEIBULL`

probability density or cumulative distribution function of the Weibull distribution

    WEIBULL(x,alpha,beta,cumulative)

#### Arguments

`x`: number

`alpha`: scale parameter

`beta`: scale parameter

`cumulative`: whether to evaluate the density function or the cumulative distribution function

#### Description

If the `cumulative` boolean is true it will return: 1 - exp (-(`x`/`beta`)^`alpha`),otherwise it will return (`alpha`/`beta`^`alpha`) * `x`^(`alpha`-1) * exp(-(`x`/`beta`^`alpha`)).

#### Note

If `x` < 0 this function returns a #NUM! error. If `alpha` <= 0 or `beta` <= 0 this function returns a #NUM! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`POISSON`.

ZTEST

### `ZTEST`

the probability of observing a sample mean as large as or larger than the mean of the given sample

    ZTEST(ref,x,stddev)

#### Arguments

`ref`: data set (sample)

`x`: population mean

`stddev`: population standard deviation, defaults to the sample standard deviation

#### Description

`ZTEST` calulates the probability of observing a sample mean as large as or larger than the mean of the given sample for samples drawn from a normal distribution with mean `x` and standard deviation `stddev`.

#### Note

If `ref` contains less than two data items `ZTEST` returns #DIV/0! error.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`CONFIDENCE`, `NORMDIST`, `NORMINV`, `NORMSDIST`, `NORMSINV`, `STANDARDIZE`.

## String

ASC

### `ASC`

text with full-width katakana and ASCII characters converted to half-width

    ASC(text)

#### Arguments

`text`: string

#### Description

`ASC` converts full-width katakana and ASCII characters to half-width equivalent characters, copying all others.

The distinction between half-width and full-width characters is described in http://www.unicode.org/reports/tr11/.

#### Note

While in obsolete encodings `ASC` used to translate between 2-byte and 1-byte characters, this is not the case in UTF-8.

#### Microsoft Excel Compatibility

For most strings, this function has the same effect as in Excel.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`JIS`.

CHAR

### `CHAR`

the CP1252 (Windows-1252) character for the code point `x`

    CHAR(x)

#### Arguments

`x`: code point

#### Description

`CHAR`(`x`) returns the CP1252 (Windows-1252) character with code `x`.

`x` must be in the range 1 to 255.

CP1252 (Windows-1252) is also known as the "ANSI code page", but it is not an ANSI standard.

CP1252 (Windows-1252) is based on an early draft of ISO-8859-1, and contains all of its printable characters. It also contains all of ISO-8859-15's printable characters (but partially at different positions.)

This function is Excel compatible.

#### Note

In CP1252 (Windows-1252), 129, 141, 143, 144, and 157 do not have matching characters. For `x` from 1 to 255 except 129, 141, 143, 144, and 157 we have CODE(`CHAR`(`x`))=`x`.

#### See also

`CODE`.

CLEAN

### `CLEAN`

text
with any non-printable characters removed

    CLEAN(text)

#### Arguments

`text`: string

#### Description

`CLEAN` removes non-printable characters from its argument leaving only regular characters and white-space.

#### Microsoft Excel Compatibility

This function is Excel compatible.

CODE

### `CODE`

the CP1252 (Windows-1252) code point for the character `c`

    CODE(c)

#### Arguments

`c`: character

#### Description

`c` must be a valid CP1252 (Windows-1252) character.

CP1252 (Windows-1252) is also known as the "ANSI code page", but it is not an ANSI standard.

CP1252 (Windows-1252) is based on an early draft of ISO-8859-1, and contains all of its printable characters (but partially at different positions.)

This function is Excel compatible.

#### Note

In CP1252 (Windows-1252), 129, 141, 143, 144, and 157 do not have matching characters. For `x` from 1 to 255 except 129, 141, 143, 144, and 157 we have `CODE`(CHAR(`x`))=`x`.

#### See also

`CHAR`.

CONCATENATE

### `CONCATENATE`

the concatenation of the strings `s1`, `s2`,…

    CONCATENATE(s1,s2,…)

#### Arguments

`s1`: first string

`s2`: second string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LEFT`, `MID`, `RIGHT`.

DOLLAR

### `DOLLAR`

num
formatted as currency

    DOLLAR(num,decimals)

#### Arguments

`num`: number

`decimals`: decimals

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FIXED`, `TEXT`, `VALUE`.

EXACT

### `EXACT`

TRUE if `string1` is exactly equal to `string2`

    EXACT(string1,string2)

#### Arguments

`string1`: first string

`string2`: second string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LEN`, `SEARCH`, `DELTA`.

FIND

### `FIND`

first position of `string1` in `string2` following position `start`

    FIND(string1,string2,start)

#### Arguments

`string1`: search string

`string2`: search field

`start`: starting position, defaults to 1

#### Note

This search is case-sensitive.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`EXACT`, `LEN`, `MID`, `SEARCH`.

FINDB

### `FINDB`

first byte position of `string1` in `string2` following byte position `start`

    FINDB(string1,string2,start)

#### Arguments

`string1`: search string

`string2`: search field

`start`: starting byte position, defaults to 1

#### Note

This search is case-sensitive.

#### Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

#### OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

#### See also

`FIND`, `LEFTB`, `RIGHTB`, `LENB`, `LEFT`, `MID`, `RIGHT`, `LEN`.

FIXED

### `FIXED`

formatted string representation of `num`

    FIXED(num,decimals,no_commas)

#### Arguments

`num`: number

`decimals`: number of decimals

`no_commas`: TRUE if no thousand separators should be used, defaults to FALSE

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`TEXT`, `VALUE`, `DOLLAR`.

JIS

### `JIS`

text with half-width katakana and ASCII characters converted to full-width

    JIS(text)

#### Arguments

`text`: original text

#### Description

`JIS` converts half-width katakana and ASCII characters to full-width equivalent characters, copying all others.

The distinction between half-width and full-width characters is described in http://www.unicode.org/reports/tr11/.

#### Note

While in obsolete encodings `JIS` used to translate between 1-byte and 2-byte characters, this is not the case in UTF-8.

#### Microsoft Excel Compatibility

For most strings, this function has the same effect as in Excel.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`ASC`.

LEFT

### `LEFT`

the first `num_chars` characters of the string `s`

    LEFT(s,num_chars)

#### Arguments

`s`: the string

`num_chars`: the number of characters to return (defaults to 1)

#### Note

If the string `s` is in a right-to-left script, the returned first characters are from the right of the string.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`MID`, `RIGHT`, `LEN`, `MIDB`, `RIGHTB`, `LENB`.

LEFTB

### `LEFTB`

the first characters of the string `s` comprising at most `num_bytes` bytes

    LEFTB(s,num_bytes)

#### Arguments

`s`: the string

`num_bytes`: the maximum number of bytes to return (defaults to 1)

#### Note

The semantics of this function is subject to change as various applications implement it. If the string is in a right-to-left script, the returned first characters are from the right of the string.

#### Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

#### OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

#### See also

`MIDB`, `RIGHTB`, `LENB`, `LEFT`, `MID`, `RIGHT`, `LEN`.

LEN

### `LEN`

the number of characters of the string `s`

    LEN(s)

#### Arguments

`s`: the string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CHAR`, `CODE`, `LENB`.

LENB

### `LENB`

the number of bytes in the string `s`

    LENB(s)

#### Arguments

`s`: the string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CHAR`, `CODE`, `LEN`.

LOWER

### `LOWER`

a lower-case version of the string `text`

    LOWER(text)

#### Arguments

`text`: string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`UPPER`.

MID

### `MID`

the substring of the string `s` starting at position `position` consisting of `length` characters

    MID(s,position,length)

#### Arguments

`s`: the string

`position`: the starting position

`length`: the number of characters to return

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`LEFT`, `RIGHT`, `LEN`, `LEFTB`, `MIDB`, `RIGHTB`, `LENB`.

MIDB

### `MIDB`

the characters following the first `start_pos` bytes comprising at most `num_bytes` bytes

    MIDB(s,start_pos,num_bytes)

#### Arguments

`s`: the string

`start_pos`: the number of the byte with which to start (defaults to 1)

`num_bytes`: the maximum number of bytes to return (defaults to 1)

#### Note

The semantics of this function is subject to change as various applications implement it.

#### Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

#### OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

#### See also

`LEFTB`, `RIGHTB`, `LENB`, `LEFT`, `MID`, `RIGHT`, `LEN`.

NUMBERVALUE

### `NUMBERVALUE`

numeric value of `text`

    NUMBERVALUE(text,separator)

#### Arguments

`text`: string

`separator`: decimal separator

#### Note

If `text` does not look like a decimal number, `NUMBERVALUE` returns the value VALUE would return (ignoring the given `separator`).

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`VALUE`.

PROPER

### `PROPER`

text
with initial of each word capitalised

    PROPER(text)

#### Arguments

`text`: string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LOWER`, `UPPER`.

REPLACE

### `REPLACE`

string `old` with `num` characters starting at `start` replaced by `new`

    REPLACE(old,start,num,new)

#### Arguments

`old`: original text

`start`: starting position

`num`: number of characters to be replaced

`new`: replacement string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`MID`, `SEARCH`, `SUBSTITUTE`, `TRIM`.

REPLACEB

### `REPLACEB`

string `old` with up to `num` bytes starting at `start` replaced by `new`

    REPLACEB(old,start,num,new)

#### Arguments

`old`: original text

`start`: starting byte position

`num`: number of bytes to be replaced

`new`: replacement string

#### Description

`REPLACEB` replaces the string of valid unicode characters starting at the byte `start` and ending at `start`+`num`-1 with the string `new`.

#### Note

The semantics of this function is subject to change as various applications implement it.

#### Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

#### OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

#### See also

`MID`, `SEARCH`, `SUBSTITUTE`, `TRIM`.

REPT

### `REPT`

num
repetitions of string `text`

    REPT(text,num)

#### Arguments

`text`: string

`num`: non-negative integer

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CONCATENATE`.

RIGHT

### `RIGHT`

the last `num_chars` characters of the string `s`

    RIGHT(s,num_chars)

#### Arguments

`s`: the string

`num_chars`: the number of characters to return (defaults to 1)

#### Note

If the string `s` is in a right-to-left script, the returned last characters are from the left of the string.

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### OpenDocument Format (ODF) Compatibility

This function is OpenFormula compatible.

#### See also

`LEFT`, `MID`, `LEN`, `LEFTB`, `MIDB`, `RIGHTB`, `LENB`.

RIGHTB

### `RIGHTB`

the last characters of the string `s` comprising at most `num_bytes` bytes

    RIGHTB(s,num_bytes)

#### Arguments

`s`: the string

`num_bytes`: the maximum number of bytes to return (defaults to 1)

#### Note

The semantics of this function is subject to change as various applications implement it. If the string `s` is in a right-to-left script, the returned last characters are from the left of the string.

#### Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

#### OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

#### See also

`LEFTB`, `MIDB`, `LENB`, `LEFT`, `MID`, `RIGHT`, `LEN`.

SEARCH

### `SEARCH`

the location of the `search` string within `text` after position `start`

    SEARCH(search,text,start)

#### Arguments

`search`: search string

`text`: search field

`start`: starting position, defaults to 1

#### Description

`search` may contain wildcard characters (*) and question marks (?). A question mark matches any single character, and a wildcard matches any string including the empty string. To search for * or ?, precede the symbol with ~.

#### Note

This search is not case sensitive. If `search` is not found, `SEARCH` returns #VALUE! If `start` is less than one or it is greater than the length of `text`, `SEARCH` returns #VALUE!

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`FIND`, `SEARCHB`.

SEARCHB

### `SEARCHB`

the location of the `search` string within `text` after byte position `start`

    SEARCHB(search,text,start)

#### Arguments

`search`: search string

`text`: search field

`start`: starting byte position, defaults to 1

#### Description

`search` may contain wildcard characters (*) and question marks (?). A question mark matches any single character, and a wildcard matches any string including the empty string. To search for * or ?, precede the symbol with ~.

#### Note

This search is not case sensitive. If `search` is not found, `SEARCHB` returns #VALUE! If `start` is less than one or it is greater than the byte length of `text`, `SEARCHB` returns #VALUE! The semantics of this function is subject to change as various applications implement it.

#### Microsoft Excel Compatibility

While this function is syntactically Excel compatible, the differences in the underlying text encoding will usually yield different results.

#### OpenDocument Format (ODF) Compatibility

While this function is OpenFormula compatible, most of its behavior is, at this time, implementation specific.

#### See also

`FINDB`, `SEARCH`.

SUBSTITUTE

### `SUBSTITUTE`

text
with all occurrences of `old` replaced by `new`

    SUBSTITUTE(text,old,new,num)

#### Arguments

`text`: original text

`old`: string to be replaced

`new`: replacement string

`num`: if `num` is specified and a number only the `num`th occurrence of `old` is replaced

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`REPLACE`, `TRIM`.

T

### `T`

value
if and only if `value` is text, otherwise empty

    T(value)

#### Arguments

`value`: original value

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CELL`, `N`, `VALUE`.

TEXT

### `TEXT`

value
as a string formatted as `format`

    TEXT(value,format)

#### Arguments

`value`: value to be formatted

`format`: desired format

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DOLLAR`, `FIXED`, `VALUE`.

TRIM

### `TRIM`

text
with only single spaces between words

    TRIM(text)

#### Arguments

`text`: string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`CLEAN`, `MID`, `REPLACE`, `SUBSTITUTE`.

UNICHAR

### `UNICHAR`

the Unicode character represented by the Unicode code point `x`

    UNICHAR(x)

#### Arguments

`x`: Unicode code point

#### See also

`CHAR`, `UNICODE`, `CODE`.

UNICODE

### `UNICODE`

the Unicode code point for the character `c`

    UNICODE(c)

#### Arguments

`c`: character

#### See also

`UNICHAR`, `CODE`, `CHAR`.

UPPER

### `UPPER`

an upper-case version of the string `text`

    UPPER(text)

#### Arguments

`text`: string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`LOWER`.

VALUE

### `VALUE`

numeric value of `text`

    VALUE(text)

#### Arguments

`text`: string

#### Microsoft Excel Compatibility

This function is Excel compatible.

#### See also

`DOLLAR`, `FIXED`, `TEXT`.

## Time Series Analysis

FOURIER

### `FOURIER`

Fourier or inverse Fourier transform

    FOURIER(Sequence,Inverse,Separate)

#### Arguments

`Sequence`: the data sequence to be transformed

`Inverse`: if true, the inverse Fourier transform is calculated, defaults to false

`Separate`: if true, the real and imaginary parts are given separately, defaults to false

#### Description

This array function returns the Fourier or inverse Fourier transform of the given data sequence.

The output consists of one column of complex numbers if `Separate` is false and of two columns of real numbers if `Separate` is true.

If `Separate` is true the first output column contains the real parts and the second column the imaginary parts.

#### Note

If `Sequence` is neither an n by 1 nor 1 by n array, this function returns #VALUE!

HPFILTER

### `HPFILTER`

Hodrick Prescott Filter

    HPFILTER(Sequence,λ)

#### Arguments

`Sequence`: the data sequence to be transformed

`λ`: filter parameter λ, defaults to 1600

#### Description

This array function returns the trend and cyclical components obtained by applying the Hodrick Prescott Filter with parameter `λ` to the given data sequence.

The output consists of two columns of numbers, the first containing the trend component, the second the cyclical component.

#### Note

If `Sequence` is neither an n by 1 nor 1 by n array, this function returns #VALUE! If `Sequence` contians less than 6 numerical values, this function returns #VALUE!

INTERPOLATION

### `INTERPOLATION`

interpolated values corresponding to the given abscissa targets

    INTERPOLATION(abscissae,ordinates,targets,interpolation)

#### Arguments

`abscissae`: abscissae of the given data points

`ordinates`: ordinates of the given data points

`targets`: abscissae of the interpolated data

`interpolation`: method of interpolation, defaults to 0 ('linear')

#### Description

The output consists always of one column of numbers.

Possible interpolation methods are:

0: linear;

1: linear with averaging;

2: staircase;

3: staircase with averaging;

4: natural cubic spline;

5: natural cubic spline with averaging.

#### Note

The `abscissae` should be given in increasing order. If the `abscissae` is not in increasing order the `INTERPOLATION` function is significantly slower. If any two `abscissae` values are equal an error is returned. If any of interpolation methods 1 ('linear with averaging'), 3 ('staircase with averaging'), and 5 ('natural cubic spline with averaging') is used, the number of returned values is one less than the number of targets and the target values must be given in increasing order. The values returned are the average heights of the interpolation function on the intervals determined by consecutive target values. Strings and empty cells in `abscissae` and `ordinates` are ignored. If several target data are provided they must be in the same column in consecutive cells.

#### See also

`PERIODOGRAM`.

PERIODOGRAM

### `PERIODOGRAM`

periodogram of the given data

    PERIODOGRAM(ordinates,filter,abscissae,interpolation,number)

#### Arguments

`ordinates`: ordinates of the given data

`filter`: windowing function to be used, defaults to no filter

`abscissae`: abscissae of the given data, defaults to regularly spaced abscissae

`interpolation`: method of interpolation, defaults to none

`number`: number of interpolated data points

#### Description

If an interpolation method is used, the number of returned values is one less than the number of targets and the targets values must be given in increasing order.

The output consists always of one column of numbers.

Possible interpolation methods are:

0: linear;

1: linear with averaging;

2: staircase;

3: staircase with averaging;

4: natural cubic spline;

5: natural cubic spline with averaging.

Possible window functions are:

0: no filter (rectangular window)

1: Bartlett (triangular window)

2: Hahn (cosine window)

3: Welch (parabolic window)

#### Note

Strings and empty cells in `abscissae` and `ordinates` are ignored. If several target data are provided they must be in the same column in consecutive cells.

#### See also

`INTERPOLATION`.