Function ที่ Sheet support

Returns the absolute value of a number.

ABS(number)

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.

ACCRINT(issue; first_interest; settlement; rate; par; frequency; basis)

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.

ACCRINTM(issue; settlement; rate; par; basis)

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.

ACOS(number)

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.

ACOSH(number)

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.

ACOT(number)

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.

ACOTH(number)

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.

ADDRESS(row; column; mode; ref_type; sheet)

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

AMORDEGRC(cost; purchase_date; first_period_end; salvage; period; rate; basis)

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

AMORLINC(cost; purchase_date; first_period_end; salvage; period; rate; basis)

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.

AND(argument1; argument2 ...argument30)

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

ARABIC(text)

Returns the Roman numeral text as a number, limited to the range 1-3999.

AREAS

Returns the number of areas in a given reference.

AREAS(reference)

reference may consist of multiple ranges.
AREAS returns the count of these ranges.

ASIN

Returns the inverse sine (the arcsine) of a number.

ASIN(number)

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.

ASINH(number)

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.

ATAN(number)

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.

ATAN2(x_coord; y_coord)

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.

ATANH(number)

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.

AVEDEV(number1; number2; ... number30)

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.

AVERAGE(number1; number2; ... number30)

number1 to number30 are up to 30 numbers or ranges containing numbers.

AVERAGEA

Returns the average of the arguments, including text (valued as 0).

AVERAGEA(value1; value2; ... value30)

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

AVERAGEIF

Conditionally returns the average the contents of cells in a range.

AVERAGEIF(test_range; condition; average_range)

This function identifies those cells in the range test_range that meet the condition, and and returns the average of the corresponding cells in the range average_range. If average_range is omitted, the cells present in test_range are considered.
condition may be:
A number, such as 34.5
An expression, such as 2/3 or SQRT(B5)
A text string
AVERAGEIF looks for cells in test_range that conforms to condition, unless condition is a text string that starts with a comparator:
>, <, >=, <=, =, <>
In this case AVERAGEIF compares those cells in test_range with the remainder of the text string (interpreted as a number if possible or text otherwise).

AVERAGEIFS

Conditionally returns the average of the contents of cells for the set of ranges.

AVERAGEIFS(average_range; test_range1;condition1;[test_range2;condition2];...)

This function identifies those cells in the test_ranges that meet the conditions, and returns the averages of the corresponding cells in the range average_range.
condition may be:
A number, such as 34.5
An expression, such as 2/3 or SQRT(B5)
A text string
AVERAGEIFS looks for cells in test_ranges that are equal to corresponding conditions, unless conditions are text string that starts with the comparator's:
>, <, >=, <=, =, <>
In this case AVERAGEIFS compares those cells in test_range's with the remainder of the text string (interpreted as a number if possible or text otherwise).

Calculates probabilities for a binomial distribution.

B(n; p; k; k2)

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.

BASE(number; radix; minlength)

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.

BESSELI(x; n)

Returns the modified Bessel function of the first kind, of order n, evaluated at x.

BESSELJ

Calculates the Bessel function of the first kind.

BESSELJ(x; n)

Returns the Bessel function of the first kind, of order n, evaluated at x.

BESSELK

Calculates the modified Bessel function of the second kind.

BESSELK(x; n)

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

BESSELY(x; n)

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.

BETADIST(x; α; β; a; b; cumulative)

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.

BETAINV(p; α; β; a; b)

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.

BIN2DEC(binarynumber)

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.

BIN2HEX(binarynumber; numdigits)

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.

BIN2OCT(binarynumber)

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.

BINOMDIST(k; n; p; mode)

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.

CEILING(number; mult; mode)

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.

CELL(info_type; cell_ref)

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.
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.
TYPE
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)
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.

CHAR(number)

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 χ²-distribution.

CHIDIST(x; k)

k is the (positive integer) degrees of freedom for the χ²-distribution.
x is a number >=0.

CHIINV

Calculates the inverse of the CHIDIST function.

CHIINV(p; k)

k is the (positive integer) degrees of freedom for the χ²-distribution.
CHIINV(p; k) returns the value x, such that CHIDIST(x; k) is p.

CHISQDIST

Calculates values for a χ²-distribution.

CHISQDIST(x; k; Cumulative)

x is the number, at which you will evaluate the χ²-distribution.
k sets the degrees of freedom for the χ²-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.

CHISQINV(p; k)

k is the degrees of freedom for the χ²-distribution.
Constraint: k must be a positive integer
p is the given probability
Constraint: 0 ≤ p < 1

CHITEST

Returns a measure of χ² 'goodness of fit'.

CHITEST(observed; expected)

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.

CHOOSE(index; value1; value2; ... value30)

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.

CLEAN(text)

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.

CODE(text)

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.

COLUMN(reference)

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.

COLUMNS(reference)

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.

COMBIN(n; k)

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.

COMBINA(n; k)

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.

COMPLEX(realpart; imaginarypart; suffix)

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.

CONCATENATE(text1; text2; ... text30)

Returns up to 30 text strings text1 - text30, joined together.
text1 - text30 may also be single cell references.

CONFIDENCE

Returns a confidence interval.

CONFIDENCE(α; sd; size)

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.

CONVERT(number; originalunits; newunits)

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 (10¹), h (10²), k (10³), M 10⁶), G (10⁹), T (10¹²), P (10¹⁵), E (10¹⁸), Z (10²¹), Y (10²⁴)

CORREL

Returns the Pearson correlation coefficient of two sets of data.

CORREL(x; y)

Where x and y are ranges or arrays containing the two sets of data.

COS

Returns the cosine of the given angle (in radians).

COS(angle)

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.

COSH(number)

Returns the hyperbolic cosine of number.

COT

Returns the cotangent of the given angle (in radians).

COT(angle)

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.

