LARGE

Returns the kth largest value in an input range. Takes the form =LARGE(array, k), where k is the position from the largest value in array you want to find. See Using Functions That Analyze Rank and Percentile on page 604.

LCM

Returns the least common multiple of integers (the smallest positive integer that is a multiple of all arguments). Takes the form =LCM(number1, number2, …), and accepts up to 29 numeric integer arguments.

LEFT

Returns the leftmost series of characters from a string. Takes the form =LEFT(text, num_chars), where num_chars indicates how many characters you want to extract from the string (1 if omitted). See Using the Substring Text Functions on page 547.

LEFTB

Returns the leftmost series of characters from a string, based on the specified number of bytes. Takes the form =LEFT(text, num_bytes), where num_bytes indicates how many characters you want to extract from the text string, based on bytes.

LEN

Returns the number of displayed characters in an entry. Takes the form =LEN(text), where text is a number, a string enclosed in quotation marks, or a reference to a cell. Trailing zeros are ignored, but spaces are counted. See Using Selected Text Functions on page 544.

LENB

Returns the number of characters in an entry, expressed in bytes. Takes the form =LENB(text). It is otherwise identical to the LEN function. This function is intended for use with double-byte characters.

LINEST

Calculates the statistics for a line using the least squares method to arrive at a slope that best describes the given data. Takes the form LINEST(known_y’s, known_x’s, const, stats). For arguments and usage details, see The LINEST Function on page 610.

LN

Returns the natural (base e) logarithm of the positive number referred to by its argument. Takes the form =LN(number). LN is the inverse of the EXP function.

LOG

Returns the logarithm of a positive number using a specified base. Takes the form =LOG(number, base). If you don’t include the base argument, Excel assumes the base is 10.

LOG10

Returns the base-10 logarithm of a number. Takes the form =LOG10(number), where number is a positive real number.

LOGEST

Returns statistics describing known data in terms of an exponential curve. Takes the form =LOGEST(known_y’s, known_x’s, const, stats). For arguments and usage details, see The LOGEST Function on page 616.

LOGNORM.INV

Returns the inverse of the lognormal cumulative distribution function of x, where ln(x) is usually distributed with parameters mean and standard_dev. Takes the form =LOGINV(probability, mean, standard_dev), where probability is a probability associated with the lognormal distribution, mean is the mean of ln(x), and standard_dev is the standard deviation of ln(x). The old form of this function is LOGINV, which is still supported as a Compatibility Function.

LOGNORM.DIST

Returns the cumulative lognormal distribution of x, where ln(x) is usually distributed with parameters mean and standard_dev. Takes the form =LOGNORMDIST(x, mean, standard_dev, cumulative), where x is the value at which to evaluate the function, mean is the mean of ln(x), and standard_dev is the standard deviation of ln(x). If cumulative is TRUE, LOGNORM.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is LOGNORMDIST, which is still supported as a Compatibility Function.

LOOKUP

Looks for a specified value in a one- or two-dimensional range. Takes two forms, vector or array: =LOOKUP(lookup_value, lookup_vector, result_vector) or =LOOKUP(lookup_value, array), where lookup_value is the value to look for, lookup_vector is a one-row or one-column range containing the lookup values sorted in alphabetical order, result_vector is a range that contains the result values and must be identical in size to lookup_vector, and array is a two-dimensional range containing both lookup and result values. The array form of this function works like HLOOPKUP if array is wider than it is tall or like VLOOKUP if array is taller than it is wide. See Using Selected Lookup and Reference Functions on page 555.

LOWER

Converts a text string to all lowercase letters. Takes the form =LOWER(text). See Using Selected Text Functions on page 544.

MATCH

Returns the position in a list of the item that most closely matches a lookup value. Takes the form =MATCH(lookup_value, lookup_array, match_type), where lookup_value is the value or string to look up, lookup_array is the range that contains the sorted values to compare, and match_type defines the rules for the search (if 1 or omitted, finds, in a range sorted in ascending order, the largest value that is less than or equal to lookup_value; if 0, finds the value that is equal to lookup_value; if –1, finds, in a range sorted in descending order, the smallest value that is greater than or equal to lookup_value). See Using Selected Lookup and Reference Functions on page 555.

MAX

Returns the largest value in a range. Takes the form =MAX(number1, number2, …), and accepts up to 30 arguments, ignoring text, error values, and logical values. See Using Built-In Statistical Functions on page 601.

MAXA

Acts like MAX except text and logical values are included in the calculation. See Using Built-In Statistical Functions on page 601.

MDETERM

Returns the matrix determinant of an array. Takes the form =MDETERM(array), where array is a numeric array with an equal number of rows and columns.

MDURATION

Calculates the annual modified duration for a security with interest payments made on a periodic basis, adjusted for market yield per number of coupon payments per year. Takes the form =MDURATION(settlement, maturity, coupon, yield, frequency, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, coupon is the security’s annual coupon rate, yield is the annual yield of the security, frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly), and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

MEDIAN

Computes the median of a set of numbers. Takes the form =MEDIAN(number1, number2, …), and accepts up to 30 arguments, ignoring text, error values, and logical values. See Using Built-In Statistical Functions on page 601.

MID

Extracts a series of characters (substring) from a text string. Takes the form =MID(text, start_num, num_chars), where text is the string from which you want to extract the substring, start_num is the location in the string where the substring begins (counting from the left), and num_chars is the number of characters you want to extract. See Using the Substring Text Functions on page 547.

MIDB

