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-2019 StatsDirect Limited, all rights reserved. Download a free trial here.