COTH(number)

Returns the hyperbolic cotangent of number.

COUNT

Counts the numbers in the list of arguments, ignoring text entries.

COUNT(value1; value2; ... value30)

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.

COUNTA(value1; value2; ... value30)

value1 to value30 are up to 30 values or ranges representing the values to be counted.

COUNTBLANK

Returns the number of empty cells.

COUNTBLANK(range)

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.

COUNTIF(test_range; condition)

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

COUNTIFS

Counts the number of times each cells in all the ranges that meet the specific conditions.

COUNTIFS(test_range1; condition1;[test_range2;condition2];...)

test_range1 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
test_range2,condition2,… Optional. Up to 127 range/criteria pairs are allowed.
COUNTIFS counts those cells in test_range's that are equal to the corresponding conditions, unless conditions are text string that starts with a comparator's:
>, <, >=, <=, =, <>
In this case COUNTIFS compares those cells in test_range’s 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.

COUPDAYBS(settlement; maturity; frequency; basis)

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.

COUPDAYS(settlement; maturity; frequency; basis)

COUPDAYSNC

Returns the number of days between the settlement date and the next coupon date.

COUPDAYSNC(settlement; maturity; frequency; basis)

COUPNCD

Returns the coupon date next after the settlement date.

COUPNCD(settlement; maturity; frequency; basis)

COUPNUM

Returns the number of coupons (interest payments) between the settlement date and maturity.

COUPNUM(settlement; maturity; frequency; basis)

COUPPCD

Returns the coupon (interest payment) date which precedes the settlement date.

COUPPCD(settlement; maturity; frequency; basis)

COVAR

Returns the covariance of two sets of data.

COVAR(x; y)

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.

CRITBINOM(n; p; a)

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.

CUMIPMT(rate; numperiods; presentvalue; start; end; type)

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.

CUMPRINC(rate; numperiods; presentvalue; start; end; type)

CURRENT

Returns the current (to date) result of evaluating the formula.

CURRENT()

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

CURRENCY

Converts the given value from one currency to another specified currency value.

CURRENCY(value; from; to)

Converts the given value from one currency to another specified currency value.
value: The value to be converted. Eg., 3500
from: The currency from which the value is to be converted. Must be given as a currency code. Eg., "USD"
to: The currency into which the value is to be converted. Must be given as a currency code. Eg., "INR"

Supported Currency Codes:

Currency Code	Currency Name	Country
AED	UAE Dirham	United Arab Emirates
ARS	Argentine Peso	Argentina
AUD	Australian Dollar	Australia
BGN	Bulgarian Lev	Bulgaria
BRL	Brazilian Real	Brazil
BSD	Bahamian Dollar	Bahamas
CAD	Canadian Dollar	Canada
CHF	Swiss Franc	Switzerland
CLP	Chilean Peso	Chile
CNY	Chinese Renminbi	China
COP	Colombian Peso	Colombia
CZK	Czech Koruna	Czech Republic
DKK	Danish Krone	Denmark
DOP	Dominican Peso	Dominican Republic
EGP	Egyptian Pound	Egypt
EUR	Euro	Germany
EUR	Euro	Austria
EUR	Euro	Belgium
EUR	Euro	Cyprus
EUR	Euro	Estonia
EUR	Euro	Finland
EUR	Euro	France
EUR	Euro	Greece
EUR	Euro	Ireland
EUR	Euro	Italy
EUR	Euro	Latvia
EUR	Euro	Lithuania
EUR	Euro	Luxembourg
EUR	Euro	Malta
EUR	Euro	Netherlands
EUR	Euro	Portugal
EUR	Euro	Slovakia
EUR	Euro	Slovenia
EUR	Euro	Spain
FJD	Fiji Dollar	Fiji
GBP	Pound Sterling	United Kingdom
GTQ	Guatemalan Quetzal	Guatemala
HKD	Hong Kong Dollar	Hong Kong
HRK	Croatian Kuna	Croatian
HUF	Hungarian Forint	Hungary
IDR	Indonesian Rupiah	Indonesia
ILS	Israeli Shekel	Israel
INR	Indian Rupee	India
ISK	Icelandic Krona	Iceland
JPY	Japanese Yen	Japan
KRW	South Korean Won	Korea
KZT	Kazakhstani Tenge	Kazakhstan
MXN	Mexican Peso	Mexico
MYR	Malaysian Ringgit	Malaysia
NOK	Norwegian Krone	Norway
NZD	New Zealand Dollar	New Zealand
PAB	Panamanian Balboa	Panama
PEN	Peruvian Nuevo Sol	Peru
PHP	Philippine Peso	Philippines
PKR	Pakistani Rupee	Pakistan
PLN	Polish Zloty	Poland
PYG	Paraguayan Guarani	Paraguay
RON	Romanian Leu	Romania
RUB	Russian Ruble	Russian Federation
SAR	Saudi Riyal	Saudi Arabia
SEK	Swedish Krona	Sweden
SGD	Singapore Dollar	Singapore
THB	Thai Baht	Thailand
TRY	Turkish Lira	Turkey
TWD	New Taiwan Dollar	Taiwan
UAH	Ukrainian Hryvnia	Ukraine
USD	US Dollar	United States
UYU	Uruguayan Peso	Uruguay
VND	Vietnamese Dong	Vietnam
ZAR	South African Rand	South Africa

Note: The data provided is for informational purpose only and may be delayed upto 30 minutes. Data provided for free by Exchange Rate API. Exchange Rate API Terms.

DATE

returns the date, given the year, month and day of the month.

DATE(year; month; day)

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.

DATEDIF

Returns the number of days, months, or years between two given dates.

DATEDIF(start_date; end_date; type)

start_date "Starting date for the period.
end_date Final or end date for the specified period.
type Indicates how the result must be.