Extracts a series of characters (substring) from a text string, based on the number of bytes you specify. Takes the form =MID(text, start_num, num_bytes), where text is the string from which you want to extract the substring, start_num is the location in the string where the substring begins (counting from the left), and num_bytes is the number of characters you want to extract, in bytes. This function is for use with double-byte characters.

MIN

Returns the smallest value in a range. Takes the form =MIN(number1, number2, …), and accepts up to 30 arguments, ignoring text, error values, and logical values. See Using Built-In Statistical Functions on page 601.

MINA

Acts like MIN except text and logical values are included in the calculation. See Using Built-In Statistical Functions on page 601.

MINUTE

Returns the minute portion of a serial date/time value. Takes the form =MINUTE(serial_number), where serial_number can be a time/date value, a reference, or text in time/date format enclosed in quotation marks. See Working with Date and Time Functions on page 575.

MINVERSE

Returns the inverse matrix for the matrix stored in an array. Takes the form =MINVERSE(array), where array is a numeric array with an equal number of rows and columns.

MIRR

Calculates the rate of return of an investment, taking into account the cost of borrowed money and assuming resulting cash inflows are reinvested. Takes the form =MIRR(values, finance rate, reinvestment rate), where values is an array or a reference to a range of cells that contain numbers beginning with the cost expressed as a negative value, finance rate is the rate at which you borrow money, and reinvestment rate is the rate at which you reinvest the returns. See Calculating Investments on page 581.

MMULT

See MODE.MULT

MOD

Returns the remainder of a division operation (modulus). Takes the form =MOD(number, divisor). If number is smaller than divisor, the result of the function equals number. If number is exactly divisible by divisor, the function returns 0. If divisor is 0, MOD returns the #DIV/0! error value. See Using Selected Mathematical Functions on page 539.

MODE.MULT

Returns a vertical array of the most frequently occurring values in a set of numbers. Takes the form =MODE.MULT(number1, number2, …), and accepts up to 30 arguments, ignoring text, error values, and logical values. The old form of this function is MODE, which is still supported as a Compatibility Function. See Using Built-In Statistical Functions on page 601.

MODE.SNGL

Determines which value occurs most frequently in an array or range. Takes the form =MODE.SNGL(number1, number2, …), and accepts up to 30 arguments, ignoring text, error values, and logical values. The old form of this function is LOGNORMDIST, which is still supported as a Compatibility Function. See Using Built-In Statistical Functions on page 601.

MONTH

Returns the value of the month portion of a serial date/time value. Takes the form =MONTH(serial_number), where serial_number can be a date value, a reference, or text in date format enclosed in quotation marks. See Working with Date and Time Functions on page 575.

MROUND

Rounds any number to a multiple you specify. Takes the form =MROUND(number, multiple), where number and multiple must both have the same sign. The function rounds up if the remainder after dividing number by multiple is at least half the value of multiple. See Using the Flexible MROUND Function on page 543.

MULTINOMIAL

Returns the ratio of the factorial of a sum of values to the product of factorials. Takes the form =MULTINOMIAL(num1, num2, …), where nums are up to 29 values for which you want to find the multinomial.

N

Returns a value converted to a number. Takes the form =N(value), where value is the value you want to convert. This function is included for compatibility with other spreadsheet programs but is not necessary in Excel.

NA

Represents an alternative for the error value #N/A. The NA function accepts no arguments and takes the form =NA().

NEGBINOM.DIST

Returns the negative binomial distribution (the probability that there will be number_f failures before the number_s-th success, when the constant probability of a success is probability_s). Takes the form =NEGBINOM.DIST(number_f, number_s, probability_s, cumulative), where number_f is the number of failures, number_s is the threshold number of successes, and probability_s is the probability of a success. If cumulative is TRUE, NEGBINOM.DIST returns the cumulative distribution function; otherwise, it returns the probability density function. The old form of this function is NEGBINOMDIST, which is still supported as a Compatibility Function.

NETWORKDAYS

Returns the number of working days between two given dates. Takes the form =NETWORKDAYS(start_date, end_date, holidays), where start_date is the date you want to count from, end_date is the date you want to count to, and holidays is an array or reference containing any dates you want to exclude. See Working with Specialized Date Functions on page 578.

NOMINAL

Returns the nominal annual interest rate. Takes the form =NOMINAL(effect_rate, npery), where effect_rate is the effective interest rate, and npery is the number of compounding periods per year.

NORM.DIST

Returns the normal cumulative distribution for the specified mean and standard deviation. Takes the form =NORM.DIST(x, mean, standard_dev, cumulative), where x is the value for which you want the distribution, mean is the arithmetic mean of the distribution, standard_dev is the standard deviation of the distribution, and cumulative is a logical value that determines the form of the function. (If cumulative is TRUE, NORM.DIST returns the cumulative distribution function; if cumulative is FALSE, it returns the probability density function.) The old form of this function is NORMDIST, which is still supported as a Compatibility Function.

NORM.INV

Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation. Takes the form =NORM.INV(probability, mean, standard_dev), where probability is a probability corresponding to the normal distribution, mean is the arithmetic mean of the distribution, and standard_dev is the standard deviation of the distribution. The old form of this function is NORMINV, which is still supported as a Compatibility Function.

NORM.S.DIST

Returns the standard normal cumulative distribution function. Takes the form =NORM.S.DIST(z). The old form of this function is NORMSDIST, which is still supported as a Compatibility Function.

NORM.S.INV

