• Sample Sheets
  • Online Excel Viewer

Functions Reference



Function Name:


ABS
Returns the absolute value of a number.
Syntax
ABS(number)
Syntax Description
number is the number whose absolute value is to be calculated. The absolute value of a number is its value without the +/- sign.


ACCRINT
Calculates the accrued interest for a security with periodic interest payments.
Syntax
ACCRINT(issue; first_interest; settlement; rate; par; frequency; basis)
Syntax Description
issue: the issue date of the security.
first_interest: the first interest date of the security.
settlement: the date at which the interest accrued up until then is to be calculated.
rate: the annual nominal rate of interest (coupon interest rate)
par: the par value of the security.
frequency: the number of interest payments per year (1, 2 or 4).
basis: is chosen from a list of options and indicates how the year is to be calculated. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


ACCRINTM
Calculates the accrued interest for a security that pays at maturity.
Syntax
ACCRINTM(issue; settlement; rate; par; basis)
Syntax Description
issue: the issue date of the security.
settlement: the date at which the interest accrued up until then is to be calculated.
rate: the annual nominal rate of interest (coupon interest rate)
par: the par value of the security.
basis: is chosen from a list of options and indicates how the year is to be calculated. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


ACOS
Returns the inverse cosine (the arccosine) of a number.
Syntax
ACOS(number)
Syntax Description
Returns the inverse trigonometric cosine of number, in other words the angle (in radians) whose cosine is number. The angle returned is between 0 and PI.
To return the angle in degrees, use the DEGREES function.


ACOSH
Returns the inverse hyperbolic cosine of a number.
Syntax
ACOSH(number)
Syntax Description
Returns the inverse hyperbolic cosine of number, in other words the number whose hyperbolic cosine is number.
number must be greater than or equal to 1.


ACOT
Returns the inverse cotangent (the arccotangent) of a number.
Syntax
ACOT(number)
Syntax Description
Returns the inverse trigonometric cotangent of number, in other words the angle (in radians) whose cotangent is number. The angle returned is between 0 and PI.
To return the angle in degrees, use the DEGREES function.


ACOTH
Returns the inverse hyperbolic cotangent of the given number.
Syntax
ACOTH(number)
Syntax Description
Returns the inverse hyperbolic cotangent of number, in other words the number whose hyperbolic cotangent is number.
An error results if number is between -1 and 1 inclusive.


ADDRESS
Returns a cell reference as text, given row and column numbers.
Syntax
ADDRESS(row; column; mode; ref_type; sheet)
Syntax Description
row is a number specifying the row.
column is a number (not a letter) specifying the column.
mode (an optional number) determines whether the cell address is absolute or relative. If omitted, it is assumed to be 1.
ref_type (optional) is a logical value that specifies the A1 or R1C1 reference style.
sheet is an optional text string specifying the sheet.


AMORDEGRC
Returns depreciation for a period using degressive depreciation (French system).
Syntax
AMORDEGRC(cost; purchase_date; first_period_end; salvage; period; rate; basis)
Syntax Description
cost: the acquisition cost.
purchase_date: the date of acquisition.
first_period_end: the end date of the first depreciation period.
salvage: the salvage value at the end of life.
period: the period for which to calculate depreciation. 0 is the initial period (from purchase_date to first_period_end.
rate: the rate of depreciation.
basis: is chosen from a list of options and indicates how the year is to be calculated. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


AMORLINC
Returns depreciation for a period using linear depreciation (French system).
Syntax
AMORLINC(cost; purchase_date; first_period_end; salvage; period; rate; basis)
Syntax Description
cost: the acquisition cost.
purchase_date: the date of acquisition.
first_period_end: the end date of the first depreciation period.
salvage: the salvage value at the end of life.
period: the period for which to calculate depreciation. 0 is the initial period (from purchase_date to first_period_end).
rate: the rate of depreciation.
basis: is chosen from a list of options and indicates how the year is to be calculated. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


AND
Returns TRUE if all the arguments are considered TRUE, and FALSE otherwise.
Syntax
AND(argument1; argument2 ...argument30)
Syntax Description
 AND tests every value (as an argument, or in each referenced cell), and returns TRUE if they are all TRUE. Any value which is a non-zero number or text is considered to be TRUE.


ARABIC
Returns an Arabic number (eg 14), given a Roman number (eg XIV).
Syntax
ARABIC(text)
Syntax Description
Returns the Roman numeral text as a number, limited to the range 1-3999.


AREAS
Returns the number of areas in a given reference.
Syntax
AREAS(reference)
Syntax Description
reference may consist of multiple ranges.
AREAS
returns the count of these ranges.


ASIN
Returns the inverse sine (the arcsine) of a number.
Syntax
ASIN(number)
Syntax Description
Returns the inverse trigonometric sine of number, in other words the angle (in radians) whose sine is number. The angle returned is between -PI/2 and +PI/2.
To return the angle in degrees, use the DEGREES function.


ASINH
Returns the inverse hyperbolic sine of a number.
Syntax
ASINH(number)
Syntax Description
Returns the inverse hyperbolic sine of number, in other words the number whose hyperbolic sine is number.


ATAN
Returns the inverse tangent (the arctangent) of a number.
Syntax
ATAN(number)
Syntax Description
Returns the inverse trigonometric tangent of number, in other words the angle (in radians) whose tangent is number. The angle returned is between -PI/2 and PI/2.
To return the angle in degrees, use the DEGREES function.


ATAN2
Returns the inverse tangent (the arctangent) for specified x and y coordinates.
Syntax
ATAN2(x_coord; y_coord)
Syntax Description
x_coord is the value of the x coordinate.
y_coord is the value of the y coordinate.
ATAN2 returns the inverse trigonometric tangent, that is, the angle (in radians) between the x-axis and a line from point x_coord, y_coord to the origin. The angle returned is between -PI and PI.
To return the angle in degrees, use the DEGREES function.


ATANH
Returns the inverse hyperbolic tangent of a number.
Syntax
ATANH(number)
Syntax Description
Returns the inverse hyperbolic tangent of number, in other words the number whose hyperbolic tangent is number.
number must obey the condition -1 < number < 1.


AVEDEV
Returns the average of the absolute deviations of values from their mean.
Syntax
AVEDEV(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.
AVEDEV calculates the mean of the numbers, then the absolute (positive signed) deviation of each number from that mean. 


AVERAGE
Returns the average of the arguments, ignoring text.
Syntax
AVERAGE(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.


AVERAGEA
Returns the average of the arguments, including text (valued as 0).
Syntax
AVERAGEA(value1; value2; ... value30)
Syntax Description
value1 to value30 are up to 30 values or ranges, which may include numbers, text and logical values.
Text is evaluated as 0. Logical values are evaluated as 1 ( TRUE) and 0 ( FALSE).


B
Calculates probabilities for a binomial distribution.
Syntax
B(n; p; k; k2)
Syntax Description
With n independent trials, each with a probability p of success, B returns the probability that the number of successes will be between k and k2 inclusive, or if k2 is omitted the probability that the number of successes will be exactly k.


BASE
Returns a text representation of a number, in a specified base radix.
Syntax
BASE(number; radix; minlength)
Syntax Description
Converts number (a positive integer) to text, with the base radix radix (an integer between 2 and 36), using characters 0-9 and A-Z.
minlength (optional) specifies the minimum number of characters returned; zeroes are added on the left if necessary.


BESSELI
Calculates the modified Bessel function of the first kind.
Syntax
BESSELI(x; n)
Syntax Description
Returns the modified Bessel function of the first kind, of order n, evaluated at x.


BESSELJ
Calculates the Bessel function of the first kind.
Syntax
BESSELJ(x; n)
Syntax Description
Returns the Bessel function of the first kind, of order n, evaluated at x.


BESSELK
Calculates the modified Bessel function of the second kind.
Syntax
BESSELK(x; n)
Syntax Description
Returns the modifed Bessel function of the second kind, of order n, evaluated at x.


BESSELY
Calculates the Bessel function of the second kind (the Neumann or Weber function).
Syntax
BESSELY(x; n)
Syntax Description
Returns the Bessel function of the second kind, of order n, evaluated at x.


BETADIST
Calculates the cumulative distribution function or the probability density function of a beta distribution.
Syntax
BETADIST(x; α; β; a; b; cumulative)
Syntax Description
X is the number, at which you will evaluate the Beta distribution.
α and β are parameters controlling the shape of the distribution.
a and b are optional parameters which default (if omitted) to 0 and 1.
cumulative is an optional, logical parameter which defaults to TRUE() if omitted.


BETAINV
Calculates the inverse of the BETADIST function.
Syntax
BETAINV(p; α; β;  a; b)
Syntax Description
The beta distribution is a family of continuous probability distributions, defined for an interval a to b, where  are
α and β parameters controlling the shape of the distribution.
BETAINV returns the value n, lying between a and b, such that BETADIST(n; α; β; a; b) is p.
a and b are optional parameters which default (if omitted) to 0 and 1.


BIN2DEC
Converts a binary number to decimal.
Syntax
BIN2DEC(binarynumber)
Syntax Description
Returns a (decimal) number, given binarynumber, which may be text, or a number containing only digits 1 and 0 (thus the number appears to be binary although it is not).
binarynumber may have up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal)


BIN2HEX
Converts a binary number to hexadecimal.
Syntax
BIN2HEX(binarynumber; numdigits)
Syntax Description
Returns text representing a hexadecimal number, given binarynumber, which may be text, or a number containing only digits 1 and 0 (thus the number appears to be binary although it is not).
binarynumber may have up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal)
numdigits is an optional number specifying the number of digits to return.
If binarynumber is negative, BIN2HEX returns ten hexadecimal digits, representing the hexadecimal number in twos complement form.


BIN2OCT
Converts a binary number to octal.
Syntax
BIN2OCT(binarynumber)
Syntax Description
Returns text representing a octal number, given binarynumber, which may be text, or a number containing only digits 1 and 0 (thus the number appears to be binary although it is not).
binarynumber may have up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal)
If binarynumber is negative, BIN2OCT returns ten octal digits, representing the octal number in two's complement form.


BINOMDIST
Calculates probabilities for a binomial distribution.
Syntax
BINOMDIST(k; n; p; mode)
Syntax Description
With n independent trials, each with a probability p of success, BINOMDIST returns the probability that the number of successes will be
Exactly k if mode is 0.
Up to (and including) k if mode is 1.
In other words, BINOMDIST returns the probability mass function if mode is 0, and the cumulative probability function if mode is 1.


CEILING
Returns a number rounded up to a multiple of another number.
Syntax
CEILING(number; mult; mode)
Syntax Description
number is the number that is to be rounded up to a multiple of mult.
If mode is zero or omitted, CEILING rounds up to the multiple above (greater than or equal to) number.
If mode is non-zero, CEILING rounds up away from zero. This is only relevant with negative numbers.