Supported Type
Y - Number of years in the given period
M - Number of months in the given period
D - Number of days in the given period
MD - Difference between days in start_date and end_date, ignoring the months and years.
YM - Difference between months in start_date and end_date, ignoring the days and years.
YD - Difference between the days in start_date and end_date, ignoring the years alone.

DATEVALUE

returns the date-time serial number, from a date given as text.

DATEVALUE(datetext)

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.

DAVERAGE(database_table; field; criteria_table)

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.

DAY(date)

Returns the day of date as a number ( 1- 31).

DAYS

Returns the number of days between two dates

DAYS(enddate; startdate)

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.

DAYS360(enddate; startdate; method)

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.

DAYSINMONTH(date)

Returns the number of days in the month in which date lies.

DAYSINYEAR

Returns the number of days in a year.

DAYSINYEAR(date)

Returns the number of days in the year in which date lies.

Returns the depreciation of an asset for a given year using the fixed rate declining-balance method.

DB(originalcost; salvagevalue; lifetime; year; months1styear)

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.

DCOUNT(database_table; field; criteria_table)

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.

DCOUNTA(database_table; field; criteria_table)

DDB

Returns the depreciation of an asset for a given year using the double (or other factor) declining-balance method.

DDB(originalcost; salvagevalue; lifetime; year; factor)

DEC2BIN

Converts a decimal number to binary.

DEC2BIN(number; numdigits)

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.

DEC2HEX(number; numdigits)

Returns a hexadecimal number as text, given the decimal number, which must be between -2³⁹ and 2³⁹-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.

DEC2OCT(number; numdigits)

Returns an octal number as text, given the decimal number, which must be between -2²⁹ and 2²⁹-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.

DECIMAL(text; radix)

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.

DEGREES(radians)

radians is the angle in radians to be converted to degrees.

DELTA

Returns 1 if two numbers are equal, and 0 otherwise.

DELTA(number1; number2)

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.

DEVSQ(number1; number2; ... number30)

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.

DGET(database_table; field; criteria_table)

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.

DISC(settlementdate; maturitydate; price; redemptionvalue; basis)

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.

DMAX(database_table; field; criteria_table)

DMIN

Returns the minimum (lowest) value in a column of a Calc 'database' table, in rows which meet specified criteria.

DMIN(database_table; field; criteria_table)

DOLLAR

Returns text representing a number in your local currency format.

DOLLAR(number; decimals)

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.

DOLLARDE(fractionalrep; denominator)

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.

DOLLARFR(decimal; denominator)

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.

DPRODUCT(database_table; field; criteria_table)

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.

DSTDEV(database_table; field; criteria_table)

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.

DSTDEVP(database_table; field; criteria_table)

DSUM

Sums the cells in a column of a Calc 'database' table, in rows which meet specified criteria.

DSUM(database_table; field; criteria_table)

PDURATION

Returns the number of periods needed for an investment to reach a certain value.

PDURATION(rate; present_value; future_value)

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

Returns the Macaulay duration of a security.

DURATION(settlementdate; maturitydate; rate; yield; frequency; basis)

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.

DVAR(database_table; field; criteria_table)

DVARP

Returns the population variance of values in a column of a Calc 'database' table, in rows which meet specified criteria.

DVARP(database_table; field; criteria_table)

EASTERSUNDAY

Returns the date of Easter Sunday in a given year.

EASTERSUNDAY(year)

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.

EDATE(startdate; months)

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.

EFFECTIVE(nom_rate; num)

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.

EOMONTH(startdate; addmonths)

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

ERF(number1; number2)

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

ERFC(number)

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.

ERRORTYPE(reference)

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.

EVEN(number)

Returns number rounded to the next even integer up, away from zero.

EXACT

returns TRUE if two text strings are identical

EXACT(text1; text2)

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.

EXP(number)

Returns e^number.

EXPONDIST

Calculates values for an exponential distribution.

EXPONDIST(x; λ; mode)

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.

FACT(number)

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.

FACTDOUBLE(number)

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.

FALSE()

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

FDIST

Calculates values for an F-distribution.

FDIST(x; r1; r2)

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.

FIND(findtext; texttosearch; startposition)

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.

FILTER

Tests a given range and returns a filtered version that meets the specified conditions.

FILTER(range; condition1; condition2...)

range: The source range to be filtered.
condition 1 to condition 30 - The conditions used to filter out the given data range.

FINV

Calculates the inverse of the FDIST function.

FINV(p; r1; r2)

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.

FISHER(r)

Returns the value of the Fisher transformation at r, (-1 < r < 1).

FISHERINV

Calculates the inverse of the FISHER transformation.

FISHERINV(z)

Returns the value r, such that FISHER(r) is z.

FIXED

Returns a number as text with a specified format.

FIXED(number; decimals; omitseparators)

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.

FLOOR(number; mult; mode)

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.

FORECAST(new_xvalue; yvalues; xvalues)

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.

FORMULA(cell)

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.

FREQUENCY(data; bins)

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.

FTEST(data1; data2)

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.

Returns the future value of an initial sum with a subsequent stream of payments.

FV(rate; numperiods; payment; presentvalue; type)

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.

FVSCHEDULE(principal; interestrates)

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.

GAMMA(x)

x is a number.
Constraint: If x is an integer, then x must be positive.

GAMMADIST

Calculates values for a gamma distribution.

GAMMADIST(x; α; β; mode)

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.

GAMMAINV(p; α; β)

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.

GAMMALN(x)

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.

GAUSS(x)

Where x is a number, returns NORMSDIST(x) - 0.5.

GCD

Returns the greatest common divisor of two or more integers.

GCD(integer1; integer2; ... integer30)

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.

GEOMEAN(number1; number2; ... number30)