Returns the inverse of the standard normal cumulative distribution (with a mean of zero and a standard deviation of one). Takes the form =NORM.S.INV(probability), where probability is a probability corresponding to the normal distribution. The old form of this function is NORMSINV, which is still supported as a Compatibility Function.

NOT

Helps develop compound conditional test formulas in conjunction with the simple logical operators =, >, <, >=, <=, and <>. The NOT function has only one argument and takes the form =NOT(logical), where logical can be a conditional test, an array, or a reference to a cell containing a logical value. See Using Selected Logical Functions on page 550.

NOW

Returns the serial value of the current date and time. Takes the form =NOW(), and accepts no arguments. See Working with Date and Time Functions on page 575.

NPER

Computes the number of periods required to amortize a loan, given a specified periodic payment. Takes the form =NPER(rate, payment, present value, future value, type), where rate is the interest rate, payment is the amount of each periodic payment when individual amounts are the same, present value is the investment value today, future value is the investment value at the end of the term, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See Calculating Investments on page 581.

NPV

Determines the profitability of an investment. Takes the form =NPV(rate, value1, value2, …), where rate is the interest rate, and the values represent up to 29 payments (or any size array) when individual amounts differ. See Calculating Investments on page 581.

OCT2BIN

Converts an octal number to binary. Takes the form =OCT2BIN(number, places), where number is the octal number you want to convert, and places is the number of characters to use (if omitted, OCT2BIN uses the minimum number of characters necessary).

OCT2DEC

Converts an octal number to decimal. Takes the form =OCT2DEC(number), where number is the octal number you want to convert.

OCT2HEX

Converts an octal number to hexadecimal. Takes the form =OCT2HEX(number, places), where number is the octal number you want to convert, and places is the number of characters to use (if omitted, uses the minimum number of characters necessary).

ODD

Rounds a number up to the nearest odd integer. Takes the form =ODD(number). Negative numbers are correspondingly rounded down. See Using the Rounding Functions on page 542.

ODDFPRICE

Returns the price per $100 of face value for a security having an odd first period. Takes the form =ODDFPRICE(settlement, maturity, issue, first coupon, rate, yield, redemption, frequency, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, issue is the issue date of the security, first coupon is the security’s first coupon due date as a serial date value, rate is the interest rate of the security at the issue date, yield is the annual yield of the security, redemption is the value of the security at redemption, frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly), and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360).

ODDFYIELD

Calculates the yield of a security that has an odd first period. Takes the form =ODDFYIELD(settlement, maturity, issue, first coupon, rate, price, redemption, frequency, basis), where price is the security’s price. See ODDFPRICE for additional argument definitions.

ODDLPRICE

Calculates the price per $100 face value of a security having an odd last coupon period. Takes the form =ODDLPRICE(settlement, maturity, last interest, rate, yield, redemption, frequency, basis), where last interest is the security’s last coupon due date as a serial date value. See ODDFPRICE for additional argument definitions.

ODDLYIELD

Calculates the yield of a security that has an odd last period. Takes the form =ODDLYIELD(settlement, maturity, last interest, rate, price, redemption, frequency, basis), where last interest is the security’s last coupon due date, and price is the security’s price. See ODDFPRICE for additional argument definitions.

OFFSET

Returns a reference of a specified height and width, located at a specified position relative to another specified reference. Takes the form =OFFSET(reference, rows, cols, height, width), where reference specifies the position from which the offset is calculated, rows and cols specify the vertical and horizontal distance from reference, and height and width specify the shape of the reference returned by the function. The rows and cols arguments can be positive or negative: Positive values specify offsets below and to the right of reference; negative values specify offsets above and to the left of reference.

OR

Helps develop compound conditional test formulas in conjunction with logical operators. Takes the form =OR(logical1, logical2, …), where the logicals can be up to 30 conditional tests, arrays, or references to cells that contain logical values. See Using Selected Logical Functions on page 550.

PEARSON

Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from –1 to 1 (inclusive) and reflects the extent of a linear relationship between two data sets. This function takes the form =PEARSON(array1, array2), where array1 is a set of independent values, and array2 is a set of dependent values.

PERCENTILE.INC PERCENTILE.EXC

Returns the member of an input range that is at a specified percentile ranking. Takes the form =PERCENTILE.INC(array, k), where array is the input range (inclusive or exclusive, depending on the form of the function used), and k is the rank you want to find. The old form of this function is PERCENTILE, which is still supported as a Compatibility Function. See Using Functions That Analyze Rank and Percentile on page 604.

PERCENTRANK.INC PERCENTRANK.EXC

Returns a percentile ranking for any member of a data set. Takes the form =PERCENTRANK.INC(array, x, significance), where array specifies the input range (inclusive or exclusive, depending on the form of the function used), x specifies the value whose rank you want to obtain, and the optional significance indicates the number of digits of precision you want. If significance is omitted, results are rounded to three digits (0.xxx or xx.x%). The old form of this function is PERCENTRANK, which is still supported as a Compatibility Function. See Using Functions That Analyze Rank and Percentile on page 604.

PERMUT

Returns the number of permutations for a given number of objects that can be selected from a larger group of objects. Takes the form =PERMUT(number, number_chosen), where number is an integer that describes the total number of objects you want to use, and number_chosen is an integer that describes the number of objects you want in each permutation.

PHONETIC

Extracts, in Japanese, Simplified or Traditional Chinese, and Korean, the phonetic (furigana) characters from a referenced cell or range. Takes the form =PHONETIC(reference), where reference denotes a single cell or range. If reference is a range, the function returns phonetic text only from the cell in the upper-left corner.