CELL
Returns information about the address, formatting or contents of a cell.
Syntax
CELL(info_type; cell_ref)
Syntax Description
Returns information about the cell cell_ref.
The information returned depends on the (case insensitive) text string info_type, which can be set as follows:
COL
CELL() returns the number of the referenced column. 
ROW
CELL() returns the number of the referenced row. 
SHEET
CELL() returns the number of the referenced sheet. 
ADDRESS
CELL() returns the absolute address of the referenced cell, as text. 
FILENAME
CELL() returns the file name and the sheet number of the referenced cell, as text.
COORD
CELL() returns the complete cell address in Lotus(TM) notation, as text. 
CONTENTS
CELL() returns the contents of the referenced cell, without any formatting.
CELL() returns text that indicates the type of cell contents:
b (blank) signifies an empty cell
l (label) signifies text, or the result of a formula as text
v (value) signifies a number, or the result of a formula as a number
WIDTH
CELL() returns the width of the referenced column. The unit is the number of zeros (0) that fit into the column in the default text and the default size.
PREFIX
CELL() returns text that indicates the alignment of the referenced cell, as:
' = align left or left-justified
" = align right
^ = centered
\ = repeating (currently inactive)
PROTECT
CELL() returns the status of the cell protection for the cell.
1 = cell is protected
0 = cell is not protected
FORMAT
CELL() returns text that indicates the number format:
, = number with thousands separator
F = number without thousands separator
C = currency format
S = exponential representation, for example 1.234+E56
P = percentage
In the above formats, the number of decimal places after the decimal separator is given as a number. Example: the number format #,##0.0 returns ,1 and the number format 00.000% returns P3
D1 = MMM-D-YY, MM-D-YY and similar formats
D2 = DD-MM
D3 = MM-YY
D4 = DD-MM-YYYY HH:MM:SS
D5 = MM-DD
D6 = HH:MM:SS AM/PM
D7 = HH:MM AM/PM
D8 = HH:MM:SS
D9 = HH:MM
G = All other formats
- (Minus) at the end = negative numbers are formatted in color
() (brackets) at the end = there is an opening bracket in the format code
COLOR
CELL() returns 1, if negative values have been formatted in color, otherwise 0.
PARENTHESES
CELL() returns 1 if the format code contains an opening bracket (, otherwise 0.


CHAR
Returns a single text character, given a character code.
Syntax
CHAR(number)
Syntax Description
number is the character code, in the range 1-255.
CHAR uses your system's character mapping (for example iso-8859-1, iso-8859-2, Windows-1252, Windows-1250) to determine which character to return. Codes greater than 127 may not be portable.


CHIDIST
Calculates values for a χ2-distribution.
Syntax
CHIDIST(x; k)
Syntax Description
k is the (positive integer) degrees of freedom for the χ2-distribution.
x is a number >=0.


CHIINV
Calculates the inverse of the CHIDIST function.
Syntax
CHIINV(p; k)
Syntax Description
k is the (positive integer) degrees of freedom for the χ2-distribution.
CHIINV(p; k) returns the value x, such that CHIDIST(x; k) is p.


CHISQDIST
Calculates values for a χ2-distribution.
Syntax
CHISQDIST(x; k; Cumulative)
Syntax Description
x is the number, at which you will evaluate the χ2-distribution.
k sets the degrees of freedom for the χ2-distribution
Constraint: k must be a positive integer
Cumulative is a logical value.
In the case Cumulative=TRUE() the cumulative distribution function is used, in the case Cumulative=FALSE() the probability density function. This parameter is optional. It is set toTRUE() if missing.


CHISQINV
Calculates the inverse of the CHISQDIST function.
Syntax
CHISQINV(p; k)
Syntax Description
k is the degrees of freedom for the χ2-distribution.
Constraint: k must be a positive integer
p is the given probability
Constraint: 0 ≤ p < 1


CHITEST
Returns a measure of χ2 'goodness of fit'.
Syntax
CHITEST(observed; expected)
Syntax Description
observed and expected are ranges or arrays of observed and expected values. They must have the same number of rows and columns and there must be at least 2 values in each.


CHOOSE
Returns a value from a list, given an index number.
Syntax
CHOOSE(index; value1; value2; ... value30)
Syntax Description
value1 ... value30 are up to 30 values, each of which may be text, a number, a logical value, a reference or a formula.
index is an index number specifying which of value1 ... value30 to return; 1 returns value1, 2 returns value2, etc.


CLEAN
returns a text string with non-printable characters removed.
Syntax
CLEAN(text)
Syntax Description
Returns text with all non-printable characters removed.
Spaces are not removed.


CODE
returns the numeric code for the first character in a text string.
Syntax
CODE(text)
Syntax Description
Returns the numeric code for the first character of the text string text, in the range 0-255.
Codes greater than 127 may depend on your system's character mapping (for example iso-8859-1, iso-8859-2, Windows-1252, Windows-1250), and hence may not be portable.


COLUMN
Returns the column number(s), given a reference.
Syntax
COLUMN(reference)
Syntax Description
Returns the column number of reference, where reference is a reference to a cell.
If reference is omitted, the column number of the current cell (containing the formula) is returned.


COLUMNS
Returns the number of columns in a given reference.
Syntax
COLUMNS(reference)
Syntax Description
reference may be given either explicitly (eg A3:B5) or by name (eg myref).


COMBIN
Returns the number of combinations of a subset of items.
Syntax
COMBIN(n; k)
Syntax Description
n is the number of items in the set.
k is the number of items to choose from the set.
COMBIN returns the number of ways to choose these items. 


COMBINA
Returns the number of combinations of a subset of items.
Syntax
COMBINA(n; k)
Syntax Description
n is the number of items in the set.
k is the number of items to choose from the set.
COMBINA returns the number of unique ways to choose these items, where the order of choosing is irrelevant, and repetition of items is allowed. 


COMPLEX
Returns a complex number, given real and imaginary parts.
Syntax
COMPLEX(realpart; imaginarypart; suffix)
Syntax Description
Returns a complex number as text, in the form a+bi or a+bj.
realpart and imaginarypart are numbers.
suffix
is optional text i or j (in lowercase) to indicate the imaginary part of the complex number; it defaults to i.


CONCATENATE
Combines several text strings into one string.
Syntax
CONCATENATE(text1; text2; ... text30)
Syntax Description
Returns up to 30 text strings text1 - text30, joined together.
text1 - text30 may also be single cell references.


CONFIDENCE
Returns a confidence interval.
Syntax
CONFIDENCE(α; sd; size)
Syntax Description
sd (> 0) is the (known) standard deviation of a normal distribution.
size is the size of a sample from that distribution.
α is the significance level (0 < α < 1), which determines the desired confidence level = (1 - α)*100%. Thus for example α = 0.05 gives a 95% confidence level.


CONVERT
Converts a number from one measurement system to another.
Syntax
CONVERT(number; originalunits; newunits)
Syntax Description
number is the number to be converted.
originalunits and newunits are text representing the original and new measurement systems. These are case-sensitive, and must be selected from the table below.
property units
Weight g, sg, lbm, u, ozm, stone, ton, grain, pweight, hweight, shweight
length m, mi, Nmi, in, ft, yd, ang, Pica, ell, parsec
time yr, day, hr, mn, sec
pressure Pa, atm, mmHg, Torr, psi
force N, dyn, pond
energy J, e, c, cal, eV, HPh, Wh, BTU
power W, HP, PS
field strength T, ga
temperature C, F, K, Reau, Rank
volume l, tsp, tbs, oz, cup, pt, qt, gal, m3, mi3, Nmi3, in3, ft3, yd3, ang3, Pica3, barrel, bushel, regton, Schooner, Middy, Glass
area m2, mi2, Nmi2, in2, ft2, yd2, ang2, Pica2, Morgen, ar, acre, ha
speed m/s, m/h, mph, kn, admkn
prefix < 1 y (10-24), z (10-21), a (10-18), f (10-15), p (10-12), n (10-9), u (10-6), m (10-3), c (10-2), d (10-1)
prefix > 1 e (101), h (102), k (103), M 106), G (109), T (1012), P (1015), E (1018), Z (1021), Y (1024)


CORREL
Returns the Pearson correlation coefficient of two sets of data.
Syntax
CORREL(x; y)
Syntax Description
Where x and y are ranges or arrays containing the two sets of data.


COS
Returns the cosine of the given angle (in radians).
Syntax
COS(angle)
Syntax Description
Returns the (trigonometric) cosine of angle, the angle in radians.
To return the cosine of an angle in degrees, use the RADIANS function.


COSH
Returns the hyperbolic cosine of a number.
Syntax
COSH(number)
Syntax Description
Returns the hyperbolic cosine of number.


COT
Returns the cotangent of the given angle (in radians).
Syntax
COT(angle)
Syntax Description
Returns the (trigonometric) cotangent of angle, the angle in radians.
To return the cotangent of an angle in degrees, use the RADIANS function.


COTH
Returns the hyperbolic cotangent of a number.
Syntax
COTH(number)
Syntax Description
Returns the hyperbolic cotangent of number.


COUNT
Counts the numbers in the list of arguments, ignoring text entries.
Syntax
COUNT(value1; value2; ... value30)
Syntax Description
value1 to value30 are up to 30 values or ranges representing the values to be counted.


COUNTA
Counts the non-empty values in the list of arguments.
Syntax
COUNTA(value1; value2; ... value30)
Syntax Description
value1 to value30 are up to 30 values or ranges representing the values to be counted.


COUNTBLANK
Returns the number of empty cells.
Syntax
COUNTBLANK(range)
Syntax Description
Returns the number of empty cells in the cell range range.
A cell that contains blank text such as spaces, or even text with zero length such as returned by ="", is not considered empty, even though it may appear empty.


COUNTIF
Counts the number of cells in a range that meet a specified condition.
Syntax
COUNTIF(test_range; condition)
Syntax Description
test_range is the range to be tested.
condition may be:
A number, such as 34.5
An expression, such as 2/3 or SQRT(B5)
A text string
COUNTIF counts those cells in test_range that are equal to condition, unless condition is a text string that starts with a comparator:
>, <, >=, <=, =, <>
In this case COUNTIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).


COUPDAYBS
Returns the number of days between the coupon date preceding the settlement, and the settlement date.
Syntax
COUPDAYBS(settlement; maturity; frequency; basis)
Syntax Description
settlement: the date of purchase of the security.
maturity: the date on which the security matures (expires).
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


COUPDAYS
Returns the number of days in the coupon period that contains the settlement date.
Syntax
COUPDAYS(settlement; maturity; frequency; basis)
Syntax Description
settlement: the date of purchase of the security.
maturity: the date on which the security matures (expires).
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


COUPDAYSNC
Returns the number of days between the settlement date and the next coupon date.
Syntax
COUPDAYSNC(settlement; maturity; frequency; basis)
Syntax Description
settlement: the date of purchase of the security.
maturity: the date on which the security matures (expires).
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


COUPNCD
Returns the coupon date next after the settlement date.
Syntax
COUPNCD(settlement; maturity; frequency; basis)
Syntax Description
settlement: the date of purchase of the security.
maturity: the date on which the security matures (expires).
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


COUPNUM
Returns the number of coupons (interest payments) between the settlement date and maturity.
Syntax
COUPNUM(settlement; maturity; frequency; basis)
Syntax Description
settlement: the date of purchase of the security.
maturity: the date on which the security matures (expires).
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


COUPPCD
Returns the coupon (interest payment) date which precedes the settlement date.
Syntax
COUPPCD(settlement; maturity; frequency; basis)
Syntax Description
settlement: the date of purchase of the security.
maturity: the date on which the security matures (expires).
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


COVAR
Returns the covariance of two sets of data.
Syntax
COVAR(x; y)
Syntax Description
Where x and y are ranges or arrays containing the two sets of data.


CRITBINOM
Returns the smallest value for which the cumulative binomial distribution is greater than or equal to a criterion value.
Syntax
CRITBINOM(n; p; a)
Syntax Description
With n independent trials, each with a probability p of success, CRITBINOM returns the number of successes, up to and including which are expected with a probability a


CUMIPMT
Returns the total interest paid on a loan in specified periodic payments.
Syntax
CUMIPMT(rate; numperiods; presentvalue; start; end; type)
Syntax Description
rate: the interest rate per period.
numperiods: the total number of payment periods in the term.
presentvalue: the initial sum borrowed.
start: the first period to include. Periods are numbered beginning with 1.
end: the last period to include.
type: when payments are made:
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


CUMPRINC
Returns the total capital repaid on a loan in specified periodic payments.
Syntax
CUMPRINC(rate; numperiods; presentvalue; start; end; type)
Syntax Description
rate: the interest rate per period.
numperiods: the total number of payment periods in the term.
presentvalue: the initial sum borrowed.
start: the first period to include. Periods are numbered beginning with 1.
end: the last period to include.
type: when payments are made:
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


CURRENT
Returns the current (to date) result of evaluating the formula.
Syntax
CURRENT()
Syntax Description
This function returns the result to date of evaluating the formula of which it is a part (in other words the result as far as that evaluation has got).


DATE
returns the date, given the year, month and day of the month.
Syntax
DATE(year; month; day)
Syntax Description
Returns the date, expressed as a date-time serial number.
year is an integer between 1583 and 9956 or between 0 and 99; month and day are integers.


DATEVALUE
returns the date-time serial number, from a date given as text.
Syntax
DATEVALUE(datetext)
Syntax Description
datetext is a date, expressed as text.
DATEVALUE returns the date-time serial number, which may be formatted to read as a date.