number1 to number30 are up to 30 numbers or ranges containing numbers. Numbers must not be zero.
The geometric mean of a₁, a₂, ... a_n is defined as (a₁ . a₂. ... . a_n)^1/n.

GESTEP

Returns 1 if a number is greater than or equal to a step number, or 0 otherwise.

GESTEP(number; stepnumber)

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.

GROWTH(yvalues; xvalues; new_xvalues; allow_factor)

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=bm^x 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.

HARMEAN(number1; number2; ... number30)

number1 to number30 are up to 30 numbers or ranges containing numbers. Numbers must not be zero.

HEX2BIN

Converts a hexadecimal number to binary.

HEX2BIN(hexadecimalnumber; numdigits)

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.

HEX2DEC(hexadecimalnumber)

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 2³⁹-1 decimal) and negative numbers FFFFFFFFFF to 8000000000 (representing -1 to -2³⁹ decimal).

HEX2OCT

Converts a hexadecimal number to octal.

HEX2OCT(hexadecimalnumber; numdigits)

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 -2²⁹ to 2²⁹-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.

HLOOKUP(lookupvalue; datatable; rowindex; mode)

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 rowindex^th 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.

HOUR(time)

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.

HYPERLINK(linkaddress; celltext)

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.

HYPGEOMDIST(x; n; M; N)

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.

Returns one of two values, depending on a test condition.

IF(test; value1; value2)

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.

IFERROR

Returns one of the two values, depending on a first value.

IFERROR(value; value_if_error)

value is the value that is checked whether the value or an expression that returns an error.
value is the value that is returned by the function if value yields not an ERROR.
value_if_error is the value that is returned by the function if value yields an ERROR.

IFS

Tests a given range and returns a filtered version that meets the specified conditions.

IFS(test1; value1; test2...; value2...)

test1: The first logical value or expression to evaluate.
value1: The value that is returned by the function if test1 yi
elds true.
test2... The second logical value or expression to evaluate.
value2... The value that is returned by the function if test2 yields true.

IMABS

Returns the absolute value of a complex number.

IMABS(complexnumber)

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 √(a² + b²).

IMAGINARY

Returns the imaginary part of a complex number.

IMAGINARY(complexnumber)

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.

IMARGUMENT(complexnumber)

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.

IMCONJUGATE(complexnumber)

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.

IMCOS(complexnumber)

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.

IMDIV(complexnum1; complexnum2)

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.

IMEXP(complexnumber)

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 e^a+bi = e^a(cosb+isinb).

IMLN

Returns the natural logarithm of a complex number.

IMLN(complexnumber)

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.

IMLOG10(complexnumber)

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.

IMLOG2(complexnumber)

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.

IMPOWER(complexnumber; number)

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)ⁿ = rⁿcosφ + irⁿsinφ.

IMPRODUCT

Returns the product of complex numbers.

IMPRODUCT(complexnum1; complexnum2; ... complexnum29)

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.

IMREAL(complexnumber)

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.

IMSIN(complexnumber)

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.

IMSQRT(complexnumber)

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 a₀+b₀i, it returns a₁+b₁i, such that ( a₁+b₁i) * ( a₁+b₁i) = a₀+b₀i.

IMSUB

Returns the difference between 2 complex numbers.

IMSUB(complexnum1; complexnum2)

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.

IMSUM(complexnum1; complexnum2; ... complexnum29)

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.

INDEX(datatable; row; column; areanumber)

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.

INDIRECT(textref; ref_type)

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.

INT(number)

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.

INTERCEPT(yvalues; xvalues)

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.

INTRATE(settlementdate; maturitydate; purchasevalue; maturityvalue; basis)

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.

IPMT(rate; period; numperiods; presentvalue; futurevalue; type)

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.

IRR(payments; guess)

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

ISBLANK(cell)

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.

ISERR(value)

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.

ISERROR(value)

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.

ISEVEN(value)

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.

ISFORMULA(cell)

Returns TRUE if cell contains a formula and FALSE otherwise.

ISLEAPYEAR

Tests if a date is in a leap year.

ISLEAPYEAR(date)

Returns 1 if date lies in a leap year, and 0 otherwise.

ISLOGICAL

Tests if a cell contains a logical value, TRUE or FALSE.

ISLOGICAL(value)

Returns TRUE if value is either TRUE or FALSE, and FALSE otherwise.

ISNA

Tests for the #N/A (Not Available) error value.

ISNA(value)

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.

ISNONTEXT(value)

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.

ISNUMBER(value)

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.

ISODD(value)

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.

ISPMT(rate; period; numperiods; principal)

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.

ISREF(value)

Returns TRUE if value is a reference to a cell or range of cells and FALSE otherwise.

ISTEXT

Tests if a cell contains text.

ISTEXT(value)

Returns TRUE if value is or refers to text, and FALSE otherwise.

KURT

Returns a measure of how peaked or flat a distribution is.

KURT(number1; number2; ... number30)

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 n^th largest value in a list of numbers.

LARGE(numberlist; n)

Returns the n^th largest number within the (unordered) range or array of numbers numberlist.

LCM

Returns the least common multiple of one or more integers.

LCM(integer1; integer2; ... integer30)

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.

LEFT(text; number)

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.

LEN(text)

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.

LINEST(yvalues; xvalues; allow_const; stats)

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 + b₁x₁ + b₂x₂ ... + b_nx_n.
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.

Returns the natural logarithm of a number.

LN(number)

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.

LOG(number; base)

Returns the logarithm to base base of number.

LOG10

Returns the base-10 logarithm of a number.

LOG10(number)

Returns the logarithm to base 10 of number.

LOGEST

Returns a table of statistics for an exponential curve that best fits a data set.