PI

Returns the value of pi, accurate to 14 decimal places (3.14159265358979). Takes the form =PI(), and accepts no arguments, but you must still type empty parentheses after the function name. To calculate the area of a circle, multiply the square of the circle’s radius by the PI function.

PMT

Computes the periodic payment required to amortize a loan over a specified number of periods. Takes the form =PMT(rate, nper, pv, fv, type), where rate is the interest rate, nper is the term (periods) of the investment, pv is the investment value today, fv is the investment value at the end of the term, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See Calculating Investments on page 581.

POISSON.DIST

Returns the Poisson distribution. Takes the form =POISSON.DIST(x, mean, cumulative), where x is the number of events, mean is the expected numeric value, and cumulative is a logical value that determines the form of the probability distribution returned. (If cumulative is TRUE, POISSON.DIST returns the cumulative Poisson probability that the number of random events occurring will be between zero and x inclusive; if cumulative is FALSE, it returns the Poisson probability mass function that the number of events occurring will be exactly x.) The old form of this function is POISSON, which is still supported as a Compatibility Function.

POWER

Returns the result of a number raised to a power. Takes the form =POWER(number, power), where number is the base number, and power is the exponent to which the base number is raised.

PPMT

Computes the principal component of an individual payment made to repay a loan over a specified time period with constant periodic payments and a constant interest rate. Takes the form =PPMT(rate, period, nper, pv, fv, type), where rate is the interest rate, period is the number of an individual periodic payment, nper is the term (periods) of the investment, pv is the investment value today, fv is the investment value at the end of the term, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See Calculating Investments on page 581.

PRICE

Calculates the price per $100 of a security that pays periodic interest. Takes the form =PRICE(settlement, maturity, rate, yield, redemption, frequency, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, rate is the interest rate of the security at the issue date, yield is the annual yield of the security, redemption is the value of the security at redemption, frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly), and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

PRICEDISC