DAVERAGE
Returns the average of values in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DAVERAGE(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column to examine. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to examine.


DAY
Returns the day of a given date.
Syntax
DAY(date)
Syntax Description
Returns the day of date as a number ( 1- 31).


DAYS
Returns the number of days between two dates
Syntax
DAYS(enddate; startdate)
Syntax Description
startdate and enddate may be dates as numbers or text (which is converted to number form).
DAYS returns enddate - startdate. The result may be negative.


DAYS360
Returns the number of days between two dates, using the 360 day year.
Syntax
DAYS360(enddate; startdate; method)
Syntax Description
startdate and enddate are the starting and ending dates (text or date-time serial numbers). If startdate is earlier than enddate, the result will be negative.
method is an optional parameter; if 0 or omitted, the US National Association of Securities Dealers (NASD) method of calculation is used; if 1 (or <>0) the European method of calcuation is used.
The calculation assumes that all months have 30 days, so a year (12 months) has 360 days.


DAYSINMONTH
Returns the number of days in a month.
Syntax
DAYSINMONTH(date)
Syntax Description
Returns the number of days in the month in which date lies.


DAYSINYEAR
Returns the number of days in a year.
Syntax
DAYSINYEAR(date)
Syntax Description
Returns the number of days in the year in which date lies.


DB
Returns the depreciation of an asset for a given year using the fixed rate declining-balance method.
Syntax
DB(originalcost; salvagevalue; lifetime; year; months1styear)
Syntax Description
originalcost: the initial cost of the asset.
salvagevalue: is the value at the end of the depreciation (sometimes called the salvage value of the asset).
lifetime: the number of years over which the asset is being depreciated.
year: the year number for which the depreciation is calculated.
months1styear: the number of months in the first year (defaults to 12 if omitted).


DCOUNT
Counts the cells containing numbers in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DCOUNT(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column to count. 
criteria_table is a range containing criteria, to select which rows of the database_table to count.


DCOUNTA
Counts the non-empty cells in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DCOUNTA(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column to count. 
criteria_table is a range containing criteria, to select which rows of the database_table to count.


DDB
Returns the depreciation of an asset for a given year using the double (or other factor) declining-balance method.
Syntax
DDB(originalcost; salvagevalue; lifetime; year; factor)
Syntax Description
originalcost: the initial cost of the asset.
salvagevalue: is the value at the end of the depreciation (sometimes called the salvage value of the asset).
lifetime: the number of years over which the asset is being depreciated.
year: the year number for which the depreciation is calculated.
factor: the factor to set the depreciation rate (2 if omitted).


DEC2BIN
Converts a decimal number to binary.
Syntax
DEC2BIN(number; numdigits)
Syntax Description
Returns a binary number as text, given the decimal number, which must be between -512 and 511 inclusive, and may be text or a number.
The output is a binary number with up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal).
numdigits is an optional number specifying the number of digits to return.


DEC2HEX
Converts a decimal number to hexadecimal.
Syntax
DEC2HEX(number; numdigits)
Syntax Description
Returns a hexadecimal number as text, given the decimal number, which must be between -239 and 239-1 inclusive, and may be text or a number.
The output is a hexadecimal number with up to ten digits in two's complement representation.
numdigits is an optional number specifying the number of digits to return.


DEC2OCT
Converts a decimal number to octal.
Syntax
DEC2OCT(number; numdigits)
Syntax Description
Returns an octal number as text, given the decimal number, which must be between -229 and 229-1 inclusive, and may be text or a number.
The result is an octal number with up to ten digits in two's complement representation.
numdigits is an optional number specifying the number of digits to return.


DECIMAL
Returns a decimal number, given a text representation and its base radix.
Syntax
DECIMAL(text; radix)
Syntax Description
text is text representing a number with the base radix radix (an integer between 2 and 36).
Any leading spaces and tabs are ignored.
Letters, if any, may be upper or lower case.
If radix is 16 (hexadecimal system), any leading 0x, 0X, x or X is ignored, as is any trailing h or H.
If radix is 2 (binary system), any trailing b or B is ignored.


DEGREES
Converts radians into degrees.
Syntax
DEGREES(radians)
Syntax Description
radians is the angle in radians to be converted to degrees.


DELTA
Returns 1 if two numbers are equal, and 0 otherwise.
Syntax
DELTA(number1; number2)
Syntax Description
number1 and number2 are numbers. If number2 is omitted it is assumed to be 0.
This function is an implementation of the (mathematical) Kronecker delta function.
number1=number2 returns TRUE or FALSE instead of 1 or 0, but is otherwise identical for number arguments.


DEVSQ
Returns the sum of squares of deviations from the mean.
Syntax
DEVSQ(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.
DEVSQ calculates the mean of all the numbers, then sums the squared deviation of each number from that mean. 


DGET
returns the contents of a cell in a column of a database table, in the unique row meeting the criteria.
Syntax
DGET(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column containing the cell. 
criteria_table is a range containing criteria, which are used to select the unique row of the database_table.


DISC
Returns the discount rate of a security.
Syntax
DISC(settlementdate; maturitydate; price; redemptionvalue; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the security.
maturitydate: the maturity (redemption) date of the security.
price: the price of the security.
redemptionvalue: the redemption value of the security.
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


DMAX
Returns the largest value in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DMAX(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column to examine. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to examine.


DMIN
Returns the minimum (lowest) value in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DMIN(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column to examine. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to examine.


DOLLAR
Returns text representing a number in your local currency format.
Syntax
DOLLAR(number; decimals)
Syntax Description
Returns text representing number as currency. decimals (optional, assumed to be 2 if omitted) sets the number of decimal places.


DOLLARDE
Converts a fractional number representation of a number into a decimal number.
Syntax
DOLLARDE(fractionalrep; denominator)
Syntax Description
fractionalrep: the fractional representation. Sometimes a security price, for example, might be expressed as 2.03, meaning $2 and 3/16 of a dollar.
denominator: the denominator - for example, 16 in the example above.
DOLLARDE converts the fractional representation to decimal. Despite its name, it returns a number, not a currency. Its inverse is DOLLARFR.


DOLLARFR
Converts a decimal number into a fractional representation of that number.
Syntax
DOLLARFR(decimal; denominator)
Syntax Description
decimal: the decimal number.
denominator: the denominator for the fractional representation.
Sometimes a security price, for example, might be expressed as 2.03, a fractional representation meaning $2 and 3/16 of a dollar. As a decimal this is 2.1875.
DOLLARFR converts the decimal representation to a fractional representation. Despite its name, it returns a number, not a currency. Its inverse is DOLLARDE.


DPRODUCT
Returns the product of cells in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DPRODUCT(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column to sum. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to sum.


DSTDEV
Returns the sample standard deviation of values in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DSTDEV(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be examined.
field is the column to examine. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to examine.


DSTDEVP
Returns the standard deviation of values in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DSTDEVP(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be examined.
field is the column to examine. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to examine.


DSUM
Sums the cells in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DSUM(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be processed.
field is the column to sum.
criteria_table is a range containing criteria, which are used to select which rows of the database_table to sum.


DURATION
Returns the number of periods needed for an investment to reach a certain value.
Syntax
DURATION(rate; present_value; future_value)
Syntax Description
rate: the interest rate per period that will apply to the investment.
present_value: the value of the investment now.
future_value: the desired value of the investment in the future.


DURATION_ADD
Returns the Macaulay duration of a security.
Syntax
DURATION_ADD(settlementdate; maturitydate; rate; yield; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the bond.
maturitydate: the maturity (redemption) date of the bond.
rate: the (annual) interest rate of the bond.
yield: the (annual) yield of the bond.
frequency: the number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


DVAR
Returns the bias-corrected sample variance of values in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DVAR(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be examined.
field is the column to examine. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to examine.


DVARP
Returns the population variance of values in a column of a Calc 'database' table, in rows which meet specified criteria.
Syntax
DVARP(database_table; field; criteria_table)
Syntax Description
database_table is a range defining the data to be examined.
field is the column to examine. 
criteria_table is a range containing criteria, which are used to select which rows of the database_table to examine.


EASTERSUNDAY
Returns the date of Easter Sunday in a given year.
Syntax
EASTERSUNDAY(year)
Syntax Description
year is an integer between 1583 and 9956 or between 0 and 99, specifying the year.


EDATE
Returns a date a number of months away.
Syntax
EDATE(startdate; months)
Syntax Description
months is a number of months that are added to the startdate. The day of the month remains unchanged, unless it is more than the number of days in the new month (when it becomes the last day of that month).
months may be negative.


EFFECTIVE
Returns the effective compounded interest rate given a nominal interest rate.
Syntax
EFFECTIVE(nom_rate; num)
Syntax Description
nom_rate: the nominal interest rate.
num: the number of times interest is credited / compounded during the period that nom_rate applies to.
If an investment has a nominal rate, say for a year, but interest is paid and credited say each quarter, the interest paid each quarter will itself start earning interest. This increases the effective value. This function returns the effective rate - that is, the rate that would have to be paid at the end of the (say) year to give the same return.


EOMONTH
Returns the date of the last day of a month.
Syntax
EOMONTH(startdate; addmonths)
Syntax Description
addmonths is a number of months to be added to the startdate (given as text or a date-time serial number), to give a new date. For this new date, EOMONTH returns the date of the last day of the month, as a date-time serial number.
addmonths may be positive (in the future), zero or negative (in the past).


ERF
Calculates the error function (Gauss error function).
Syntax
ERF(number1; number2)
Syntax Description
If number2 is omitted, returns the error function calculated between 0 and number1, otherwise returns the error function calculated between number1 and number2.


ERFC
Calculates the complementary error function (complementary Gauss error function).
Syntax
ERFC(number)
Syntax Description
Returns the error function calculated between number and infinity, that is, the complementary error function for number.


ERRORTYPE
Returns the number corresponding to an error value.
Syntax
ERRORTYPE(reference)
Syntax Description
Returns the error number of the cell referred to by reference, or #N/A if that cell has no error.


EVEN
Rounds a number up, away from zero, to the next even integer.
Syntax
EVEN(number)
Syntax Description
Returns number rounded to the next even integer up, away from zero.


EXACT
returns TRUE if two text strings are identical
Syntax
EXACT(text1; text2)
Syntax Description
Returns TRUE if the text strings text1 and text2 are exactly the same (including case).


EXP
Returns the mathematical constant e raised to the power of a number.
Syntax
EXP(number)
Syntax Description
Returns enumber.


EXPONDIST
Calculates values for an exponential distribution.
Syntax
EXPONDIST(x; λ; mode)
Syntax Description
The exponential distribution is a continuous probability distribution, with parameter λ (rate). λ must be greater than zero.
If mode is 0, EXPONDIST calculates the probability density function of the exponential distribution.
If mode is 1, EXPONDIST calculates the cumulative distribution function of the exponential distribution.


FACT
Returns the factorial of a number.
Syntax
FACT(number)
Syntax Description
Returns number!, the factorial of number, calculated as 1*2*3*4* ... * number.
FACT(0) returns 1 by definition.


FACTDOUBLE
Returns the double factorial of a number.
Syntax
FACTDOUBLE(number)
Syntax Description
Returns number!!, the double factorial of number, where number is an integer greater than or equal to zero.
For even numbers FACTDOUBLE(number) returns:
2*4*6*8* ... *number
For odd numbers FACTDOUBLE(number) returns:
1*3*5*7* ... *number
FACTDOUBLE(0) returns 1 by definition.


FALSE
Returns the logical value FALSE.
Syntax
FALSE()
Syntax Description
The FALSE() function has no arguments, and always returns the logical value FALSE.


FDIST
Calculates values for an F-distribution.
Syntax
FDIST(x; r1; r2)
Syntax Description
r1 and r2, which are positive integers, are the degrees of freedom parameters for the F-distribution.
x must be greater than or equal to 0.
FDIST returns the area of the right tail of the probability density function for the F-distribution.


FIND
Returns the position of a string of text within another string.
Syntax
FIND(findtext; texttosearch; startposition)
Syntax Description
Returns the character position of the first occurrence of findtext within texttosearch.
startposition (optional) is the position from which the search starts.
The search is case-sensitive.
A failed search gives the #VALUE! error.


FINV
Calculates the inverse of the FDIST function.
Syntax
FINV(p; r1; r2)
Syntax Description
Returns the value x, such that FDIST(x; r1; r2) is p.
Parameters r1 and r2 (degrees of freedom) are positive integers.
p must be greater than 0 and less than or equal to 1.


FISHER
Calculates values for the Fisher transformation.
Syntax
FISHER(r)
Syntax Description
Returns the value of the Fisher transformation at r, (-1 < r < 1).


FISHERINV
Calculates the inverse of the FISHER transformation.
Syntax
FISHERINV(z)
Syntax Description
Returns the value r, such that FISHER(r) is z.


FIXED
Returns a number as text with a specified format.
Syntax
FIXED(number; decimals; omitseparators)
Syntax Description
Returns text representing number with decimals decimal places. If omitseparators (optional) is TRUE thousands separators will be omitted. In the absence of omitseparators thousands separators are included.


FLOOR
Returns a number rounded down to a multiple of another number.
Syntax
FLOOR(number; mult; mode)
Syntax Description
number is the number that is to be rounded down to a multiple of mult.
If mode is zero or omitted, FLOOR rounds down to the multiple below (more negative than or equal to) number.
If mode is non-zero, FLOOR rounds down towards zero. 


FORECAST
Fits a straight line to data using linear regression and returns a point on that line.
Syntax
FORECAST(new_xvalue; yvalues; xvalues)
Syntax Description
yvalues and xvalues are single row or column ranges specifying points in a set of data. yvalues and xvalues must be the same size.
FORECAST fits a straight line through these data points, using the linear regression method.
FORECAST returns the y value on the straight line found, corresponding to the x value in new_xvalue


FORMULA
Returns the formula used in a cell as a text string.
Syntax
FORMULA(cell)
Syntax Description
Returns the formula entered in the cell cell as a text string.


FREQUENCY
Returns an array, categorising values of a data set into given intervals.
Syntax
FREQUENCY(data; bins)
Syntax Description
data is a range or array containing numerical data.
bins is a single column range or array containing numbers in ascending order which are the upper limit of each category.
FREQUENCY returns a single column array, where the first element is the count of values in data that are less than or equal to the first value in bins, the second value is the count of values in data that are greater than the first value but less than or equal to the second value in bins, and so on. The array returned is one element longer than bins; the last element contains the count of values in data that are greater than the last value in bins.


FTEST
Returns the result of an F-test.
Syntax
FTEST(data1; data2)
Syntax Description
data1 and data2 are ranges or arrays (possibly of different size) containing numbers, on which the F-test is performed. The F-test calculates the likelihood that two samples have the same variance.


FV
Returns the future value of an initial sum with a subsequent stream of payments.
Syntax
FV(rate; numperiods; payment; presentvalue; type)
Syntax Description
rate: the (fixed) interest rate per period.
numperiods: the total number of payment periods in the term.
payment: the payment made each period. If presentvalue is given, this may omitted (defaults to 0).
presentvalue: the lump sum payment at the start of the term (optional - defaults to 0). With a loan, this would normally be the sum borrowed; with a bond this would generally be 0.
type: when payments are made (optional - defaults to 0):
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


FVSCHEDULE
Returns the future value of a lump sum, with changing future interest rates.
Syntax
FVSCHEDULE(principal; interestrates)
Syntax Description
principal: the initial value of the lump sum.
interestrates: a range or array containing a schedule of interest rates which apply each period.


GAMMA
Returns the values of the Gamma function.
Syntax
GAMMA(x)
Syntax Description
x is a number.
Constraint: If x is an integer, then x must be positive.


GAMMADIST
Calculates values for a gamma distribution.
Syntax
GAMMADIST(x; α; β; mode)
Syntax Description
The gamma distribution is a family of continuous probability distributions, with parameters α (shape) and β (scale).
If mode is 0, GAMMADIST calculates the probability density function of the gamma distribution.
If mode is 1, GAMMADIST calculates the cumulative distribution function of the gamma distribution.
x
must be greater than or equal to 0.


GAMMAINV
Calculates the inverse of the cumulative GAMMADIST function.
Syntax
GAMMAINV(p; α; β)
Syntax Description
The gamma distribution is a family of continuous probability distributions, with two controlling parameters α and β.
GAMMAINV returns the value n, such that GAMMADIST(n; α; β; 1) is p.


GAMMALN
Returns the natural logarithm of the gamma function.
Syntax
GAMMALN(x)
Syntax Description
Returns the natural logarithm of �“( x), the gamma function.
x must be greater than 0.


GAUSS
Returns a value in the standard normal cumulative distribution.
Syntax
GAUSS(x)
Syntax Description
Where x is a number, returns NORMSDIST(x) - 0.5.


GCD
Returns the greatest common divisor of two or more integers.
Syntax
GCD(integer1; integer2; ... integer30)
Syntax Description
integer1 to integer30 are up to 30 integers or ranges of integers whose greatest common divisor is to be calculated.
The greatest common divisor (or highest common factor) is the largest positive integer which will divide, without remainder, each of the given integers.


GEOMEAN
Returns the geometric mean of the arguments.
Syntax
GEOMEAN(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers. Numbers must not be zero.
The geometric mean of a1, a2, ... an is defined as (a1 . a2. ... . an)1/n.


GESTEP
Returns 1 if a number is greater than or equal to a step number, or 0 otherwise.
Syntax
GESTEP(number; stepnumber)
Syntax Description
Returns 1 if number is greater than or equal to stepnumber (both being numbers). If stepnumber is omitted it is assumed to be 0.


GROWTH
Fits an exponential curve to a data set, and returns points on that curve.
Syntax
GROWTH(yvalues; xvalues; new_xvalues; allow_factor)
Syntax Description
data is a range or array containing numerical data.
yvalues and xvalues are single row or column ranges specifying points in a set of data.
GROWTH fits an exponential curve of the form y=bmx through these data points, using the linear regression method.
If allow_factor is FALSE the factor b in this equation is set to 1; if allow_factor is non-zero, TRUE or omitted the factor b is calculated from the data.


HARMEAN
Returns the harmonic mean of the arguments.
Syntax
HARMEAN(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers. Numbers must not be zero.


HEX2BIN
Converts a hexadecimal number to binary.
Syntax
HEX2BIN(hexadecimalnumber; numdigits)
Syntax Description
Returns text representing a binary number, given hexadecimalnumber, which may be text, or a number (taken to be hexadecimal although it is not).
The binary number returned may have up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal).
hexadecimalnumber must therefore also lie in this range, and is given in two's complement form with up to ten digits.
numdigits is an optional number specifying the number of digits to return.


HEX2DEC
Converts a hexadecimal number to decimal.
Syntax
HEX2DEC(hexadecimalnumber)
Syntax Description
Returns a (decimal) number, given hexadecimalnumber, which may be text, or a number (taken to be hexadecimal although it is not).
hexadecimalnumber may have up to ten digits in two's complement representation; positive numbers are 0 to 7FFFFFFFFF (representing 0 to 239-1 decimal) and negative numbers FFFFFFFFFF to 8000000000 (representing -1 to -239 decimal).


HEX2OCT
Converts a hexadecimal number to octal.
Syntax
HEX2OCT(hexadecimalnumber; numdigits)
Syntax Description
Returns text representing an octal number, given hexadecimalnumber, which may be text, or a number (taken to be hexadecimal although it is not).
The octal number returned may have up to ten digits in twos complement representation; positive numbers are 0 to 3777777777 octal and negative numbers 7777777777 to 4000000000 (a range of -229 to 229-1 decimal).
hexadecimalnumber must therefore also lie in this range, and is given in twos complement form with up to ten digits.
numdigits is an optional number specifying the number of digits to return.


HLOOKUP
Returns a value from a table row, in the column found by lookup in the first row.
Syntax
HLOOKUP(lookupvalue; datatable; rowindex; mode)
Syntax Description
lookupvalue is a value (number, text or logical value) to look up in the top row of the range/array datatable. When a value is matched in the top row, HLOOKUP returns the corresponding value (in the same column) in the rowindexth row of datatable, where rowindex = 1 is the top row.
If mode is 0 or FALSE, the top row of datatable may be unordered, and the first exact match is found (searching left to right).
If mode is 1 or TRUE, or is omitted, the top row of datatable must be sorted, with numbers in ascending order appearing before text values in alphabetic order.
HLOOKUP
decides where in the top row lookupvalue would appear. If there is an exact match, that is the column found; if there is more than one exact match, the column found is not necessarily the leftmost. If there is no exact match, the column to the left of where value would appear in the top row is found; the #N/A error results if that column is not in the table.


HOUR
Returns the hour of a given time.
Syntax
HOUR(time)
Syntax Description
Returns the hour of time as a number, 0 - 23.
time may be text or a date-time serial number.


HYPERLINK
Sets a cell to open a hyperlink (in another application) when clicked.
Syntax
HYPERLINK(linkaddress; celltext)
Syntax Description
A cell containing a HYPERLINK function will open a hyperlink when the cell is clicked.
linkaddress is the address (as text) that is opened.
celltext
is the text displayed in the cell. If celltext is omitted, linkaddress is displayed in the cell.


HYPGEOMDIST
Calculates values for a hypergeometric distribution.
Syntax
HYPGEOMDIST(x; n; M; N)
Syntax Description
The hypergeometric distribution is a discrete probability distribution giving the probability of x successes in a sequence of n draws (without replacement) from a finite population of size N which contains M successes.
HYPGEOMDIST calculates the probability density function of the hypergeometric distribution.


IF
Returns one of two values, depending on a test condition.
Syntax
IF(test; value1; value2)
Syntax Description
test is or refers to a logical value or expression that returns a logical value ( TRUE or FALSE).
value1 is the value that is returned by the function if test yields TRUE.
value2 is the value that is returned by the function if test yields FALSE.


IMABS
Returns the absolute value of a complex number.
Syntax
IMABS(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMABS returns the absolute value or modulus of complexnumber - that is, in a polar representation, the distance from the origin. If complexnumber is a+bi, then the absolute value is √(a2 + b2).


IMAGINARY
Returns the imaginary part of a complex number.
Syntax
IMAGINARY(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj, where a is the real part and b the imaginary part.
IMAGINARY returns the imaginary part as a number.


IMARGUMENT
Returns the argument of a complex number.
Syntax
IMARGUMENT(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMARGUMENT returns the argument of complexnumber in radians - that is, in a polar representation, the angle relative to the horizontal axis. For a complex number a+bi = r(cosφ + isinφ) the argument is φ.


IMCONJUGATE
Returns the complex conjugate of a complex number.
Syntax
IMCONJUGATE(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMCONJUGATE returns the complex conjugate of complexnumber - that is, if complexnumber is a+bi, it returns a-bi, as text.


IMCOS
Returns the cosine of a complex number.
Syntax
IMCOS(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMCOS returns the cosine of complexnumber, as text - that is, if complexnumber is a+bi, it returns cos(a)cosh(b)-sin(a)sinh(b)i.


IMDIV
Returns the result of dividing one complex number by another.
Syntax
IMDIV(complexnum1; complexnum2)
Syntax Description
complexnum1 and complexnum2 are text representing complex numbers, for example as a+bi or a+bj.
IMDIV returns complexnum1/complexnum2 as text.


IMEXP
Returns e to the power of a complex number.
Syntax
IMEXP(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMEXP returns the mathematical constant e raised to the power of complexnumber. The result is a complex number presented as text.
If complexnumber is a+bi, IMEXP(complexnumber) returns ea+bi = ea(cosb+isinb).


IMLN
Returns the natural logarithm of a complex number.
Syntax
IMLN(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMLN returns the natural logarithm of complexnumber as text.


IMLOG10
Returns the base10 logarithm of a complex number.
Syntax
IMLOG10(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMLOG10 returns the base10 logarithm of complexnumber, as text.


IMLOG2
Returns the base2 logarithm of a complex number.
Syntax
IMLOG2(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMLOG2 returns the base2 logarithm of complexnumber, as text.


IMPOWER
Returns a complex number raised to a power.
Syntax
IMPOWER(complexnumber; number)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj. number is a number.
IMPOWER returns complexnumber raised to the power of number. The result is a complex number presented as text.
If complexnumber is a+bi = r(cosφ + isinφ) and number is n, IMPOWER(complexnumber; number) returns (a+bi)n = rncosφ + irnsinφ.


IMPRODUCT
Returns the product of complex numbers.
Syntax
IMPRODUCT(complexnum1; complexnum2; ... complexnum29)
Syntax Description
complexnum1, complexnum2 ... are complex numbers as text, or references to cells / cell ranges containing complex numbers as text.
IMPRODUCT returns complexnum1*complexnum2*... as text.


IMREAL
Returns the real part of a complex number.
Syntax
IMREAL(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj, where a is the real part and b the imaginary part.
IMREAL returns the real part as a number.


IMSIN
Returns the sine of a complex number.
Syntax
IMSIN(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMSIN returns the sine of complexnumber, as text - that is, if complexnumber is a+bi, it returns sin(a)cosh(b)-cos(a)sinh(b)i.


IMSQRT
Returns the square root of a complex number.
Syntax
IMSQRT(complexnumber)
Syntax Description
complexnumber is text representing a complex number, for example as a+bi or a+bj.
IMSQRT returns the square root of complexnumber as text. That is, if complexnumber is a0+b0i, it returns a1+b1i, such that ( a1+b1i) * ( a1+b1i) = a0+b0i.


IMSUB
Returns the difference between 2 complex numbers.
Syntax
IMSUB(complexnum1; complexnum2)
Syntax Description
Returns complexnum1 - complexnum2, where complexnum1 and complexnum2 are complex numbers as text, or references to cells containing complex numbers as text.


IMSUM
Returns the sum of complex numbers.
Syntax
IMSUM(complexnum1; complexnum2; ... complexnum29)
Syntax Description
complexnum1, complexnum2, ... are complex numbers as text, or references to cells / cell ranges containing complex numbers as text.


INDEX
Returns a value from a table, given a row and column number.
Syntax
INDEX(datatable; row; column; areanumber)
Syntax Description
Returns the value at position row, column in the range or array datatable.
If datatable has multiple ranges then areanumber specifies which is to be used. areanumber is optional and defaults to 1.
If datatable has a single column, this syntax may be used:
INDEX(datatable; row)


INDIRECT
Returns a reference, given a text string.
Syntax
INDIRECT(textref; ref_type)
Syntax Description
textref is a text string specifying the reference to be returned; the reference may be to a single cell or to a range.
ref_type (optional) is a logical value that specifies the A1 or R1C1 reference style.


INT
Rounds a number down to the nearest integer.
Syntax
INT(number)
Syntax Description
Returns number rounded down to the nearest integer.
Negative numbers round down to the integer below: -1.3 rounds to -2.


INTERCEPT
Fits a straight line to data using linear regression and returns its intercept on the y-axis.
Syntax
INTERCEPT(yvalues; xvalues)
Syntax Description
yvalues and xvalues are single row or column ranges specifying points in a set of data. yvalues and xvalues must be the same size.
INTERCEPT fits a straight line through these data points, using the linear regression method (least squares). It then returns the y value where that straight line crosses the y-axis.


INTRATE
Returns the equivalent annual interest rate for an investment bought at one price and sold at another.
Syntax
INTRATE(settlementdate; maturitydate; purchasevalue; maturityvalue; basis)
Syntax Description
settlementdate: the date the item was bought.
maturitydate: the date the item was sold.
purchasevalue: the amount paid for the item.
maturityvalue: the amount received for the item.
basis: is chosen from a list of options and indicates how the year is to be calculated. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Exact number of days in months, exact number of days in year
2 - Exact number of days in month, year has 360 days
3 - Exact number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


IPMT
Returns the portion of the periodic payment which is interest for a fixed rate loan or annuity.
Syntax
IPMT(rate; period; numperiods; presentvalue; futurevalue; type)
Syntax Description
rate: the interest rate, per period.
period: the period of the payment whose interest portion is to be calculated, numbered from 1.
numperiods: the total number of payment periods in the term.
presentvalue: the initial sum borrowed or invested.
futurevalue: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0.
type: when payments are made (optional - defaults to 0):
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


IRR
Calculates the internal rate of return of a series of cash flows.
Syntax
IRR(payments; guess)
Syntax Description
payment is a range containing the payments made or received, at regular intervals.
guess (optional, defaults to 10%) is a first guess at the rate.
IRR iterates to find the rate of return which gives a zero net present value for the cash flows. At least one of the cash flows must be negative and at least one positive - to allow the net present value to be zero. The rate of return is per period, and interest is compounded each period.
The payments are assumed to arise at the start of each period; the order in which the payments are given is important.


ISBLANK
Tests if the cell is blank (empty).
Syntax
ISBLANK(cell)
Syntax Description
Returns TRUE if cell is a blank (empty) cell, and FALSE otherwise. Cells containing a formula or a error return FALSE.


ISERR
Tests for an error value other than #N/A.
Syntax
ISERR(value)
Syntax Description
Returns TRUE if value refers to or evaluates to an error value other than the Not Available error #N/A, and FALSE otherwise.
Use the ISERROR function to test for any errors including #N/A.


ISERROR
Tests for any error value.
Syntax
ISERROR(value)
Syntax Description
Returns TRUE if value refers to or evaluates to any error value, including #N/A, and FALSE otherwise.
Use the ISERR function to test for any errors except #N/A.


ISEVEN
Returns TRUE if the value is an even number, or FALSE if the value is odd.
Syntax
ISEVEN(value)
Syntax Description
value is the value to be checked.
If value is not an integer any digits after the decimal point are ignored. The sign of value is also ignored.


ISFORMULA
Tests if a cell contains a formula.
Syntax
ISFORMULA(cell)
Syntax Description
Returns TRUE if cell contains a formula and FALSE otherwise.


ISLEAPYEAR
Tests if a date is in a leap year.
Syntax
ISLEAPYEAR(date)
Syntax Description
Returns 1 if date lies in a leap year, and 0 otherwise.


ISLOGICAL
Tests if a cell contains a logical value, TRUE or FALSE.
Syntax
ISLOGICAL(value)
Syntax Description
Returns TRUE if value is either TRUE or FALSE, and FALSE otherwise.


ISNA
Tests for the #N/A (Not Available) error value.
Syntax
ISNA(value)
Syntax Description
Returns TRUE if value is or refers to the #N/A (Not Available) error and FALSE otherwise.


ISNONTEXT
Tests if a cell contains no text.
Syntax
ISNONTEXT(value)
Syntax Description
Returns TRUE unless value is or refers to text, in which case it returns FALSE.
ISNONTEXT(value) is equivalent to NOT(ISTEXT(value)).


ISNUMBER
Tests if a cell contains a number.
Syntax
ISNUMBER(value)
Syntax Description
Returns TRUE if value is a number or logical value and FALSE otherwise.


ISODD
Returns TRUE if the value is an odd number, or FALSE if the value is even.
Syntax
ISODD(value)
Syntax Description
value is the value to be checked.
If value is not an integer any digits after the decimal point are ignored. The sign of value is also ignored.


ISPMT
Returns the interest paid in a period for a fixed rate loan.
Syntax
ISPMT(rate; period; numperiods; principal)
Syntax Description
rate: the interest rate per period.
period: the period for which interest is to be calculated.
numperiods: the total number of payment periods in the term.
principal: the initial sum borrowed.


ISREF
Tests if the argument is a reference to a cell or range of cells.
Syntax
ISREF(value)
Syntax Description
Returns TRUE if value is a reference to a cell or range of cells and FALSE otherwise.


ISTEXT
Tests if a cell contains text.
Syntax
ISTEXT(value)
Syntax Description
Returns TRUE if value is or refers to text, and FALSE otherwise.


KURT
Returns a measure of how peaked or flat a distribution is.
Syntax
KURT(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges/arrays containing numbers.
KURT returns the kurtosis, a measure of how peaked or flat a distribution is, relative to a normal distribution. Positive values indicate a relatively peaked distribution, and negative a relatively flat distribution. 


LARGE
Returns the nth largest value in a list of numbers.
Syntax
LARGE(numberlist; n)
Syntax Description
Returns the nth largest number within the (unordered) range or array of numbers numberlist.


LCM
Returns the least common multiple of one or more integers.
Syntax
LCM(integer1; integer2; ... integer30)
Syntax Description
integer1 to integer30 are up to 30 integers or ranges of integers whose least common multiple is to be calculated.
The least common multiple (or lowest common multiple) is the smallest positive integer that is a multiple of all the given numbers.


LEFT
Returns text from the left side of a text string.
Syntax
LEFT(text; number)
Syntax Description
Returns number characters from the left side of the text text.
number defaults to 1 if omitted.


LEN
Returns the length of a text string.
Syntax
LEN(text)
Syntax Description
Returns the number of characters in the text text, including spaces.


LINEST
Returns a table of statistics for a straight line that best fits a data set.
Syntax
LINEST(yvalues; xvalues; allow_const; stats)
Syntax Description
yvalues is a single row or column range specifying the y coordinates in a set of data points.
xvalues is a corresponding single row or column range specifying the x coordinates. If xvalues is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of variables xvalues may be a range with corresponding multiple rows or columns.
LINEST finds a straight line y = a + bx that best fits the data, using linear regression (the "least squares" method). With more than one set of variables the straight line is of the form y = a + b1x1 + b2x2 ... + bnxn.
If allow_const is FALSE the straight line found is forced to pass through the origin (the constant a is zero; y = bx). If omitted, allow_const defaults to TRUE (the line is not forced through the origin).
If stats is omitted or FALSE only the top line of the statistics table is returned. If TRUE the entire table is returned.


LN
Returns the natural logarithm of a number.
Syntax
LN(number)
Syntax Description
Returns the natural logarithm (the logarithm to base e) of number, that is the power of e necessary to equal number.
The mathematical constant e is approximately 2.71828182845904.


LOG
Returns the logarithm of a number to the specified base.
Syntax
LOG(number; base)
Syntax Description
Returns the logarithm to base base of number.


LOG10
Returns the base-10 logarithm of a number.
Syntax
LOG10(number)
Syntax Description
Returns the logarithm to base 10 of number.


LOGEST
Returns a table of statistics for an exponential curve that best fits a data set.
Syntax
LOGEST(yvalues; xvalues; allow_const; stats)
Syntax Description
yvalues is a single row or column range specifying the y coordinates in a set of data points.
xvalues is a corresponding single row or column range specifying the x coordinates. If xvalues is omitted it defaults to 1, 2, 3, ..., n. If there is more than one set of x variables, xvalues may be a range with corresponding multiple rows or columns.
LOGEST finds an exponential curve y = a . bx that best fits the data. With more than one set of variables the curve is of the form y = a . b1x1 . b2x2 ... . bnxn.
In order to fit the curve, LOGEST uses linear regression (the "least squares" method) based on the equation ln(y) = ln(a) + x1ln(b1) + x2ln(b2) + ... xnln(bn).
If allow_const is FALSE the constant a is forced to be one; y = bx. If omitted, allow_const defaults to TRUE (a value for a is found).
If stats is omitted or FALSE only the top line of the statistics table is returned. If TRUE the entire table is returned. 


LOGINV
Calculates the inverse of the LOGNORMDIST function.
Syntax
LOGINV(p; μ ; σ )
Syntax Description
A variable is lognormally distributed if its natural logarithm is normally distributed. Parameters of the distribution are μ (mean) and σ (standard deviation).
LOGINV returns the value x, such that LOGNORMDIST(x; μ; σ) is p.


LOGNORMDIST
Calculates values for the cumulative distribution function of a lognormal distribution.
Syntax
LOGNORMDIST(x; μ; σ )
Syntax Description
A variable is lognormally distributed if its natural logarithm is normally distributed. Parameters of the distribution are μ (mean) and σ (standard deviation).
LOGNORMDIST calculates the cumulative density function for a lognormal distribution.


LOOKUP
Returns a value from a single-cell-wide table, in a position found by lookup in another table.
Syntax
LOOKUP(lookupvalue; searchtable; resulttable)
Syntax Description
lookupvalue is a value (number, text or logical value) to look up in the single row or single column range/array searchtable. searchtable must be sorted, with numbers in ascending order appearing before text values in alphabetic order.
resulttable is a range/array of the same size as searchtable. LOOKUP returns the value in resulttable in the position where the match was found in searchtable.
LOOKUP decides where in searchtable lookupvalue would appear. If there is an exact match, that is the position found; if there is more than one exact match, the position found is not necessarily the leftmost/ topmost. If there is no exact match, the position just before where lookupvalue would appear is found; the #N/A error results if that position is not in searchtable.


LOWER
Converts a text string to lowercase.
Syntax
LOWER(text)
Syntax Description
Returns text with all characters converted to lower case.


MATCH
Returns the position of a search item in a single row or column table.
Syntax
MATCH(searchitem; searchregion; matchtype)
Syntax Description
searchitem is the value to be found within the single row or single column range searchregion.
If matchtype is 1 or omitted, searchregion is assumed to be sorted in ascending order. MATCH then returns the position of the largest value in searchregion that is less than or equal to searchitem.
If matchtype is -1, searchregion is assumed to be sorted in descending order. MATCH then returns the position of the smallest value in searchregion that is greater than or equal to searchitem.
If matchtype is 0, MATCH returns the position of the first exact match. searchregion may be unsorted. 
If the search fails, MATCH returns the #N/A error.


MAX
Returns the maximum of a list of arguments, ignoring text entries.
Syntax
MAX(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.


MAXA
Returns the maximum of a list of arguments, including text and logical entries.
Syntax
MAXA(value1; value2; ... value30)
Syntax Description
value1 to value30 are up to 30 values or ranges, which may include numbers, text and logical values. Text is evaluated as 0. Logical values are evaluated as 1 ( TRUE) and 0 ( FALSE).


MDETERM
Returns the determinant of a matrix.
Syntax
MDETERM(array)
Syntax Description
Returns the determinant of the square matrix array, which may be either an inline array or a range, containing all numbers.


MDURATION
Returns the modified Macaulay duration of a security.
Syntax
MDURATION(settlementdate; maturitydate; rate; yield; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the bond.
maturitydate: the maturity (redemption) date of the bond.
rate: the (annual) interest rate of the bond.
yield: the (annual) yield of the bond.
frequency: the number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


MEDIAN
Returns the median of a set of numbers.
Syntax
MEDIAN(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.
MEDIAN returns the median (middle value) of the numbers. If the count of numbers is odd, this is the exact middle value. If the count of numbers is even, the average of the two middle values is returned.


MID
Returns text from the middle of a text string.
Syntax
MID(text; start; number)
Syntax Description
Returns number characters from the text text, starting at position start.


MIN
Returns the minimum of a list of arguments, ignoring text entries.
Syntax
MIN(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.


MINA
Returns the minimum of a list of arguments, including text and logical entries.
Syntax
MINA(value1; value2; ... value30)
Syntax Description
value1 to value30 are up to 30 values or ranges, which may include numbers, text and logical values. Text is evaluated as 0. Logical values are evaluated as 1 ( TRUE) and 0 ( FALSE).


MINUTE
Returns the minutes of a given time.
Syntax
MINUTE(time)
Syntax Description
Returns the minutes of time as a number, 0 - 59.
time may be text or a date-time serial number.


MINVERSE
Returns the inverse of a matrix.
Syntax
MINVERSE(array)
Syntax Description
Returns the inverse of the square matrix array, which may be either an inline array or a range, containing all numbers.
A matrix has an inverse if and only if its determinant is not zero.


MIRR
Returns the modified internal rate of return of a series of cash flows.
Syntax
MIRR(payments; financerate; reinvestrate)
Syntax Description
payment is a range containing the payments made or received, at regular intervals.
financerate is the rate of interest you pay per period on outstanding balances.
reinvestrate is the rate of interest you receive per period on invested balances.
At least one of the payments must be negative and at least one positive. The payments are assumed to arise at the start of each period; the order in which the payments are given is important.


MMULT
Returns the ordinary product of two matrices.
Syntax
MMULT(array1; array2)
Syntax Description
Multiplies array1 and array2, and returns the matrix result. array1 and array2 may each be either an inline array or a range, containing all numbers.
The number of columns in array1 must be the same as the number of rows in array2.
MMULT returns an array with the same number of rows as array1 and the same number of columns as array2. 


MOD
Returns the remainder when one integer is divided by another.
Syntax
MOD(number; divisor)
Syntax Description
For integer arguments this function returns number modulo divisor, that is the remainder when number is divided by divisor.
This function is implemented as number - divisor * INT( number/divisor) , and this formula gives the result if the arguments are not integer.


MODE
Returns the most common value in a set of numbers.
Syntax
MODE(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges/arrays containing numbers.
MODE returns the number that occurs most often, or if more than one number occurs that many times, the smallest such number. If no number occurs more than once, MODE returns an error.


MONTH
Returns the month of a given date.
Syntax
MONTH(date)
Syntax Description
Returns the month of date as a number, where January is 1 and December is 12.
date may be text or a date-time serial number.


MONTHS
Returns the number of months between two dates.
Syntax
MONTHS(startdate; enddate; mode)
Syntax Description
If mode is 0, MONTHS returns the number of whole months between startdate and enddate, day of the month to day of the month (see examples).
If mode is 1, MONTHS identifies the month that startdate and enddate each lie in, and returns the difference between those months. In other words it returns MONTH(enddate) - MONTH(startdate) + 12 * (YEAR(enddate) - YEAR(startdate)).
If startdate is after enddate the result will be negative.


MROUND
Returns a number rounded to the nearest multiple of another number.
Syntax
MROUND(number; mult)
Syntax Description
Returns number rounded to the nearest multiple of mult, that is to mult times an integer.
An alternative implementation would be mult * ROUND(number/mult).


MULTINOMIAL
Returns the factorial of the sum of the arguments divided by the product of the factorials of the arguments.
Syntax
MULTINOMIAL (number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges of numbers.
This function returns:
( number1 + number2 + ... + number30 )! / (number1! * number2! * ...* number30!)


MUNIT
Returns a unit (identity) matrix of a given size.
Syntax
MUNIT(size)
Syntax Description
Returns the unit matrix, also known as the identity matrix I, of size size (an integer greater than zero).
The identity matrix has ones on the leading diagonal, and zeroes elsewhere.


N
Returns the numeric value.
Syntax
N(value)
Syntax Description
Returns the numeric value of value if possible. It returns the logical values TRUE and FALSE as 1 and 0 respectively.
N() is not intended to convert text to a number - use the VALUE() function instead.


NA
Returns the #N/A (Not Available) error value.
Syntax
NA()
Syntax Description
This function has no arguments, and returns the error value #N/A.


NEGBINOMDIST
Calculates probabilities for a negative binomial distribution.
Syntax
NEGBINOMDIST(x; r; p)
Syntax Description
For independent trials each with a probability p of success, NEGBINOMDIST returns the probability that there will be exactly x failures before there have been r successes. 


NETWORKDAYS
Returns the number of workdays between two dates.
Syntax
NETWORKDAYS(startdate; enddate; hols_range)
Syntax Description
Returns the number of workdays between startdate and enddate (inclusive).
Saturday and Sunday are assumed to be non-working days. hols_range (optional) is a range containing dates of other non-working days.


NOMINAL
Returns a nominal interest rate given the effective compounded interest rate.
Syntax
NOMINAL(eff_rate; num)
Syntax Description
eff_rate: the effective interest rate.
num: the number of times interest is credited / compounded during the period that the nominal rate applies to.
If an investment has a nominal rate, say for a year, but interest is paid and credited say each quarter, the interest paid each quarter will itself start earning interest. This increases the effective value. The effective rate is the rate that would have to be paid at the end of the (say) year to give the same return.
Given an effective rate, this function returns the appropriate nominal rate.


NORMDIST
Calculates values for a normal distribution.
Syntax
NORMDIST(x; μ;σ; mode)
Syntax Description
The normal distribution is an often encountered family of continuous probability distributions, with parameters μ (mean) and σ (standard deviation).
If mode is 0, NORMDIST calculates the probability density function of the normal distribution.
If mode is 1, NORMDIST calculates the cumulative distribution function of the normal distribution.


NORMINV
Calculates the inverse of the cumulative NORMDIST normal distribution function.
Syntax
NORMINV(p; α; λ)
Syntax Description
The normal distribution is a family of continuous probability distributions, with two controlling parameters α and λ.
NORMINV returns the value n, such that NORMDIST(n; α; λ; 1) is p.


NORMSDIST
Calculates values for the cumulative distribution function of a normal distribution.
Syntax
NORMSDIST(x)
Syntax Description
The standard normal distribution is a normal distribution with mean μ = 0 and standard deviation �ƒ = 1.
NORMSDIST calculates the cumulative distribution function of the standard normal distribution.
It is equivalent to NORMDIST(x; 0; 1; 1).


NORMSINV
Calculates the inverse of the NORMSDIST function.
Syntax
NORMSINV(p)
Syntax Description
NORMSINV returns the value x, such that NORMSDIST(x) is p.
It is equivalent to NORMINV(p; 0; 1).


NOT
Reverses the logical value. Returns TRUE if the argument is FALSE, and FALSE if the argument is TRUE.
Syntax
NOT(logical_value)
Syntax Description
Where logical_value is the logical value to be reversed.


NOW
Returns the current date and time
Syntax
NOW()
Syntax Description
Returns the current date and time (as a date-time serial number). NOW is updated at every recalculation, for instance if a cell is modified.


NPER
Returns the number of payment periods for an annuity.
Syntax
NPER(rate; payment; presentvalue; futurevalue; type)
Syntax Description
rate: the (fixed) interest rate per period.
payment: the payment made each period.
presentvalue: the lump sum payment at the start of the term.
futurevalue: the cash balance paid at the end of the term (optional - defaults to 0).
type: when payments are made (optional - defaults to 0):
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


NPV
Returns the net present value of an investment with regular cash payments.
Syntax
NPV(discountrate; payment1; payment2; ... payment30)
Syntax Description
payment1 to payment30 are up to 30 numbers or ranges containing numbers, representing payments made at the end of each of a series of fixed length periods. The payments may be both positive and negative, for income and outgoing.
discountrate is the discount rate (expressed as a fraction of 1) which you consider applies to one single period. It is assumed to be constant for all periods.


OCT2BIN
Converts a octal number to binary.
Syntax
OCT2BIN(octalnumber; numdigits)
Syntax Description
Returns text representing a binary number, given octalnumber, which may be text, or a number (taken to be octal although it is not).
The binary number returned may have up to ten bits in two's complement representation; positive numbers are 0 to 111111111 (nine bits representing 0 to 511 decimal) and negative numbers 1111111111 to 1000000000 (ten bits representing -1 to -512 decimal).
octalnumber must therefore also lie in this range, and is given in twos complement form with up to ten digits.
numdigits is an optional number specifying the number of digits to return.


OCT2DEC
Converts an octal number to decimal.
Syntax
OCT2DEC(octalnumber)
Syntax Description
Returns a (decimal) number, given octalnumber, which may be text, or a number (taken to be octal although it is not).
octalnumber may have up to ten digit in twos complement representation; positive numbers are 0 to 3777777777 (representing 0 to 229-1 decimal) and negative numbers 7777777777 to 4000000000 (representing -1 to -229 decimal).


OCT2HEX
Converts a octal number to hexadecimal.
Syntax
OCT2HEX(octalnumber; numdigits)
Syntax Description
Returns text representing a hexadecimal number, given octalnumber, which may be text, or a number containing only digits 0 to 7 (thus the number appears to be octal although it is not).
octalnumber may have up to ten digit in twos complement representation; positive numbers are 0 to 3777777777 (representing 0 to 229-1 decimal) and negative numbers 7777777777 to 4000000000 (representing -1 to -229 decimal).
numdigits is an optional number specifying the number of digits to return.
If octalnumber is negative, OCT2HEX returns ten octal digits, representing the octal number in twos complement form.


ODD
Rounds a number up, away from zero, to the next odd integer.
Syntax
ODD(number)
Syntax Description
Returns number rounded to the next odd integer up, away from zero.


ODDFPRICE
Returns the value of a security per 100 currency units of face value, where the time to the first interest payment is not a whole period.
Syntax
ODDFPRICE(settlementdate; maturitydate; issuedate; firstinterestdate; rate; yield; redemptionvalue; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the security.
maturitydate: the maturity date of the security.
issuedate: the issue date of the security.
firstinterestdate: the date on which the security first pays interest.
rate: the interest rate of the security.
yield: the annual yield of the security.
redemptionvalue: the redemption value of the security per 100 currency units of face value.
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


ODDFYIELD
Returns the yield of a security, where the time to the first interest payment is not a whole period.
Syntax
ODDFYIELD(settlementdate; maturitydate; issuedate; firstinterestdate; rate; price; redemptionvalue; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the security.
maturitydate: the maturity date of the security.
issuedate: the issue date of the security.
firstinterestdate: the date on which the security first pays interest.
rate: the interest rate of the security.
price: the price of the security.
redemptionvalue: the redemption value of the security per 100 currency units of face value.
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


ODDLPRICE
Returns the value of a security per 100 currency units of face value, where the last interest payment is not a whole period.
Syntax
ODDLPRICE(settlementdate; maturitydate; lastinterestdate; rate; yield; redemptionvalue; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the security.
maturitydate: the maturity date of the security.
lastinterestdate: the date on which the security last pays interest.
rate: the interest rate of the security.
yield: the annual yield of the security.
redemptionvalue: the redemption value of the security per 100 currency units of face value.
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


ODDLYIELD
Returns the yield of a security, where the last interest payment is not a whole period.
Syntax
ODDLYIELD(settlementdate; maturitydate; lastinterestdate; rate; price; redemptionvalue; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the security.
maturitydate: the maturity date of the security.
lastinterestdate: the date on which the security last pays interest.
rate: the interest rate of the security.
price: the price of the security.
redemptionvalue: the redemption value of the security per 100 currency units of face value.
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


OFFSET
Returns a modified reference, given a reference, an offset, and a desired size.
Syntax
OFFSET(reference; row_offset; col_offset; new_height; new_width)
Syntax Description
reference is the given reference, which may be a range.
row_offset and col_offset are the number of rows / columns to move reference down / right by. Negative numbers are allowed.
new_height and new_width set the height and width of the new reference, by adjusting the bottom right corner. These are optional parameters.


OR
Returns TRUE if any of the arguments are considered TRUE, and FALSE otherwise.
Syntax
OR(argument1; argument2 ...argument30)
Syntax Description


PEARSON
Returns the Pearson correlation coefficient of two sets of data.
Syntax
PEARSON(x; y)
Syntax Description
Where x and y are ranges or arrays containing the two sets of data.
Any text or empty entries are ignored.


PERCENTILE
Returns a specified percentile in a list of numbers.
Syntax
PERCENTILE(numberlist; fraction)
Syntax Description
PERCENTILE returns the value below which fraction of the numbers in numberlist lie.
fraction
must be between 0 and 1 inclusive.
numberlist
is a range or array of numbers, not necessarily in order..
PERCENTILE internally assigns a rank to each number in numberlist, where 0 is the rank of the lowest number, 1 of the next lowest and so on. The rank r of the value to be found is fraction * (N-1), where N is the count of numbers in numberlist. If r is integer the corresponding value from numberlist is returned; otherwise the value is calculated proportionately between the values with rank |r| and |r|+1.
This function may be useful for example when determining a score below which lies a certain percentage of all scores for a test. PERCENTILE returns the maximum, median, minimum value when fraction is 1, 0.5, 0 respectively.


PERCENTRANK
Returns the percentage rank of a number in a list of numbers.
Syntax
PERCENTRANK(numberlist; number)
Syntax Description
number is the number whose percentage rank is to be found within the unordered range or array of numbers numberlist.
PERCENTRANK internally assigns a rank r to number, where 0 is the rank of the lowest number, 1 of the next lowest and so on. If number is not in numberlist, it is assigned a fractional rank proportionately between the rank of the numbers on either side (see the examples). Where N is the count of numbers in numberlist, PERCENTRANK returns r / (N-1), which is a fraction between 0 and 1 inclusive. It may be useful for example when determining where a score lies within all scores for a test, in which case the formula returned can be stated as L / (N-1), where L is the count of scores which are lower than number.


PERMUT
Returns the number of ordered permutations for a given number of objects.
Syntax
PERMUT(n; k)
Syntax Description
Where n and k are integers.
PERMUT returns the number of ordered ways that k objects can be chosen from a set of n objects, where an object can only be chosen once. For example with a set of 3 objects A, B, C, we can choose 2 as follows: AB, AC, BA, BC, CA, CB.


PERMUTATIONA
Returns the number of ordered permutations, allowing repetition.
Syntax
PERMUTATIONA(n; k)
Syntax Description
Where n and k are integers.
PERMUTATIONA returns the number of ordered ways that k objects can be chosen from a set of n objects, where an object can be chosen repeatedly. For example with a set of 3 objects A, B, C, we can choose 2 as follows: AA, AB, AC, BA, BB, BC, CA, CB, CC.
PERMUTATIONA simply calculates nk.


PHI
Calculates values for a standard normal distribution.
Syntax
PHI(x)
Syntax Description
The standard normal distribution is a normal distribution with mean μ = 0 and standard deviation �ƒ = 1.
PHI calculates the probability density function of the standard normal distribution; in other words it returns NORMDIST(x; 0; 1; 0).


PI
Returns 3.14159265358979, the value of the mathematical constant PI to 14 decimal places.
Syntax
PI()
Syntax Description


PMT
Returns the payment per period for a fixed rate loan.
Syntax
PMT(rate; numperiods; principal; finalbalance; type)
Syntax Description
rate: the interest rate per period.
numperiods: the total number of payment periods in the term.
principal: the initial sum borrowed.
finalbalance: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0.
type: when payments are made (optional - defaults to 0):
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


POISSON
Calculates values for a Poisson distribution.
Syntax
POISSON(x; λ; mode)
Syntax Description
The Poisson distribution is a discrete probability distribution giving the probability that x events occur in a certain time, where events occur independently, and where on average λ events are expected. x should be >=0 and λ should be >0. x should be integer.
If mode is 0, POISSON calculates the probability density function of the Poisson distribution.
If mode is 1, POISSON calculates the cumulative distribution function of the Poisson distribution.


POWER
Returns a number raised to a power.
Syntax
POWER(number; power)
Syntax Description
Returns numberpower, that is number raised to the power of power.
The same result may be achieved by using the exponentiation operator ^:
number^power


PPMT
Returns the portion of the periodic payment which is repaid capital for a fixed rate loan or annuity.
Syntax
PPMT(rate; period; numperiods; presentvalue; futurevalue; type)
Syntax Description
rate: the interest rate per period.
period: the period of the payment whose repaid capital portion is to be calculated, numbered from 1.
numperiods: the total number of payment periods in the term.
presentvalue: the initial sum borrowed or invested.
futurevalue: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0.
type: when payments are made (optional - defaults to 0):
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


PRICE
Calculates a quoted price for an interest paying security, per 100 currency units par value.
Syntax
PRICE(settlementdate; maturitydate; rate; yield; redemptionvalue; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the security.
maturitydate: the maturity (redemption) date of the security.
rate: the (annual) coupon rate of the security.
yield: the required annual rate of return (compounded at each interest payment).
redemptionvalue: the redemption value of the security, per 100 par value.
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


PRICEDISC
Calculates a price for a non-interest paying discounted bond.
Syntax
PRICEDISC(settlementdate; maturitydate; discountrate; redemptionvalue; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the bond.
maturitydate: the maturity (redemption) date of the bond.
discountrate: the (annual) discount rate of the bond.
redemptionvalue: the redemption value of the bond, per 100 par value.
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


PRICEMAT
Calculates a price (per 100 currency units par value) for a bond that pays interest on maturity.
Syntax
PRICEMAT(settlementdate; maturitydate; issuedate; rate; yield; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the bond.
maturitydate: the maturity (redemption) date of the bond.
issuedate: the original issue date of the bond.
rate: the (annual) interest rate of the bond (interest only paid at maturity).
yield: the (annual) yield of the bond.
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


PROB
Returns a result from a list of probabilities.
Syntax
PROB(values; probabilities; start; end)
Syntax Description
values is a range or array of numbers (possibly unordered).
probabilities is a range or array of numbers of the same size as values, indicating the corresponding probability (>0 and <=1) that each value in values will occur. The numbers in probabilities must sum to 1 exactly.
PROB finds all values which are between start and end inclusive and returns the sum of their corresponding probabilities.
end may be omitted, in which case PROB returns the probability corresponding to start (or 0 if start is not present in values).


PRODUCT
Multiplies all the numbers given as arguments and returns the product.
Syntax
PRODUCT(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges/arrays of numbers whose product is to be calculated.
PRODUCT returns number1 * number2 * number3 * ........
PRODUCT ignores any text or empty cell within a range or array.


PROPER
returns text with words in lowercase after a capitalised first letter.
Syntax
PROPER(text)
Syntax Description
Returns text with the first character of each word capitalised, and other characters in lower case.


PV
Returns the present value of a stream of future payments with a final lump sum.
Syntax
PV(rate; numperiods; payment; futurevalue; type)
Syntax Description
rate: the (fixed) interest rate per period.
numperiods: the total number of payment periods in the term.
payment: the payment made each period. If futurevalue is given, this may omitted (defaults to 0).
futurevalue: the cash balance you wish to attain at the end of the term (optional - defaults to 0). With a loan, this would normally be 0; with a bond this would be the redemption value.
type: when payments are made (optional - defaults to 0):
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).


QUARTILE
Returns a specified quartile in a list of numbers.
Syntax
QUARTILE(numberlist; q)
Syntax Description
QUARTILE returns the value below which q quarters of the numbers in numberlist lie. q may be 0 (to return the minimum value), 1 (to return the value below which a quarter of values lie), 2 (to return the median value), 3 (to return the value below which three quarters of values lie) or 4 (to return the maximum value). numberlist is a range or array of numbers, not necessarily in order.


QUOTIENT
Returns the integer part of a division operation.
Syntax
QUOTIENT(numerator; denominator)
Syntax Description
Returns the integer part of numerator divided by denominator.
QUOTIENT is equivalent to INT(numerator/denominator), except that it may report errors with different error codes.


RADIANS
Converts degrees to radians.
Syntax
RADIANS(degrees)
Syntax Description
degrees is the angle in degrees to be converted to radians.


RAND
Returns a random number between 0 and 1.
Syntax
RAND()
Syntax Description
This function produces a new random number each time Calc recalculates, greater than or equal to 0, and less than 1.


RANDBETWEEN
Returns an integer random number in a specified range.
Syntax
RANDBETWEEN(bottom; top)
Syntax Description
Returns an integer random number between integers bottom and top (both inclusive).
This function produces a new random number each time Calc recalculates. 


RANK
Returns the rank of a number in a list of numbers.
Syntax
RANK(number; numberlist; order)
Syntax Description
Returns the rank of number within the unordered range or array of numbers numberlist.
If order is zero or omitted, numbers are ranked with the highest being first; if order is non-zero, numbers are ranked with the lowest being first.
An error results if number is not present in numberlist.


RATE
Calculates the interest rate for an annuity.
Syntax
RATE(numperiods; payment; presentvalue; futurevalue; type; guess)
Syntax Description
rate: the (fixed) interest rate per period.
numperiods: the total number of payment periods in the term.
payment: the payment made each period. If futurevalue is given, this may omitted (defaults to 0).
presentvalue: the lump sum payment at the start of the term.
futurevalue: the cash balance you wish to attain at the end of the term (optional - defaults to 0).
type: when payments are made (optional - defaults to 0):
0 - at the end of each period.
1 - at the start of each period (including a payment at the start of the term).
guess: an optional guess of the interest rate (defaults to 10%).


RECEIVED
Calculates the amount received at maturity for a zero coupon
Syntax
RECEIVED(settlementdate; maturitydate; purchasevalue; discountrate; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the bond.
maturitydate: the maturity (redemption) date of the bond.
purchasevalue: the value of the bond at purchase.
discountrate: the discount rate of the bond.
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


REPLACE
Replaces part of a text string with a different text string.
Syntax
REPLACE(originaltext; startposition; length; newtext)
Syntax Description
In originaltext, removes length characters beginning at character startposition, replaces them with newtext, and returns the result.
startposition and length must be 1 or more.


REPT
returns a text string comprised of copies of another text string.
Syntax
REPT(text; number)
Syntax Description
Returns number copies of text, joined together.


RIGHT
Returns text from the right side of a text string.
Syntax
RIGHT(text; number)
Syntax Description
Returns number characters from the right side of the text text.
number defaults to 1 if omitted.


ROMAN
Returns a Roman numeral (eg XIV), as text, given a number.
Syntax
ROMAN(number; mode)
Syntax Description
number is a number in the range 1-3999 that is to be converted into a Roman numeral.
mode (optional, in the range 0-4) indicates the degree of simplification. The higher the value, the greater is the simplification of the Roman number.


ROUND
Rounds a number to a certain precision.
Syntax
ROUND(number; places)
Syntax Description
Returns number rounded to places decimal places. If places is omitted or zero, the function rounds to the nearest integer. If places is negative, the function rounds to the nearest 10, 100, 1000, etc.
This function rounds to the nearest number. 


ROUNDDOWN
Rounds a number down, toward zero, to a certain precision.
Syntax
ROUNDDOWN(number; places)
Syntax Description
Returns number rounded down (towards zero) to places decimal places. If places is omitted or zero, the function rounds down to an integer. If places is negative, the function rounds down to the next 10, 100, 1000, etc.
This function rounds towards zero


ROUNDUP
Rounds a number up, away from zero, to a certain precision.
Syntax
ROUNDUP(number; places)
Syntax Description
Returns number rounded up (away from zero) to places decimal places. If places is omitted or zero, the function rounds up to an integer. If places is negative, the function rounds up to the next 10, 100, 1000, etc.
This function rounds away from zero


ROW
Returns the row number(s), given a reference.
Syntax
ROW(reference)
Syntax Description
Returns the row number of reference, where reference is a reference to a cell.
If reference is omitted, the row number of the current cell (containing the formula) is returned.


ROWS
Returns the number of rows in a given reference.
Syntax
ROWS(reference)
Syntax Description
reference may be given either explicitly (eg A3:B5) or by name (eg myref).


RRI
Returns an equivalent interest rate when an investment increases in value.
Syntax
RRI(numperiods; presentvalue; futurevalue)
Syntax Description
numperiods: the number of periods in the term.
presentvalue: the value at the start of the term.
futurevalue: the value at the end of the term.
RRI calculates the fixed interest rate needed, so that presentvalue invested for numperiods (and compounded each period) is worth futurevalue at the end of the term. RRI returns a fixed interest rate which applies to each period.


RSQ
Returns the square of the Pearson correlation coefficient of two sets of data.
Syntax
RSQ(x; y)
Syntax Description
Where x and y are ranges or arrays containing the two sets of data.
Any text or empty entries are ignored.
RSQ calculates the square of the Pearson correlation coefficient (which is conventionally given the letter r, hence "r squared").


SEARCH
Returns the position of a string of text within another string.
Syntax
SEARCH(findtext; texttosearch; startposition)
Syntax Description
Returns the character position of the first occurrence of findtext within texttosearch.
startposition (optional) is the position from which the search starts.
The search is not case-sensitive.
A failed search gives the #VALUE! error.


SECOND
Returns the seconds of a given time.
Syntax
SECOND(time)
Syntax Description
Returns the seconds of time as a number, 0 - 59.
time may be text or a date-time serial number.


SERIESSUM
Sums the first terms of a power series.
Syntax
SERIESSUM(x; n; m; ar)
Syntax Description
x is the variable,
n is the power of x for the first term,
m is the increment by which the power of x increases with each term, and
ar refers to a range containing the a coefficients of the terms to be included.


SHEET
Returns the sheet number, given a reference.
Syntax
SHEET(reference)
Syntax Description
Returns the sheet number of reference, where reference is a reference to a cell.
If reference is omitted, the sheet number of the current sheet (containing the formula) is returned.


SIGN
Returns the sign of a number:- 1 if the number is positive, -1 if negative and 0 if zero.
Syntax
SIGN(number)
Syntax Description
number is the number whose sign is to be determined.


SIN
Returns the sine of the given angle (in radians).
Syntax
SIN(angle)
Syntax Description
Returns the (trigonometric) sine of angle, the angle in radians.
To return the sine of an angle in degrees, use the RADIANS function.


SINH
Returns the hyperbolic sine of a number.
Syntax
SINH(number)
Syntax Description
Returns the hyperbolic sine of number.


SKEW
Returns a measure of how skewed a distribution is.
Syntax
SKEW(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges/arrays containing numbers.
SKEW returns a measure of how skewed a distribution is, relative to a normal distribution - that is, how asymmetric it is. Positive values indicate a distribution with a tail inclining to the positive side, and negative values a distribution with a tail inclining to the negative side. 


SLN
Returns the depreciation of an asset in a single period using the straight-line depreciation method.
Syntax
SLN(originalcost; salvagevalue; lifetime)
Syntax Description
originalcost: the initial cost of the asset.
salvagevalue: is the value at the end of the depreciation (sometimes called the salvage value of the asset).
lifetime: the number of periods (commonly years) over which the asset is being depreciated.
The straight-line depreciation method depreciates the asset by the same constant value every period; that value is returned by the SLN function. 


SLOPE
Fits a straight line to data using linear regression and returns its slope.
Syntax
SLOPE(yvalues; xvalues)
Syntax Description
yvalues and xvalues are single row or column ranges specifying points in a set of data. yvalues and xvalues must be the same size.
SLOPE fits a straight line through these data points, using the linear regression method (least squares). It then returns the slope of that line.


SMALL
Returns the nth smallest value in a list of numbers.
Syntax
SMALL(numberlist; n)
Syntax Description
Returns the nth smallest number within the (unordered) range or array of numbers numberlist.


SQRT
Returns the positive square root of a number.
Syntax
SQRT(number)
Syntax Description
Returns the positive square root of number.
number must be positive.


SQRTPI
Returns the square root of (PI times a number).
Syntax
SQRTPI(number)
Syntax Description
Returns the positive square root of ( PI multiplied by number ).
This is equivalent to SQRT(PI()*number).


STANDARDIZE
Converts a value in a normal distribution to its equivalent in a standard normal distribution.
Syntax
STANDARDIZE(n; μσ)
Syntax Description
n is a number in a normal distribution with known mean μ and known standard deviation σ.
STANDARDIZE converts n to a corresponding value in a standard normal distribution, which has mean 0 and standard deviation 1.


STDEV
Returns the sample standard deviation of the arguments.
Syntax
STDEV(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.
STDEV returns the standard deviation where number1 to number30 are a sample of the entire population. .


STDEVA
Returns the sample standard deviation of the arguments.
Syntax
STDEVA(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers. Logical values and text may also be included.
STDEVA returns the standard deviation where number1 to number30 are a sample of the entire population.
Logical values are regarded as 1 ( TRUE) and 0 ( FALSE).
Text values are always regarded as zero..


STDEVP
Returns the population standard deviation.
Syntax
STDEVP(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.
STDEVP returns the standard deviation where number1 to number30 are the entire population. If you only have a sample of the population use STDEV instead. .


STDEVPA
Returns the population standard deviation (allowing text and logical values).
Syntax
STDEVPA(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers. Logical values and text may also be included.
STDEVPA returns the standard deviation where number1 to number30 are the entire population. If you only have a sample of the population use STDEVA instead.
Logical values are regarded as 1 ( TRUE) and 0 ( FALSE).
Text values are always regarded as zero..


STEYX
Fits a straight line to data using linear regression and returns the standard error of y values.
Syntax
STEYX(yvalues; xvalues)
Syntax Description
yvalues and xvalues are single row or column ranges specifying points in a set of data. yvalues and xvalues must be the same size.
STEYX in effect fits a straight line through these data points, using the linear regression method (least squares). It then returns the standard error of actual y values compared to y values on the straight line found.


STOCK
Returns the stock details for the symbol provided.
Syntax
STOCK(Symbol; Function)
Syntax Description
Symbol: represent the stock symbol of the company.
Function: represents the type of data you want, this is a optional parameter[Default value is price].
Example: =STOCK("GOOG","PRICE"),   =STOCK("MSFT"),    =STOCK("YHOO","VOLUME")
supported functions
price - price of stock.
tradetime - last traded time of stock in PST.
dayopen - price when the current day open.
dayhigh - highest traded price for the current day.
daylow - lowest traded price for the current day.
volume - volume of shares traded for the day.
close - price at previous day close.
change - %change from previous day close.
52weekhigh - highest price in last 52 weeks.
52weeklow - lowest price in last 52 weeks.
Note:Stock details are provided from free stock sources available in the Web. The data may be delayed up to 30 minutes. Also the data provided is for informational purposes only.


SUBSTITUTE
Substitutes new text for old text in a text string.
Syntax
SUBSTITUTE(originaltext; oldtext; newtext; which)
Syntax Description
In originaltext, removes oldtext, inserts newtext in its place, and returns the result. oldtext and newtext can have different lengths.
which (optional) is a number which specifies which occurrence of oldtext to replace (counting from the left). If omitted, all occurrences are replaced.


SUBTOTAL
Returns SUM, AVERAGE, STDEV, etc. results for filtered data.
Syntax
SUBTOTAL(function; range)
Syntax Description
range is the overall range from which cells for calculation are selected by filtering.
function is a number that specifies the function to calculate, as follows:
functionFunction
1AVERAGE
2COUNT
3COUNTA
4MAX
5MIN
6PRODUCT
7STDEV
8STDEVP
9SUM
10VAR
11VARP
If range contains other SUBTOTAL functions they are ignored to avoid double counting.


SUM
Sums the contents of cells.
Syntax
SUM(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges/arrays of numbers whose sum is to be calculated.
SUM ignores any text or empty cell within a range or array.


SUMIF
Conditionally sums the contents of cells in a range.
Syntax
SUMIF(test_range; condition; sum_range)
Syntax Description
This function identifies those cells in the range test_range that meet the condition, and sums the corresponding cells in the range sum_range. If sum_range is omitted the cells in test_range are summed.
condition may be:
A number, such as 34.5
An expression, such as 2/3 or SQRT(B5)
A text string
SUMIF looks for cells in test_range that are equal to condition, unless condition is a text string that starts with a comparator:
>, <, >=, <=, =, <>
In this case SUMIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).


SUMPRODUCT
Returns the sum of the products of corresponding array elements.
Syntax
SUMPRODUCT(array1; array2; ... array30)
Syntax Description
array1 to array30 are up to 30 arrays or ranges of the same size whose corresponding elements are to be multiplied.


SUMSQ
Returns the sum of the squares of the arguments.
Syntax
SUMSQ(number1; number2; .... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges of numbers which are squared and then summed.


SUMX2MY2
Returns the sum of the differences between corresponding squared elements of two matrices.
Syntax
SUMX2MY2(x; y)
Syntax Description
x and y are arrays or ranges of the same size. 


SUMX2PY2
Returns the sum of the squares of all elements of two matrices.
Syntax
SUMX2PY2(x; y)
Syntax Description
x and y are arrays or ranges of the same size. 


SUMXMY2
Returns the sum of the squared differences between corresponding elements of two matrices.
Syntax
SUMXMY2(x; y)
Syntax Description
x and y are arrays or ranges of the same size. 


SYD
Returns the depreciation of an asset for a given year using the sum-of-years'-digits method.
Syntax
SYD(originalcost; salvagevalue; lifetime; year)
Syntax Description
originalcost: the initial cost of the asset.
salvagevalue: is the value at the end of the depreciation (sometimes called the salvage value of the asset).
lifetime: the number of years over which the asset is being depreciated.
year: the year number for which the depreciation is calculated.


T
Returns the given text, or an empty text string if the target is not text.
Syntax
T(value)
Syntax Description
If value is text, it is returned.
If value is a number or a logical value, an empty text string "" is returned.


TAN
Returns the tangent of the given angle (in radians).
Syntax
TAN(angle)
Syntax Description
Returns the (trigonometric) tangent of angle, the angle in radians.
To return the tangent of an angle in degrees, use the RADIANS function.


TANH
Returns the hyperbolic tangent of a number.
Syntax
TANH(number)
Syntax Description
Returns the hyperbolic tangent of number.


TBILLEQ
Returns the bond-equivalent-yield (BEY) for a US Treasury bill.
Syntax
TBILLEQ(settlementdate; maturitydate; discount)
Syntax Description
settlementdate: the settlement (purchase) date of the Treasury bill.
maturitydate: the maturity (redemption) date of the Treasury bill.
discountrate: the discount rate of the Treasury bill.
A Treasury bill is a short term (up to a year) Government security, sold at a discount to its par value (face value). It pays no interest and is redeemed at par value. This function calculates the yield that a bond would need, in order to provide growth equivalent to the Treasury bill. The bond considered assumes 365 days in the year, and pays interest only at the end of the term (ie interest is not compounded).The Treasury bill has a 360 day year basis.


TBILLPRICE
Returns the issue price for a US Treasury bill, per $100 par value, given a discount rate.
Syntax
TBILLPRICE(settlementdate; maturitydate; discountrate)
Syntax Description
settlementdate: the settlement (purchase) date of the Treasury bill.
maturitydate: the maturity (redemption) date of the Treasury bill.
discountrate: the discount rate of the Treasury bill.
A Treasury bill is a short term (up to a year) Government security, sold at a discount to its par value (face value). It pays no interest and is redeemed at par value. The Treasury bill here has a 360 day year basis.


TBILLYIELD
Returns the yield for a US Treasury bill.
Syntax
TBILLYIELD(settlementdate; maturitydate; issueprice)
Syntax Description
settlementdate: the settlement (purchase) date of the Treasury bill.
maturitydate: the maturity (redemption) date of the Treasury bill.
issueprice: the issue price of the Treasury bill per $100 of par value.
A Treasury bill is a short term (up to a year) Government security, sold at a discount to its par value (face value). It pays no interest and is redeemed at par value. The Treasury bill here has a 360 day year basis.


TDIST
Calculates values for a Student's t-distribution.
Syntax
TDIST(x; r; mode)
Syntax Description
x is the value at which the distribution is calculated.
r, an integer >=1, is the degrees of freedom parameter for the t-distribution.
mode may be 1 or 2, to calculate the one or two tailed probability.
TDIST returns the probability density function for the t-distribution.


TEXT
Converts a number into text according to a given format.
Syntax
TEXT(number; format)
Syntax Description
Returns number converted to text, according to the format code specified by format.


TIME
returns the time, given hours, minutes and seconds.
Syntax
TIME(hours; minutes; seconds)
Syntax Description
Returns the time, expressed as a date-time serial number. hours, minutes and seconds are integers.


TIMEVALUE
returns the date-time serial number, from a time given as text.
Syntax
TIMEVALUE(timetext)
Syntax Description
timetext is a time, expressed as text.
TIMEVALUE returns the date-time serial number, which may be formatted to read as a time.


TINV
Calculates the inverse of the two-tailed TDIST function.
Syntax
TINV(p; r)
Syntax Description
Returns the value x, such that TDIST(x; r; 2) is p.
r (degrees of freedom) is an integer >=1.
p (probability) must be >=0 and <=1.
To calculate the inverse of the one-tailed TDIST function, use TINV(p*2; r), which returns the value x, such that TDIST(x; r; 1) is p.


TODAY
Returns the current date and time
Syntax
TODAY()
Syntax Description
Returns the current date (as a date-time serial number). TODAY is updated at every recalculation, for instance if a cell is modified.


TRANSPOSE
Returns an array with its rows and columns transposed.
Syntax
TRANSPOSE(array)
Syntax Description
array may be either an inline array or a range, containing all numbers.


TREND
Fits a straight line to data using linear regression and returns points on that line.
Syntax
TREND(yvalues; xvalues; new_xvalues; type)
Syntax Description
yvalues and xvalues are single row or column ranges specifying points in a set of data.
TREND fits a straight line through these data points, using the linear regression method.
If type is 0 the straight line found will pass through the origin.
If type is non-zero or omitted the best fit straight line will be found.


TRIM
removes excess spaces from a text string.
Syntax
TRIM(text)
Syntax Description
Returns text with any leading or trailing spaces removed, and with any multiple spaces replaced with a single space.


TRIMMEAN
Returns the mean of a set of numbers, ignoring a proportion of high and low values.
Syntax
TRIMMEAN(numberlist; fraction)
Syntax Description
numberlist is a range or array of numbers, not necessarily in order. TRIMMEAN returns the mean, ignoring a fraction (0 <= fraction < 1) of the outermost values in numberlist.
Specifically, where n is the count of numbers in numberlist, the highest and lowest INT(n * fraction / 2) values are ignored.


TRUE
Returns the logical value TRUE.
Syntax
TRUE()
Syntax Description
The TRUE() function has no arguments, and always returns the logical value TRUE.


TRUNC
Truncates a number by removing decimal places.
Syntax
TRUNC(number; places)
Syntax Description
Returns number with at most places decimal places. Excess decimal places are simply removed, irrespective of sign.
TRUNC(number; 0) behaves as INT(number) for positive numbers, but effectively rounds towards zero for negative numbers.


TTEST
Returns the result of a Student's t-test.
Syntax
TTEST(data1; data2; mode; type)
Syntax Description
data1 and data2 are ranges or arrays (possibly of different size) containing numbers, on which the t-test is performed.
mode is
1 for a one-tailed t-test
2 for a two-tailed t-test.
type is
1 for paired samples
2 for two samples with equal variance
3 for two samples with unequal variance.


TYPE
Returns the type of value (number, text, etc.).
Syntax
TYPE(value)
Syntax Description
Returns the type of value as a number: 1 = number, 2 = text, 4 = logical value, 8 = formula, 16 = error value.
If a cell contains both an error and a formula, 16 (error value) is returned.
A blank cell is classed as a number here, and 1 is returned.


UPPER
Converts a text string to uppercase.
Syntax
UPPER(text)
Syntax Description
Returns text with all characters converted to upper case.


VALUE
Returns a number, given a text representation.
Syntax
VALUE(text)
Syntax Description
Converts text to a number. text may represent: a number, including fractions, exponential notation and (locale dependent) decimal point and thousands separator; a percentage; or a date or time (the datetime serial number is returned).


VAR
Returns the sample variance.
Syntax
VAR(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.
VAR returns the variance where number1 to number30 are a sample of the entire population.


VARA
Returns the sample variance (allowing text and logical values).
Syntax
VARA(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers. Logical values and text may also be included.
VARA returns the standard deviation where number1 to number30 are a sample of the entire population.
Logical values are regarded as 1 ( TRUE) and 0 ( FALSE).
Text values are always regarded as zero.


VARP
Returns the population variance.
Syntax
VARP(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers.
VARP returns the standard deviation where number1 to number30 are the entire population. If you only have a sample of the population use VAR instead. 


VARPA
Returns the population variance (allowing text and logical values).
Syntax
VARPA(number1; number2; ... number30)
Syntax Description
number1 to number30 are up to 30 numbers or ranges containing numbers. Logical values and text may also be included.
VARPA returns the variance where number1 to number30 are the entire population. If you only have a sample of the population use VARA instead.
Logical values are regarded as 1 ( TRUE) and 0 ( FALSE).
Text values are always regarded as zero..


VDB
Returns the depreciation of an asset for a given year using a variable declining-balance method.
Syntax
VDB(originalcost; salvagevalue; lifetime; periodstart; periodend; factor; nostraightline)
Syntax Description
originalcost: the initial cost of the asset.
salvagevalue: is the value at the end of the depreciation (sometimes called the salvage value of the asset).
lifetime: the number of years over which the asset is being depreciated.
periodstart, periodend: the start and end year numbers defining the period for which the depreciation is calculated. These may be fractional, from 0 to lifetime.
factor: the factor to set the depreciation rate (2 if omitted).
nostraightline: TRUE to prevent VDB from using any straight line depreciation ( FALSE if omitted - ie if omitted, straight line depreciation will be used where appropriate)


VLOOKUP
Returns a value from a table column, in the row found by lookup in the first column.
Syntax
VLOOKUP(lookupvalue; datatable; columnindex; mode)
Syntax Description
lookupvalue is a value (number, text or logical value) to look up in the left column of the range/array datatable. When a value is matched in the left column, VLOOKUP returns the corresponding value (in the same row) in the columnindexth column of datatable, where columnindex = 1 is the left column.
If mode is 0 or FALSE, the left column of datatable may be unordered, and the first exact match is found (searching from the top).
If mode is 1 or TRUE, or is omitted, the left column of datatable must be sorted, with numbers in ascending order appearing before text values in alphabetic order.
VLOOKUP
decides where in the left column lookupvalue would appear. If there is an exact match, that is the row found; if there is more than one exact match, the row found is not necessarily nearest the top. If there is no exact match, the row above where value would appear in the left column is found; the #N/A error results if that row is not in the table.


WEEKDAY
Returns the day of the week for a given date.
Syntax
WEEKDAY(date; type)
Syntax Description
Returns the day of the week that date falls on, as a number.
The day is returned as an integer between 1 (Sunday) and 7 (Saturday) if no type or type = 1 is specified. If type=2, numbering begins at Monday=1; and if type=3 numbering begins at Monday=0.


WEEKNUM
Returns the ISO week number of a given date.
Syntax
WEEKNUM(date; mode)
Syntax Description
Returns the international standard ISO8601 week number, where week 1 is the week that contains January 4th.
date is the date as a date-time serial number or as text.
mode specifies which day is considered the first day of the week: 1 for Sunday, 2 for Monday


WEEKNUM_ADD
Returns the non-ISO week number of a given date.
Syntax
WEEKNUM_ADD(date; mode)
Syntax Description
Returns the 'conventional' non-ISO week number, where week 1 is the week that contains January 1st.
date is the date as a date-time serial number or as text.
mode specifies which day is considered the first day of the week: 1 for Sunday, 2 for Monday.


WEEKS
Returns the number of weeks between two dates.
Syntax
WEEKS(startdate; enddate; mode)
Syntax Description
If mode is 0, WEEKS returns the number of whole weeks between startdate and enddate - that is INT(number_of_days_difference / 7).
If mode is 1, WEEKS identifies the Monday_to_Sunday week that startdate and enddate each lie in, and returns the difference between those weeks.
If startdate is after enddate the result will be negative.


WEEKSINYEAR
Returns the number of weeks in a year.
Syntax
WEEKSINYEAR(date)
Syntax Description
Returns the number of weeks in the year in which date lies.
A week is considered to start on Monday, and is considered to be in the year if most of its days are in that year.


WEIBULL
Calculates values for a Weibull distribution.
Syntax
WEIBULL(x; k; λ; mode)
Syntax Description
The Weibull distribution is a continuous probability distribution, with parameters k > 0 (shape) and λ > 0 (scale).
If mode is 0, WEIBULL calculates the probability density function of the Weibull distribution.
If mode is 1, WEIBULL calculates the cumulative distribution function of the Weibull distribution.


WORKDAY
Returns a date a given number of workdays away.
Syntax
WORKDAY(startdate; days; hols_range)
Syntax Description
Returns the date that is days workdays after the given date startdate.
days is a number (which may be negative to find a date before startdate).
Saturday and Sunday are assumed to be non-working days.
hols_range
(optional) is a range containing dates of other non-working days.


XIRR
Calculates the internal rate of return of a series of irregular cash flows.
Syntax
XIRR(payments; dates; guess)
Syntax Description
payments is a range containing the payments made or received, at irregular intervals.
dates is a range containing dates on which the payments were made or received.
guess (optional, defaults to 10%) is a first guess at the rate.
XIRR iterates to find the rate of return which gives a zero net present value for the cash flows. At least one of the cash flows must be negative and at least one positive - to allow the net present value to be zero. The rate of return is per annum, and interest is assumed compounded annually, with a year assumed to be 365 days long. The order in which the payments/dates are stated is not important, except that the first payment given must have the earliest date.


XNPV
Returns the net present value of an investment with irregular cash payments.
Syntax
XNPV(rate; payments; dates)
Syntax Description
rate is the annual interest rate.
payments is a range or array containing payments made, positive if paid to you, negative if you pay.
dates is a range or array containing the dates on which those payments are made.


YEAR
Returns the year of a given date.
Syntax
YEAR(date)
Syntax Description
Returns the year of date as a number.
date may be text or a date-time serial number.


YEARFRAC
Returns the number of years including fraction between two dates.
Syntax
YEARFRAC(startdate; enddate; basis)
Syntax Description
Returns the number of years between the startdate and enddate; basis is an integer specifying the system of how many days are in a month or year, as follows:


YEARS
Returns the number of years between two dates.
Syntax
YEARS(startdate; enddate; mode)
Syntax Description
If mode is 0, YEARS returns the number of whole years between startdate and enddate, day/month to day/month.
If mode is 1, YEARS identifies the year that startdate and enddate each lie in, and returns the difference between those years. In other words it returns YEAR(enddate) - YEAR(startdate)
If startdate is after enddate the result will be negative.


YIELD
Calculates the yield for an interest paying security.
Syntax
YIELD(settlementdate; maturitydate; rate; price; redemptionvalue; frequency; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the security.
maturitydate: the maturity (redemption) date of the security.
rate: the (annual) coupon rate of the security.
price: the price of the security, per 100 par value.
redemptionvalue: the redemption value of the security, per 100 par value.
frequency: number of interest payments per year (1, 2 or 4).
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each


YIELDDISC
Calculates the yield for a non-interest paying discounted bond.
Syntax
YIELDDISC(settlementdate; maturitydate; price; redemptionvalue; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the bond.
maturitydate: the maturity (redemption) date of the bond.
price: the price of the security, per 100 par value.
redemptionvalue: the redemption value of the bond, per 100 par value.
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
YIELDDISC calculates the annual (uncompounded) yield for a bond which pays no interest (a 'pure discount instrument' or 'discounted zero coupon bond'). 


YIELDMAT
Calculates the yield for a bond that pays interest on maturity.
Syntax
YIELDMAT(settlementdate; maturitydate; issuedate; rate; price; basis)
Syntax Description
settlementdate: the settlement (purchase) date of the bond.
maturitydate: the maturity (redemption) date of the bond.
issuedate: the original issue date of the bond.
rate: the (annual) interest rate of the bond (interest only paid at maturity).
price: the price of the bond, per 100 par value.
basis: is the calendar system to use. Defaults to 0 if omitted.
0 - US method (NASD), 12 months of 30 days each
1 - Actual number of days in months, actual number of days in year
2 - Actual number of days in month, year has 360 days
3 - Actual number of days in month, year has 365 days
4 - European method, 12 months of 30 days each
This function calculates the yield for a bond which pays interest just once, at maturity.


ZTEST
Returns the result of a z-test.
Syntax
ZTEST(data; μ ;σ)
Syntax Description
data is a range or array containing a random sample from a population (population assumed to have a normal distribution).
μ is the (known) mean of the population.
σ is the (known) standard deviation of the population. If omitted, it is estimated from the sample data by STDEV(data).
ZTEST returns the one-sided cumulative probability - the area under the standard normal curve to the right of the z value.


This help document contains modified content from OpenOffice.org's Manual of Calc Functions, available at http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_Functions_listed_alphabetically. This content is licensed under Public Documentation License (PDL), version 1.0, available at http://www.openoffice.org/licenses/PDL.html. Refer to our Legal Notices page for more information.