LOGEST(yvalues; xvalues; allow_const; stats)

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 . b^x that best fits the data. With more than one set of variables the curve is of the form y = a . b₁^x₁ . b₂^x₂ ... . b_n^x_n.
In order to fit the curve, LOGEST uses linear regression (the "least squares" method) based on the equation ln(y) = ln(a) + x₁ln(b₁) + x₂ln(b₂) + ... x_nln(b_n).
If allow_const is FALSE the constant a is forced to be one; y = b^x. 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.

LOGINV(p; μ ; σ )

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.

LOGNORMDIST(x; μ; σ )

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.

LOOKUP(lookupvalue; searchtable; resulttable)

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.

LOWER(text)

Returns text with all characters converted to lower case.

MATCH

Returns the position of a search item in a single row or column table.

MATCH(searchitem; searchregion; matchtype)

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.

MAX(number1; number2; ... number30)

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.

MAXA(value1; value2; ... value30)

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

MAXIFS

Returns the maximum of a list of arguments, including text and logical entries.

MAXIFS(range; criteria_range1; criterion1;[criteria_range2; criterion2];...)

range: The source range to find the maximum value.
criteria_range1: The criteria range to apply the criterions.
criterion1: The conditions used to filter out the given criteria range.
criteria_range2 to criteria_range30 (optional) The criteria range to apply the criterions.
criterion2: to criterion30 (optional)The conditions used to filter out the given criteria range.

MDETERM

Returns the determinant of a matrix.

MDETERM(array)

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.

MDURATION(settlementdate; maturitydate; rate; yield; frequency; basis)

MEDIAN

Returns the median of a set of numbers.

MEDIAN(number1; number2; ... number30)

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.

MID(text; start; number)

Returns number characters from the text text, starting at position start.

MIN

Returns the minimum of a list of arguments, ignoring text entries.

MIN(number1; number2; ... number30)

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.

MINA(value1; value2; ... value30)

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

MINIFS

Returns the minimum value from a given list of arguments, based on the specified conditions.

MINIFS(range; criteria_range1; criterion1; criteria_range2...; criterion2...;)

range: The source range to find the minimum value.
criteria_range1: The criteria range to apply the criterions.
criterion1: The conditions used to filter out the given criteria range.
criteria_range2...: (optional) The criteria range to apply the criterions.
criterion2... : (optional) The conditions used to filter out the given criteria range.

MINUTE

Returns the minutes of a given time.

MINUTE(time)

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.

MINVERSE(array)

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.

MIRR(payments; financerate; reinvestrate)

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.

MMULT(array1; array2)

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.

MOD(number; divisor)

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.

MODE(number1; number2; ... number30)

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.

MONTH(date)

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.

MONTHS(startdate; enddate; mode)

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.

MROUND(number; mult)

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.

MULTINOMIAL (number1; number2; ... number30)

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.

MUNIT(size)

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.

Returns the numeric value.

N(value)

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.

Returns the #N/A (Not Available) error value.

NA()

This function has no arguments, and returns the error value #N/A.

NEGBINOMDIST

Calculates probabilities for a negative binomial distribution.

NEGBINOMDIST(x; r; p)

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.

NETWORKDAYS(startdate; enddate; hols_range)

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.

NOMINAL(eff_rate; num)

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.

NORMDIST(x; μ;σ; mode)

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.

NORMINV(p; α; λ)

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.

NORMSDIST(x)

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.

NORMSINV(p)

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.

NOT(logical_value)

Where logical_value is the logical value to be reversed.

NOW

Returns the current date and time

NOW()

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.

NPER(rate; payment; presentvalue; futurevalue; type)

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.

NPV(discountrate; payment1; payment2; ... payment30)

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.

OCT2BIN(octalnumber; numdigits)

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.

OCT2DEC(octalnumber)

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 2²⁹-1 decimal) and negative numbers 7777777777 to 4000000000 (representing -1 to -2²⁹ decimal).

OCT2HEX

Converts a octal number to hexadecimal.