Returns the price per $100 of a discounted security. Takes the form =PRICEDISC(settlement, maturity, discount, redemption, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, discount is the security’s discount rate, redemption is the value of the security at redemption, and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

PRICEMAT

Returns the price per $100 of a security that pays interest at maturity. Takes the form =PRICEMAT(settlement, maturity, issue, rate, yield, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, issue is the issue date of the security, rate is the interest rate of the security at the issue date, yield is the annual yield of the security, and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

PROB

Returns the probability that values in a range are between two limits. Takes the form =PROB(x_range, prob_range, lower_limit, upper_limit), where x_range is the range of numeric values of x with which there are associated probabilities, prob_range is a set of probabilities associated with values in x_range, lower_limit is the lower bound on the value for which you want a probability, and upper_limit is the optional upper bound on the value for which you want a probability.

PRODUCT

Multiplies all the numbers referenced by its arguments. Takes the form =PRODUCT(number1, number2, …), and accepts as many as 30 arguments. Text, logical values, and blank cells are ignored. See Using Selected Mathematical Functions on page 539.

PROPER

Capitalizes the first letter in each word and any other letters in a text string that do not follow another letter—all other letters are converted to lowercase. Takes the form =PROPER(text). See Using Selected Text Functions on page 544.

PV

Computes the present value of a series of equal periodic payments or a lump-sum payment. Takes the form =PV(rate, nper, payment, future value, type), where rate is the interest rate, nper is the term (periods) of the investment, payment is the amount of each periodic payment when individual amounts are the same, future value is the investment value at the end of the term, and type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period). See Calculating Investments on page 581.

QUARTILE.INC QUARTILE.EXC

Returns the value in an input range (either inclusive or exclusive, depending on the form of the function used) that represents a specified quarter-percentile. Takes the form =QUARTILE.INC(array, quart). The old form of this function is QUARTILE, which is still supported as a Compatibility Function. For usage and argument details, see The PERCENTILE and QUARTILE Functions on page 605.

QUOTIENT

Returns the integer portion of a division. Takes the form =QUOTIENT(numerator, denominator), where numerator is the dividend and denominator is the divisor.

RADIANS

Converts degrees to radians. Takes the form =RADIANS(angle), where angle represents an angle measured in degrees.

RAND

Generates a random number between 0 and 1. Takes the form =RAND() with no arguments, but you must still type empty parentheses after the function name. The result changes with each sheet recalculation. See Using Selected Mathematical Functions on page 539.

RANDBETWEEN

Generates random integer values between a specified range of numbers. Takes the form =RANDBETWEEN(bottom, top), where bottom is the smallest, and top is the largest integer you want to use, inclusive. See Using Selected Mathematical Functions on page 539.

RANK.AVG

Returns the ranked position of a particular number within a set of numbers. Takes the form =RANK.AVG(number, ref, order). If more than one value has the same rank, the function returns the average rank. The old form of this function is RANK, which is still supported as a Compatibility Function. For usage and argument details, see The RANK Functions on page 606.

RANK.EQ

Returns the ranked position of a particular number within a set of numbers. Takes the form =RANK.EQ(number, ref, order). If more than one value has the same rank, the function returns the top rank. The old form of this function is RANK, which is still supported as a Compatibility Function. For usage and argument details, see The RANK Functions on page 606.

RATE

Calculates the rate of return of an investment that generates a series of equal periodic payments or a single lump-sum payment. Takes the form =RATE(nper, payment, present value, future value, type, guess), where nper is the term (periods) of the investment, payment is the amount of each periodic payment when individual amounts are the same, present value is the investment value today, future value is the investment value at the end of the term, type indicates when payments are made (0 or omitted = at end of period, 1 = at beginning of period), and guess is an approximate interest rate (10 percent if omitted). See Calculating Investments on page 581.

RECEIVED

Calculates the amount received at maturity for a fully invested security. Takes the form =RECEIVED(settlement, maturity, investment, discount, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, investment is the amount invested in the security, discount is the security’s discount rate, and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

REPLACE

Substitutes one string of characters with another string. Takes the form =REPLACE(old_text, start_num, num_chars, new_text), where old_text is the text string where you want to replace characters, start_num specifies the starting character to replace, num_chars specifies the number of characters to replace (counting from the left), and new_text specifies the text string to insert. See Using the Substring Text Functions on page 547.

REPLACEB

Substitutes one string of characters with another string. Takes the form =REPLACEB(old_text, start_num, num_bytes, new_text), where old_text is the text string in which you want to replace characters, start_num specifies the starting character to replace, num_bytes specifies the number of bytes to replace, and new_text specifies the text string to insert. This function is for use with double-byte characters.

REPT

Fills a cell with a string of characters repeated a specified number of times. Takes the form =REPT(text, number_times), where text specifies a string in double quotation marks, and number_times specifies how many times to repeat text. The result of the function cannot exceed 32,767 characters.

RIGHT

Returns the rightmost series of characters from a string. Takes the form =RIGHT(text, num_chars), where num_chars indicates how many characters you want to extract from the text string (1, if omitted). Blank spaces count as characters. See Using the Substring Text Functions on page 547.

RIGHTB

Returns the rightmost series of characters from a string, based on the number of bytes you specify. Takes the form =RIGHTB(text, num_bytes), where num_bytes indicates how many characters you want to extract from the text string, based on bytes. This function is for use with double-byte characters.

ROMAN

Converts an Arabic numeral to Roman numerals, as text. Takes the form =ROMAN(number, form), where number is the Arabic numeral you want to convert, and form is a number specifying the type of Roman numeral you want (1, 2, or 3 = more concise notation; 4 or FALSE = simplified notation; TRUE = classic notation).

ROUND

Rounds numbers to a specified number of decimal places. Takes the form =ROUND(number, num_digits), where number can be a number, a reference to a cell that contains a number, or a formula that results in a number; and num_digits can be any positive or negative integer and determines the number of decimal places. Use a negative num_digits to round to the left of the decimal; use zero to round to the nearest integer. See Using the Rounding Functions on page 542.

ROUNDDOWN

Rounds numbers down to a specified number of decimal places. Takes the same form and arguments as ROUND. See Using the Rounding Functions on page 542.

ROUNDUP

Rounds numbers up to a specified number of decimal places. Takes the same form and arguments as ROUND. See Using the Rounding Functions on page 542.

ROW

Returns the row number of the referenced cell or range. Takes the form =ROW(reference). If reference is omitted, the result is the row number of the cell containing the function. If reference is a range or a name and the function is entered as an array (by pressing Ctrl+Shift+Enter), the result is an array of the numbers of each of the rows or columns in the range. See Using Selected Lookup and Reference Functions on page 555.

ROWS

Returns the number of rows in a reference or an array. Takes the form =ROWS(array), where array is an array constant, a range reference, or a range name. See Using Selected Lookup and Reference Functions on page 555.

RSQ

Returns the square of the Pearson product moment correlation coefficient through data points in the arrays known_y’s and known_x’s. Takes the form =RSQ(known_y’s, known_x’s).

RTD

Returns real-time data from a program that supports COM automation. Takes the form =RTD(progID, server, topic1, topic2, …), where progID is the program identifier (enclosed in quotation marks) for a registered COM automation add-in that has been installed on the local computer, server is the name of the server where the add-in should be run (if other than the local computer), and topics are up to 28 parameters describing the real-time data you want.

SEARCH

Returns the position of specified text within a string. Takes the form =SEARCH(find_text, within_text, start_num), where find_text is the text you want to find, within_text indicates where to look, and start_num specifies the character position in within_text where you want to begin the search. See Using the Substring Text Functions on page 547.

SEARCHB

Returns the position of specified text within a string, expressed in bytes. Takes the form =SEARCHB(find_text, within_text, start_num), and is otherwise identical to SEARCH.

SECOND

Returns the seconds portion of a serial date/time value. Takes the form =SECOND(serial_number), where serial_number can be a time/date value, a reference, or text in time/date format enclosed in quotation marks. See Working with Date and Time Functions on page 575.

SERIESSUM

Returns the sum of a power series. Takes the form =SERIESSUM(x, n, m, coefficients), where x is the input value to the power series, n is the initial power to which you want to raise x, m is the step by which to increase n for each term in the series, and coefficients is a set of coefficients by which each successive power of x is multiplied. The number of values in coefficients determines the number of terms in the power series.

SIGN

Determines the sign of a number. Returns 1 if the number is positive, zero (0) if the number is 0, and –1 if the number is negative. Takes the form =SIGN(number), where number is any real number.

SIN

Returns the sine of an angle. The complement of the COS function, it takes the form =SIN(number), where number is the angle in radians.

SINH

Returns the hyperbolic sine of a number. Takes the form =SINH(number), where number is any real number.

SKEW

Returns the skew of a distribution (the degree of asymmetry of a distribution around its mean). Takes the form =SKEW(number1, number2, …), and accepts up to 30 arguments.

SLN

Returns straight-line depreciation for an asset for a single period. Takes the form =SLN(cost, salvage, life), where cost is the initial asset cost, salvage is the remaining value after asset is fully depreciated, and life is the length of depreciation time. See Calculating Depreciation on page 588.

SLOPE

Returns the slope of a linear regression line. Takes the form =SLOPE(known_y’s, known_x’s). For arguments and usage details, see The SLOPE Function on page 615.

SMALL

Returns the kth smallest value in an input range. Takes the form =SMALL(array, k), where k is the position from the smallest value in array you want to find. See Using Functions That Analyze Rank and Percentile on page 604.

SQRT

Returns the positive square root of a number. Takes the form =SQRT(number).

SQRTPI

Returns the square root of (number * pi). Takes the form =SQRTPI(number).

STANDARDIZE

Returns a normalized value from a distribution characterized by mean and standard_dev. Takes the form =STANDARDIZE(x, mean, standard_dev), where x is the value you want to normalize, mean is the arithmetic mean of the distribution, and standard_dev is the standard deviation of the distribution.

STDEV.S

Computes standard deviation, assuming that the arguments represent only a sample of the total population. Takes the form =STDEV.S(number1, number2, …), and accepts up to 30 arguments. The old form of this function is STDEV, which is still supported as a Compatibility Function. See Using Sample and Population Statistical Functions on page 607.

STDEVA

Acts like STDEV except text and logical values are included in the calculation. See Using Sample and Population Statistical Functions on page 607.

STDEV.P

Computes the standard deviation, assuming that the arguments represent the total population. Takes the form =STDEV.P(number1, number2, …). The old form of this function is STDEVP, which is still supported as a Compatibility Function. See Using Sample and Population Statistical Functions on page 607.

STDEVPA

Acts like STDEV.P except that text and logical values are included in the calculation. See Using Sample and Population Statistical Functions on page 607.

STEYX

Calculates the standard error of a regression. Takes the form =STEYX(known_y’s, known_x’s). For arguments and usage details, see The SLOPE Function on page 615.

SUBSTITUTE

Replaces specified text with new text within a specified string. Takes the form =SUBSTITUTE(text, old_text, new_text, instance_num), where text is the string you want to work on; old_text is the text to be replaced; new_text is the text to substitute; and instance_num is optional, indicating a specific occurrence of old_text within text. See Using the Substring Text Functions on page 547.

SUBTOTAL

Returns a subtotal in a table or database. Takes the form =SUBTOTAL(function_num, ref1, ref2, …), where function_num is a number that specifies which function to use in calculating subtotals (1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 6=PRODUCT, 7=STDEV, 8=STDEVP, 9=SUM, 10=VAR, 11=VARP), and the refs are 1 to 29 ranges or references for which you want the subtotal.

SUM

Totals a series of numbers. Takes the form =SUM(num1, num2, …), where the nums (up to 30) can be numbers, formulas, ranges, or cell references. Ignores arguments that refer to text values, logical values, or blank cells. See Using the SUM Function on page 537.

SUMIF

Tests each cell in a range before adding it to the total. Takes the form =SUMIF(range, criteria, sum_range), where range is the range you want to test, criteria is the logical test to be performed on each cell, and sum_range specifies the cells to be totaled. See Using Built-In Statistical Functions on page 601.

SUMIFS

Tests each cell in a range using multiple criteria before adding it to the total. Takes the form =SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, …), where sum_range is the range containing values you want to sum; criteria_rangeX is a cell range containing data to be evaluated; and criteriaX is a cell range containing values, expressions, references, or text that define which cells will be added to the total.

SUMPRODUCT

Multiplies the value in each cell in a specified range by the corresponding cell in another equal-sized range and then adds the results. Takes the form =SUMPRODUCT(array1, array2, array3, …) and can include up to 30 arrays. Nonnumeric entries are treated as zero. See Using Selected Mathematical Functions on page 539.

SUMSQ

Returns the sum of the squares of each specified value in a specified range. Takes the form =SUMSQ(number1, number2, …), and accepts up to 30 arguments or a single array or array reference.

SUMX2MY2

Calculates the sum of the differences of the squares of the corresponding values in x and y. Takes the form =SUMX2MY2(array_x, array_y), where x and y are arrays that contain the same number of elements.

SUMX2PY2

Calculates the sum of the sum of the squares of the corresponding values in x and y. Takes the form =SUMX2PY2(array_x, array_y), where x and y are arrays that contain the same number of elements.

SUMXMY2

Calculates the sum of the squares of the differences of the corresponding values in x and y. Takes the form =SUMXMY2(array_x, array_y), where x and y are arrays that contain the same number of elements.

SYD

Computes depreciation for a specific time period with the sum-of-the-years’-digits method. Takes the form =SYD(cost, salvage, life, period), where cost is the initial asset cost, salvage is the remaining value after the asset is fully depreciated, life is the length of depreciation time, and period is the individual period to be computed. See Calculating Depreciation on page 588.

T

Returns the text referred to by value. Takes the form =T(value), where value is the value you want to test. This function is included for compatibility with other spreadsheet programs but is not necessary in Excel.

TAN

Returns the tangent of an angle. Takes the form =TAN(number), where number is the angle in radians.

TANH

Returns the hyperbolic tangent of a number. Takes the form =TANH(number), where number is any real number.

TBILLEQ

Calculates the bond-equivalent yield for a U.S. Treasury bill. Takes the form =TBILLEQ(settlement, maturity, discount), where settlement is the day you pay for the security, maturity is the maturity date of the security, and discount is the discount rate of the security. See Analyzing Securities on page 591.

TBILLPRICE

Calculates the price per $100 of face value for a U.S. Treasury bill. Takes the form =TBILLPRICE(settlement, maturity, discount), where settlement is the day you pay for the security, maturity is the maturity date of the security, and discount is the discount rate of the security. See Analyzing Securities on page 591.

TBILLYIELD

Calculates a U.S. Treasury bill’s yield. Takes the form =TBILLYIELD(settlement, maturity, price), where settlement is the day you pay for the security, maturity is the maturity date of the security, and price is the security’s price. See Analyzing Securities on page 591.

T.DIST.2T

Returns the two-tailed Student’s t-distribution. Takes the form =T.DIST.2T(x, deg_freedom), where x is the numeric value at which to evaluate the distribution, and deg_freedom is an integer indicating the number of degrees of freedom. The old form of this function is TDIST, which is still supported as a Compatibility Function.

T.DIST.RT

Returns the right-tailed Student’s t-distribution. This function takes the form =T.DIST.RT(x, deg_freedom), where x is the numeric value at which to evaluate the distribution, and deg_freedom is an integer indicating the number of degrees of freedom. The old form of this function is TDIST, which is still supported as a Compatibility Function.

TEXT

Converts a number into a text string using a specified format. Takes the form =TEXT(value, format_text), where value can be any number, formula, or cell reference; and format_text specifies the format using built-in custom formatting symbols. See Using Selected Text Functions on page 544.

TIME

Returns the decimal number for a particular time. Takes the form =TIME(hour, minute, second), where hour is a number from 0 (zero) to 23 representing the hour, minute is a number from 0 to 59 representing the minute, and second is a number from 0 to 59 representing the second.

TIMEVALUE

Translates a time into a decimal value. Takes the form =TIMEVALUE(time_text), where time_text represents a time entered as text in quotation marks. See Working with Date and Time Functions on page 575.

T.INV

Returns the left-tailed inverse of the Student’s t-distribution as a function of the probability and the degrees of freedom. Takes the form =T.INV(probability, deg_freedom), where probability is the probability associated with the two-tailed Student’s t-distribution, and deg_freedom is the number of degrees of freedom to characterize the distribution. The old form of this function is TINV, which is still supported as a Compatibility Function.

T.INV.2T

Returns the two-tailed inverse of the Student’s t-distribution. Takes the form =T.INV.2T(probability, deg_freedom), where probability is the probability associated with the two-tailed Student’s t-distribution, and deg_freedom is the number of degrees of freedom to characterize the distribution. The old form of this function is TINV, which is still supported as a Compatibility Function.

TODAY

Returns the serial value of the current date. Takes the form =TODAY(), and accepts no arguments. See Working with Date and Time Functions on page 575.

TRANSPOSE

Changes the horizontal or vertical orientation of an array. Takes the form =TRANSPOSE(array). If array is vertical, the result is horizontal, and vice versa. Must be entered as an array formula by pressing Ctrl+Shift+Enter, with a range selected with the same proportions as array. See Using Selected Lookup and Reference Functions on page 555.

TREND

Returns values of points that lie along a linear trendline. Takes the form =TREND(known_y’s, known_x’s, new_x’s, const). For arguments and usage details, see The TREND Function on page 613.

TRIM

Removes leading, trailing, and extra blank characters from a string, leaving single spaces between words. Takes the form =TRIM(text). See Using Selected Text Functions on page 544.

TRIMMEAN

Returns the mean of the interior of a data set (the mean taken by excluding a percentage of data points from the top and bottom tails of a data set). This function takes the form =TRIMMEAN(array, percent), where array is the array or range of values to trim and average, and percent is the fractional number of data points to exclude from the calculation.

TRUE

Represents an alternative for the logical condition TRUE. The TRUE function accepts no arguments, and takes the form =TRUE(). See Using Selected Logical Functions on page 550.

TRUNC

Truncates everything to the right of the decimal point, regardless of its sign. Takes the form =TRUNC(number, num_digits). Truncates everything after the specified num_digits to the right of the decimal point. See Using the Rounding Functions on page 542.

T.TEST

Returns the probability associated with a Student’s t-test. Takes the form =T.TEST(array1, array2, tails, type), where array1 is the first data set, array2 is the second data set, tails specifies the number of distribution tails (if 1, uses the one-tailed distribution; if 2, uses the two-tailed distribution), and type is the kind of t-test to perform (1 = paired, 2 = two-sample equal variance, 3 = two-sample unequal variance). The old form of this function is TTEST, which is still supported as a Compatibility Function.

TYPE

Determines the type of value a cell contains. Takes the form =TYPE(value). The result is one of the following numeric codes: 1 (number), 2 (text), 4 (logical value), 16 (error value), or 64 (array). See Using Selected Lookup and Reference Functions on page 555.

UPPER

Converts a text string to all uppercase letters. Takes the form =UPPER(text). See Using Selected Text Functions on page 544.

VALUE

Converts a text string that represents a number to a number. Takes the form =VALUE(text), where text is the text enclosed in quotation marks or a reference to a cell containing the text you want to convert. This function is included for compatibility with other spreadsheet programs but is not necessary in Excel.

VARA

Acts like VAR.S except text and logical values are included in the calculation. See Using Sample and Population Statistical Functions on page 607.

VAR.S

Computes variance, assuming that the arguments represent only a sample of the total population. Takes the form =VAR.S(number1, number2, …), accepting up to 30 arguments. See Using Sample and Population Statistical Functions on page 607. The old form of this function is VAR, which is still supported as a Compatibility Function.

VAR.P

Computes variance, assuming that the arguments represent the total population. Takes the form =VAR.P(number1, number2, …). See Using Sample and Population Statistical Functions on page 607. The old form of this function is VAR, which is still supported as a Compatibility Function.

VARPA

Acts like VAR.P except text and logical values are included in the calculation. See Using Sample and Population Statistical Functions on page 607.

VDB

Calculates depreciation for any complete or partial period, using either double-declining balance or a specified accelerated-depreciation factor. Takes the form =VDB(cost, salvage, life, start_period, end_period, factor, no_switch), where cost is the initial asset cost, salvage is the remaining value after the asset is fully depreciated, life is the length of depreciation time, start_period is the period number after which depreciation begins, end_period is the last period calculated, factor is the rate at which the balance declines, and no_switch turns off the default switch to straight-line depreciation when it becomes greater than the declining balance. See Calculating Depreciation on page 588.

VLOOKUP

Looks for a specified value in the leftmost column in a table, and returns the value in the same row and a specified column. Takes the form =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), where lookup_value is the value to look for; table_array is the range containing the lookup and result values sorted in alphabetical order by the leftmost column; col_index_num is the column number containing the value you want to find; and range_lookup is a logical value, which, if FALSE, forces an exact match. See Using Selected Lookup and Reference Functions on page 555.

