IF(condition, true_value, false_value)

Tests the condition and returns the true_value if condition is TRUE or false_value if condition is FALSE.

See also: AND, FALSE, NOT, OR, TRUE

INDEX(reference [,row] [,column] [,range number])

Returns the contents of a cell in a specified reference range. If there is more than one range, each reference is separated by commas and the whole reference enclosed in parentheses (e.g. (A1:C6, B7:E14)).In this case, range number refers to the range from which data is returned.

See also: CHOOSE, HLOOKUP, LOOKUP, MATCH, VLOOKUP

INDIRECT(reference)

Returns the contents of the cell referenced by the specified cell.

e.g. INDIRECT(C1) returns the contents of cell D1 if cell C1 contains "D1"

See also: OFFSET

INT(Any number)

Rounds the number to the nearest integer

See also: CEILING, FLOOR, MOD, ROUND, TRUNC

IPMT(interest rate, period, number of payments, present value [,future value] [,type])

Interest payment of an annuity for a given period based on regular payments and a fixed periodic interest rate. Present value and future value (default is 0) refer to the values which the annuity is worth currently and after all the payments have been made. Type=0 if the payments are due at the end of each period and Type=1 if they are due at the beginning.

See also: FV, PMT, PPMT, RATE

IRR(cash flow [,guess])

Internal rate of return for a series of periodic cash flows given as a reference to a range (e.g. IRR(B1:B6)). Guess (default is 10%) is the estimate of the internal rate of return.

See also: MIRR, NPV, RATE

ISBLANK(reference)

Returns TRUE if the specified reference cell is blank and FALSE otherwise.

See also: ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT

ISERR(expression)

Returns TRUE if the specified expression returns an error and FALSE otherwise.

See also: ISBLANK, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT

ISLOGICAL(expression)

Returns TRUE if the expression returns a logical value and FALSE otherwise.

See also: ISBLANK, ISERR, ISERROR, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT

ISNA(expression)

Returns TRUE if the expression returns the #N/A! error and FALSE otherwise.

See also: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNONTEXT, ISNUMBER, ISREF, ISTEXT

ISNONTEXT(expression)

Returns TRUE if there is a non-text value in the expression and FALSE otherwise.

See also: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNUMBER, ISREF, ISTEXT

ISNUMBER(expression)

Returns TRUE if the expression returns a number and FALSE otherwise.

See also: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISREF, ISTEXT

ISREF(expression)

Returns TRUE if the expression returns a range reference and FALSE otherwise.

See also: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISTEXT

ISTEXT(expression)

Returns TRUE if the expression text and FALSE otherwise.

See also: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF

LEFT(text [,numchars])

Returns the leftmost characters in the text string. Numchars is the number of characters to return.

See also: MID, RIGHT

LEN(text)

Number of characters in the text string, spaces included.

See also: EXACT, SEARCH

LN(positive real number)

Natural (based on the constant e) logarithm.

See also: EXP, LOG, LOG10

LOG(positive real number [,base])

Logarithm of the number. Base default value is 10.

See also: EXP, LN, LOG10

LOG10(positive real number)

Base-10 logarithm.

See also: EXP, LN, LOG

LOOKUP(value, lookup range, result range)

Searches for the value in the lookup range and returns the contents of corresponding position in the result range. The sizes of both ranges have to be the same and both ranges can only contain one row or column.

See also: HLOOKUP, INDEX, VLOOKUP

LOWER(text)

Changes the characters in the specified text to lowercase.

See also: PROPER, UPPER

MATCH(lookup value, lookup range, comparison)

Compares the lookup value against values in the lookup range and returns the position of the matching value in that range. Comparison represents the type of comparison: 1 matches the largest value that is less than or equal to the lookup value, 0 matches the same value and -1 matches the smallest value that is greater than or equal to the lookup value.

See also: HLOOKUP, INDEX, LOOKUP, VLOOKUP

MAX(number list)

Largest value in the specified list of numbers. The list can be a cell range.

See also: AVERAGE, MIN

MID(text, start position, numchars)

Returns the specified number of characters in the text string, beginning at the specified start position.

See also: CODE, FIND, LEFT, RIGHT, SEARCH

MIN(number list)

Smallest value in the specified list of numbers. The list can be a cell range.

See also: AVERAGE, MAX

MINUTE(serial number)

Minute component of the specified time (given as serial number) as an integer between 0 and 59.

See also: DATE, DAY, HOUR, MONTH, NOW, SECOND, WEEKDAY, YEAR

MIRR(cash flows, finance rate, reinvest rate)

Modified internal rate of return for a series (given as a reference to a range)of periodic cash flows. Cash received and cash paid have positive and negative values, respectively. The order of calculation is the order in which the values appear. Finance rate is the interest rate paid on money used in the cash flow and reinvest rate is the interest rate received on money reinvested from the cash flow.

See also: IRR, NPV, RATE

MOD(any number, divisor)

Remainder after dividing a number by the specified divisor.

See also: INT, ROUND, TRUNC

MONTH(serial number)

Month correspondent to the specified date (given as serial number) as an integer between 1 (January) and 12 (December).

See also: DAY, DATE, MINUTE, HOUR, NOW, SECOND, WEEKDAY, YEAR

N(value)

Tests the supplied value and returns the value if it is a number or 1 if the value is the logical function TRUE(). All other values are returned as 0.

See also: T, VALUE

NA()

Returns the error value #N/A (not available).

See also: ISNA

NOT(logical)

Returns TRUE if logical is FALSE and vice-versa.

See also: AND, IF, OR

NOW()

Current date and time as a serial number. The numbers to the left of the decimal point represent the date; to the right represent the time.

See also: DATE, DAY, HOUR, MINUTE, MONTH, SECOND, TODAY, WEEKDAY, YEAR

NPER(interest rate, pmt, pf, [,fv] [,type])

Number of periods of an investment based on regular periodic payments and a fixed interest rate. Pmt is the fixed payment made each period, pf is the present value that a series of future payments is currently worth, fv is the balance to attain after the final payment (default is 0) and type indicates when the payments are due: 0 refers to payments due at the end of the period and 1 at the beginning.

See also: FV, IPMT, PMT, PPMT, PV, RATE

NPV(discount rate, value list)

Net present value of an investment based on a series of periodic payments and a discount rate. Value list contains values that represent payments and income.

See also: FV, IRR, PV

Copyright © 2000-2018 StatsDirect Limited, all rights reserved. Download a free trial here.