OCT2HEX(octalnumber; numdigits)

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 2²⁹-1 decimal) and negative numbers 7777777777 to 4000000000 (representing -1 to -2²⁹ 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.

ODD(number)

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.

ODDFPRICE(settlementdate; maturitydate; issuedate; firstinterestdate; rate; yield; redemptionvalue; frequency; basis)

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.

ODDFYIELD(settlementdate; maturitydate; issuedate; firstinterestdate; rate; price; redemptionvalue; frequency; basis)

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.

ODDLPRICE(settlementdate; maturitydate; lastinterestdate; rate; yield; redemptionvalue; frequency; basis)

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.

ODDLYIELD(settlementdate; maturitydate; lastinterestdate; rate; price; redemptionvalue; frequency; basis)

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.

OFFSET(reference; row_offset; col_offset; new_height; new_width)

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.

Returns TRUE if any of the arguments are considered TRUE, and FALSE otherwise.

OR(argument1; argument2 ...argument30)

PEARSON

Returns the Pearson correlation coefficient of two sets of data.

PEARSON(x; y)

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.

PERCENTILE(numberlist; fraction)

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.

PERCENTRANK(numberlist; number)

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.

PERMUT(n; k)

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.

PERMUTATIONA(n; k)

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 n^k.

PHI

Calculates values for a standard normal distribution.

PHI(x)

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

Returns 3.14159265358979, the value of the mathematical constant PI to 14 decimal places.

PI()

PMT

Returns the payment per period for a fixed rate loan.

PMT(rate; numperiods; principal; finalbalance; type)

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.

POISSON(x; λ; mode)

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.

POWER(number; power)

Returns number^power, 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.

PPMT(rate; period; numperiods; presentvalue; futurevalue; type)

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.

PRICE(settlementdate; maturitydate; rate; yield; redemptionvalue; frequency; basis)

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.

PRICEDISC(settlementdate; maturitydate; discountrate; redemptionvalue; basis)

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.

PRICEMAT(settlementdate; maturitydate; issuedate; rate; yield; basis)

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.

PROB(values; probabilities; start; end)

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.

PRODUCT(number1; number2; ... number30)

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.

PROPER(text)

Returns text with the first character of each word capitalised, and other characters in lower case.

Returns the present value of a stream of future payments with a final lump sum.

PV(rate; numperiods; payment; futurevalue; type)

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.

QUARTILE(numberlist; q)

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.

QUOTIENT(numerator; denominator)

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.

RADIANS(degrees)

degrees is the angle in degrees to be converted to radians.

RAND

Returns a random number between 0 and 1.

RAND()

This function produces a new random number each time Calc recalculates, greater than or equal to 0, and less than 1.

RANDARRAY

Returns an array of random values in the given cell range. The minimum, maximum and type of values to be returned can also be specified.

RANDARRAY([row]; [column]; [min]; [max]; [type])

row : Number of rows to be filled with the random array. Defaults to 1 if omitted.
column : Number of columns to be filled with the sequential array. Defaults to 1 if omitted.
min : The lowest value you want in the array.
max : The highest value you want in the array.
type : Logical expression that specifies the type of value to return.
If TRUE, the function returns whole numbers.
If FALSE, the function returns decimal numbers.

RANDBETWEEN

Returns an integer random number in a specified range.

RANDBETWEEN(bottom; top)

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.

RANK(number; numberlist; order)

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.

RANK.AVG

Returns the rank of a given number from the specified list of numbers. If the number occurs more than once in the list, the average rank is returned.

RANK.AVG(number; number_list; [order])

number :The number whose rank is to be found.
number_list :The data set to test.
order :Determines the order of ranking. Defaults to 0 if omitted.
If 0, the given list is considered to be sorted in the descending order.
For any non-zero number, the list is considered to be sorted in the ascending order.

RANK.EQ

Returns the rank of a given number from the specified list of numbers. If the number occurs more than once in the list, the top rank is returned.

RANK.EQ(number; number_list; [order])

RATE

Calculates the interest rate for an annuity.

RATE(numperiods; payment; presentvalue; futurevalue; type; guess)

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

RECEIVED(settlementdate; maturitydate; purchasevalue; discountrate; basis)

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.

REPLACE(originaltext; startposition; length; newtext)

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.

REPT(text; number)

Returns number copies of text, joined together.

RIGHT

Returns text from the right side of a text string.

RIGHT(text; number)

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.

ROMAN(number; mode)

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.

ROUND(number; places)

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.

ROUNDDOWN(number; places)

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.

ROUNDUP(number; places)

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.

ROW(reference)

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.

ROWS(reference)

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.

RRI(numperiods; presentvalue; futurevalue)

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.

RSQ(x; y)

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").

Returns the position of a string of text within another string.

SEARCH(findtext; texttosearch; startposition)

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.

SECOND(time)

Returns the seconds of time as a number, 0 - 59.
time may be text or a date-time serial number.

SEQUENCE

Reurns a list of sequential numbers as an array in the provided cell range.

SEQUENCE(row; [column]; [first_value]; [step])

row : Number of rows to be filled with the sequential array.
column : Number of columns to be filled with the sequential array.
first_value : The starting or first value in the sequence. Defaults to 1 if omitted.
step : Value by which each value in the sequence increments.

SERIESSUM

Sums the first terms of a power series.

SERIESSUM(x; n; m; ar)

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.

SHEET(reference)

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.

SIGN(number)

number is the number whose sign is to be determined.

SIN

Returns the sine of the given angle (in radians).

SIN(angle)

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.

SINH(number)

Returns the hyperbolic sine of number.

SKEW

Returns a measure of how skewed a distribution is.

SKEW(number1; number2; ... number30)

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.

SLN(originalcost; salvagevalue; lifetime)

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.

SLOPE(yvalues; xvalues)

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 n^th smallest value in a list of numbers.

SMALL(numberlist; n)

Returns the n^th smallest number within the (unordered) range or array of numbers numberlist.

SORT

Sorts a given array or range of values based on the specified criteria. It is a dynamic array function, where the result array may increase or decrease in size based on the source range.

SORT(range; [sort_by]; [order]; [direction])

range :The range containing the array of values to sort.
sort_by :Number indicating the row or column to sort the range by.
order :Indicates the order in which the range should be sorted.
If 1, the range is sorted in ascending order
If -1, the range is sorted in descending order
direction :Logical expression that specifies direction of sort. Defaults to FALSE if omitted.
If TRUE, sorts by column
If FALSE, sorts by row

SORTBY

Sorts a given array or range of values based on a corresponding array or range, and the specified criteria. It is a dynamic array function, where the result array may increase or decrease in size based on the source range.

SORTBY(range; by_range; [order]; [by_range2]; [order2]; ...)

range :The range containing the array of values to sort.
by_range :The range containing the array of values to sort the range by.
order :Indicates the order in which the range should be sorted.
If 1, the range is sorted in ascending order
If -1, the range is sorted in descending order

SQRT

Returns the positive square root of a number.

SQRT(number)

Returns the positive square root of number.
number must be positive.

SQRTPI

Returns the square root of (PI times a number).

SQRTPI(number)

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.

STANDARDIZE(n; μ; σ)

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.

STDEV(number1; number2; ... number30)

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.

STDEVA(number1; number2; ... number30)

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.

STDEVP(number1; number2; ... number30)

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

STDEVPA(number1; number2; ... number30)

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.

STEYX(yvalues; xvalues)

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

Currently available for US Exchanges only

Returns the stock details for the symbol provided.

STOCK(Symbol; Function)

Symbol: The stock symbol of the company.
Function(Optional) : The type of data. Defaults to the price value when omitted.
Example: =STOCK("GOOG","PRICE"), =STOCK("MSFT"), =STOCK("AAPL","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: The data provided is for informational purpose only and may be delayed upto 30 minutes. Data provided for free by IEX. IEX Terms.

SUBSTITUTE

Substitutes new text for old text in a text string.

SUBSTITUTE(originaltext; oldtext; newtext; which)

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.

SUBTOTAL(function; range)

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:

function	Function
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV
8	STDEVP
9	SUM
10	VAR
11	VARP

If range contains other SUBTOTAL functions they are ignored to avoid double counting.

SUM

Sums the contents of cells.

SUM(number1; number2; ... number30)

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.

SUMIF(test_range; condition; sum_range)

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

SUMIFS

Conditionally sums the contents of cells for the set of ranges.

SUMIFS(sum_range; test_range1;condition1;[test_range2;condition2];...)

This function identifies those cells in the test_range's that meet the conditions, and sums the corresponding cells in the range sum_range.
condition may be:
A number, such as 34.5
An expression, such as 2/3 or SQRT(B5)
A text string
SUMIFS looks for cells in test_ranges that are equal to corresponding conditions, unless those conditions is a text string that starts with a comparator's:
>, <, >=, <=, =, <>
In this case SUMIFS compares those cells in test_ranges 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.

SUMPRODUCT(array1; array2; ... array30)

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.

SUMSQ(number1; number2; .... number30)

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.

SUMX2MY2(x; y)

x and y are arrays or ranges of the same size.

SUMX2PY2

Returns the sum of the squares of all elements of two matrices.

SUMX2PY2(x; y)

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.

SUMXMY2(x; y)

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.

SYD(originalcost; salvagevalue; lifetime; year)

Returns the given text, or an empty text string if the target is not text.

T(value)

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

TAN(angle)

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.

TANH(number)

Returns the hyperbolic tangent of number.

TBILLEQ

Returns the bond-equivalent-yield (BEY) for a US Treasury bill.

TBILLEQ(settlementdate; maturitydate; discount)

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.

TBILLPRICE(settlementdate; maturitydate; discountrate)

TBILLYIELD

Returns the yield for a US Treasury bill.

TBILLYIELD(settlementdate; maturitydate; issueprice)

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.

TDIST(x; r; mode)

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.

TEXT(number; format)

Returns number converted to text, according to the format code specified by format.

TEXTJOIN

Combines the text from multiple given ranges or strings with a specified delimiter separating them.

TEXTJOIN(delimiter; ignore_blank; text1; text2; text3...)

delimiter: A text string, either an empty one or that with one or two characters.
ignore_blank: The function excludes blank empty cells, if TRUE.
text1 to text30: The text strings or ranges containing the strings to combine.

TIME

returns the time, given hours, minutes and seconds.

TIME(hours; minutes; seconds)

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.

TIMEVALUE(timetext)

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.

TINV(p; r)

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

TODAY()

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

TOP

Returns top part/whole array from the top of data_range after sorting it in descending order.

TOP(data_range; sort_index; result_index; no_of_resultsets)

data_range is the input data, which can either be a range or an array.
sort_index is the index of row/column to be be sorted. It can be a range or array, supporting sort of multiple rows/columns simultaneously.
result_index is the index of result row/column and is an optional parameter which defaults to sort_index. It can be a range or array, displaying multiple rows/columns.
no_of_resultsets is the number of result row/column and is an optional parameter which defaults to 1 if omitted.

TRANSPOSE

Returns an array with its rows and columns transposed.

TRANSPOSE(array)

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.

TREND(yvalues; xvalues; new_xvalues; type)

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.

TRIM(text)

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.

TRIMMEAN(numberlist; fraction)

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.

TRUE()

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

TRUNC

Truncates a number by removing decimal places.

TRUNC(number; places)

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.

TTEST(data1; data2; mode; type)

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

TYPE(value)

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.

UPPER(text)

Returns text with all characters converted to upper case.

UNIQUE

Returns the unique array of values from a given range or array of values.

UNIQUE(range; col; occurs_once)

Returns the unique array of values from a given range or array of values.
range: The array of values to evaluate.
col: (optional) Logical expression or value to compare the values. TRUE compares the values column-wise, while FAlSE or by row is omitted.
occurs_once: (optional) Logical expression or value to return the values based on occurrence. TRUE returns those values that occur only once in the given list, while FALSE returns all the unique values in the list and hence omitted.

VALUE

Returns a number, given a text representation.

VALUE(text)

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.

VAR(number1; number2; ... number30)

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

VARA(number1; number2; ... number30)

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.

VARP(number1; number2; ... number30)

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

VARPA(number1; number2; ... number30)

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.

VDB(originalcost; salvagevalue; lifetime; periodstart; periodend; factor; nostraightline)

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.

VLOOKUP(lookupvalue; datatable; columnindex; mode)

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 columnindex^th 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.

WEEKDAY(date; type)

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.

WEEKNUM(date; mode)

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.

WEEKNUM_ADD(date; mode)

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.

WEEKS(startdate; enddate; mode)

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.

WEEKSINYEAR(date)

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.

WEIBULL(x; k; λ; mode)

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.

WORKDAY(startdate; days; hols_range)

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.

XIRR(payments; dates; guess)

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.

XNPV(rate; payments; dates)

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.

XOR

With two logical statements, XOR returns TRUE if either statement is TRUE, but returns FALSE if both statements are TRUE. If neither is TRUE, XOR also returns FALSE.

XOR(logical1; logical2...; logical30)

logical1: logical value, or expression which returns logical value
logical2 to logical30 logical value, or expression which returns logical value

YEAR

Returns the year of a given date.

YEAR(date)

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.

YEARFRAC(startdate; enddate; basis)

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.

YEARS(startdate; enddate; mode)

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.

YIELD(settlementdate; maturitydate; rate; price; redemptionvalue; frequency; basis)

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.

YIELDDISC(settlementdate; maturitydate; price; redemptionvalue; basis)

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.

YIELDMAT(settlementdate; maturitydate; issuedate; rate; price; basis)

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.

ZTEST(data; μ ;σ)

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.

XLOOKUP

Tests the search_table for the given lookup_value and returns a corresponding value from the result_table. XLOOKUP is an advanced version of the VLOOKUP and HLOOKUP functions that searches both vertically and horizontally.

XLOOKUP(lookup_value; search_table; result_table; [if_not_found]; [match_mode]; [search_mode])

lookup_value : The value to search for in the search_table.
search_table : Range with values to evaluate.
result_table : Range that you want the corresponding value from as result.
if_not_found : Text to be displayed when no valid match is found. Defaults to #N/A error if omitted.
match_mode : Determines the type of match to perform. Defaults to 0, if omitted. 0 - The first exact match is considered. -1 - The first exact match is considered. If none found, it returns the next smallest value. 1 - The first exact match is considered. If none found, it returns the next largest value. 2 - A regex match where .*, .?, and / have special meaning.
search_mode. : Determines how lookup_value is searched in search_table. . Defaults to 1, if omitted. 1 - Search from top to bottom in the given range. -1 - Search from bottom to top in the given range. 2 - Perform a binary search with the search_table sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search with the search_table sorted in descending order. If not sorted, invalid results will be returned.

Note:
.* - Can be used to replace any number of characters. Eg., Zyl.* finds Zylker
.? - Can be used to replace a single character. Eg., Karthi.? finds Karthik and Karthic
/ - Can be used to include *, ? and / in the search term. Eg., Zylker/* finds Zylker* and Karthi.?/? finds Karthik? and Karthic?

XMATCH

Returns the relative position of the given value in a given range. XMATCH is an advanced version of the MATCH function.

XMATCH(search_item; search_region; [match_mode]; [search_mode])

search_item : The value to search for in the search_region.
search_region: Range with values to evaluate.
match_mode : Determines the type of match to perform. Defaults to 0, if omitted. 0 - The first exact match is considered. -1 - The first exact match is considered. If none found, it returns the next smallest value. 1 - The first exact match is considered. If none found, it returns the next largest value. 2 - A regex match where .*, .?, and / have special meaning.
search_mode. : Determines how lookup_value is searched in search_table. . Defaults to 1, if omitted. 1 - Search from top to bottom in the given range. -1 - Search from bottom to top in the given range. 2 - Perform a binary search with the search_table sorted in ascending order. If not sorted, invalid results will be returned. -2 - Perform a binary search with the search_table sorted in descending order. If not sorted, invalid results will be returned.

Note:
.* - Can be used to replace any number of characters. Eg., Zyl.* finds Zylker
.? - Can be used to replace a single character. Eg., Karthi.? finds Karthik and Karthic
/ - Can be used to include *, ? and / in the search term. Eg., Zylker/* finds Zylker* and Karthi.?/? finds Karthik? and Karthic?

IMPORTRANGE

Imports values from a given range of cells from another specified spreadsheet (workbook).

IMPORTRANGE(spreadsheet_url; range)

spreadsheet_url: The link of the source spreadsheet from where the range is to be imported. Eg., "https://sheet.zoho.com/sheet/open/c2zep9f2d2676610d426fb3ab9652164d0f87".
range_string: The reference of the range to be imported. Eg., "Sheet1.A2:F10".
Note:
- The source spreadsheet must be linked in order to pull data using IMPORTRANGE function.
- The user must have access to the source spreadsheet to link it.
- The values get auto-updated when there is a change in the source range.

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.

Related Articles
วิธีสร้าง Sheet
1. Log In เข้า ระบบ ZOHO และไปที่ https://sheet.zoho.com 2. ไปที่ New Spreadsheet สามารถเริ่มการทำงานได้
วิธีลบ sheet
1. เลือก sheet ที่ ต้องการ ไปที่ more เลือก Move to Trash 2. เมื่อลบสเปรดชีตมันจะถูกย้ายไปที่ถังขยะ คลิกที่ Trash ไปที่ More 2.1 การเลือก Delete ไฟล์จะถูกลบอย่างถาวร และไม่สามารถกู้คืนได้อีก 2.2 Restore คุณสามารถเลือกที่จะกู้ไฟล์ได้
ภาษาที่รองรับ Writer Office Suite
ภาษาที่รองรับ Writer Office Suite มีดังต่อไปนี้ อังกฤษ เกาหลี (เกาหลี) โปรตุเกส (โปรตุเกส) ขัด เดนมาร์ก (เดนมาร์ก) ลิทัวเนีย (ลิทัวเนีย) รัสเซีย (รัสเซีย) คาตาลัน (Català) เยอรมัน (Deutsche) โรมาเนีย (română) สวีเดน (svenska) Thai (ไทย) สเปน ...
Zoho Writer and live link into a Sheet document table
1. ไปที่ ZohoSheet และ copy ตารางที่ต้องการ 2. ไปที่ writer กด วาง และเลือก Link data with the source 3. กดที่ตารางอีกครั้ง และไปที่ แถมเมนูด้านข้าง 4. เลือก Update เพียงเท่านี้ เราก็สามารถ update ข้อมูลได้
ภาษาที่รองรับ
· · Zoho Sheet is available in 13 languages - English - Chinese - Dutch - French - German - Korean - Italian - Japanese - Polish - Portuguese - Spanish - Swedish - Turkish

Function ที่ Sheet support

Function ที่ Sheet support

Related Articles

วิธีสร้าง Sheet

วิธีลบ sheet

ภาษาที่รองรับ Writer Office Suite

Zoho Writer and live link into a Sheet document table

ภาษาที่รองรับ