WEEKDAY

Returns a number value representing the day of the week for a specified date. Takes the form =WEEKDAY(serial_number, return_type), where serial_number is a date value, a reference, or text in date form enclosed in quotation marks; and return_type determines the way the result is represented (if 1 or omitted, Sunday is day 1; if 2, Monday is day 1; if 3, Monday is day 0). See Working with Date and Time Functions on page 575.

WEEKNUM

Returns a number that indicates where the week falls numerically within a year. Takes the form =WEEKNUM(serial_num, return_type), where serial_num is a date within the week, and return_type is a number that determines the day on which the week begins (1 or omitted = week begins on Sunday, 2 = week begins on Monday).

WEIBULL.DIST

Returns the Weibull distribution. Takes the form =WEIBULL.DIST(x, alpha, beta, cumulative), where x is the value at which to evaluate the function, alpha is a parameter to the distribution, beta is a parameter to the distribution, and cumulative determines the form of the function. The old form of this function is WEIBULL, which is still supported as a Compatibility Function.

WORKDAY

Returns a date that is a specified number of working days before or after a given date. Takes the form =WORKDAY(start_date, days, holidays), where start_date is the date you want to count from; days is the number of workdays before or after the start date, excluding weekends and holidays; and holidays is an array or reference containing any dates you want to exclude. See Working with Specialized Date Functions on page 578.

XIRR

Returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. Takes the form =XIRR(values, dates, guess), where values is a series of cash flows that corresponds to a schedule of payments in dates, dates is a schedule of payment dates that corresponds to the cash flow payments, and guess is a number you think is close to the result.

XNPV

Returns the net present value for a schedule of cash flows that is not necessarily periodic. Takes the form =XNPV(rate, values, dates), where rate is the discount rate to apply to the cash flows, values is a series of cash flows that corresponds to a schedule of payments in dates, and dates is a schedule of payment dates that corresponds to the cash flow payments.

YEAR

Returns the value of the year portion of a serial date/time value. Takes the form =YEAR(serial_number), where serial_number can be a date value, a reference, or text in date format enclosed in quotation marks. See Working with Date and Time Functions on page 575.

YEARFRAC

Returns a decimal number that represents the portion of a year that falls between two given dates. Takes the form =YEARFRAC(start_date, end_date, basis), where start_date and end_date specify the span you want to convert to a decimal, and basis is the type of day count (0 or omitted = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360). See Working with Specialized Date Functions on page 578.

YIELD

Determines the annual yield for a security that pays interest on a periodic basis. Takes the form =YIELD(settlement, maturity, rate, price, redemption, frequency, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, rate is the interest rate of the security at the issue date, price is the security’s price, redemption is the value of the security at redemption, frequency is the number of coupon payments made per year (1 = annual, 2 = semiannual, 4 = quarterly), and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

YIELDDISC

Calculates the annual yield for a discounted security. Takes the form =YIELDDISC(settlement, maturity, price, redemption, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, price is the security’s price, redemption is the value of the security at redemption, and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

YIELDMAT

Calculates the annual yield for a security that pays its interest at maturity. Takes the form =YIELDMAT(settlement, maturity, issue, rate, price, basis), where settlement is the day you pay for the security, maturity is the maturity date of the security, issue is the issue date of the security, rate is the interest rate of the security at the issue date, price is the security’s price, and basis is the day-count basis of the security (if 0 or omitted = 30/360, if 1 = actual/actual, if 2 = actual/360, if 3 = actual/365, if 4 = European 30/360). See Analyzing Securities on page 591.

Z.TEST

Returns the two-tailed P-value of a Z-test (generates a standard score for x with respect to the data set and array, and returns the two-tailed probability for the normal distribution). This function takes the form =Z.TEST(array, x, sigma), where array is the array or range of data against which to test x, x is the value to test, and sigma is the known population’s standard deviation. The old form of this function is ZTEST, which is still supported as a Compatibility Function.