Functions List (N-Z)

This is a complete list of all of the current functions available in Excel. Depending upon the version of Excel you are using, some of the functions may not work. Likewise if you are using a Mac version of Excel.

NAME SYNTAX What it does.

N =N(Value)
Converts a value to a number.

NA =NA()
Returns an error value of #N/A.

NEGBINOMDIST =NEGBINOMDIST(number_f,number_s,probability_s)
Returns the negative binomial distribution. NEGBINOMDIST returns 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.

NETWORKDAYS =NETWORKDAYS(start_date,end_date,holidays)
Calculates how many workdays exist between two dates.

NOMINAL =NOMINAL(effect_rate,npery)
Determines the nominal annual interest rate for an investment.

NORMDIST =NORMDIST(x,mean,standard_dev,cumulative)
Returns the normal cumulative distribution for the specified mean and standard deviation.

NORMINV =NORMINV(probability,mean,standard_dev)
Returns the inverse of the normal cumulative distribution for the specified mean and standard deviation.

NORMSDIST =NORMSDIST(z)
Returns the standard normal cumulative distribution function.

NORMSINV =NORMSINV(probability)
Returns the inverse of the standard normal cumulative distribution.

NOT =NOT(Logical)
Returns the reverse value of a condition.

NOW =NOW()
Returns the current date and time.

NPER =NPER(rate, pmt, pv, fv, type)
Used to determine how many payments are needed to reach an investments future value.

NPV =NPV(rate,value1,value2, ...)
Determines the present value for a series of periodic cash flows.

OCT2BIN =OCT2BIN(number,places)
Converts an octal number to binary.

OCT2DEC =OCT2DEC(number)
Converts an octal number to decimal.

OCT2HEX =OCT2HEX(number,places)
Converts an octal number to hexadecimal.

ODD =ODD(number)
Rounds a numeric value to the closest odd integer.

ODDFPRICE =ODDFPRICE(settlement,maturity,issue,first_coupon,rate,yld,redemption, frequency,basis)
Determines the cost of securities that have abnormally long or short first period.

ODDFYIELD =ODDFYIELD(settlement,maturity,issue,first_coupon,rate,pr,redemption, frequency,basis)
Determines the interest of securities that have abnormally long or short first period.

ODDLPRICE =ODDLPRICE(settlement,maturity,last_interest,rate,yld,redemption, frequency,basis)
Determines the cost of securities that have abnormally long or short last period.

ODDLYIELD =ODDLYIELD(settlement,maturity,last_interest,rate,pr,redemption, frequency,basis)
Determines the interest of securities that have abnormally long or short last period.

OFFSET =OFFSET(reference,rows,cols,height,width)
Returns the reference to a cell(s) that is the specified number of rows and columns for the specified cell(s).

OR =OR(logical1,logical2,...)
Compares multiple conditions and returns a value of TRUE if one or more condition is true. If all conditions are false, FALSE is returned.

PEARSON =PEARSON(array1,array2)
Returns the Pearson product moment correlation coefficient, r, a dimensionless index that ranges from -1.0 to 1.0 inclusive and reflects the extent of a linear relationship between two data sets.

PERCENTILE =PERCENTILE(array,k)
Returns the k-th percentile of values in a range.

PERCENTRANK =PERCENTRANK(array,x,significance)
Returns the rank of a value in a data set as a percentage of the data set.

PERMUT =PERMUT(number,number_chosen)
Returns the number of permutations for a given number of objects that can be selected from number objects.

PHONETIC =PHONETIC(reference)
Extracts the phonetic (furigana) characters from a text string.

PI =PI()
Returns Pi.

PMT =PMT(rate,nper,pv,fv,type)
Determines the payments on an investment.

POISSON =POISSON(x,mean,cumulative)
Returns the Poisson distribution. Commonly used in predicting the number of events over a specific time.

POWER =POWER(number,power)
Determines the results of raising a numeric value to a specific power.

PPMT =PPMT(rate,per,nper,pv,fv,type)
Determines that amount of a payment that is applied to the principal for a specified time.

PRICE =PRICE(settlement,maturity,rate,yld,redemption,frequency,basis)
Returns the price per face value of a security that pays periodic interest.

PRICEDISC =PRICEDISC(settlement,maturity,discount,redemption,basis)
Returns the price per face value of a discounted security.

PRICEMAT =PRICEMAT(settlement,maturity,issue,rate,yld,basis)
Determines the cost of a security that pays interest at maturity.

PROB =PROB(x_range,prob_range,lower_limit,upper_limit)
Returns the probability that values in a range are between two limits.

PRODUCT =PRODUCT(number1,number2, ...)
Multiplies a series of numeric values together.

PROPER =PROPER(text)
Capitalizes the first letter in all sentences within a text string.

PV =PV(rate,nper,pmt,fv,type)
Returns the present value of an investment.

QUARTILE =QUARTILE(array,quart)
Returns the quartile of a data set.

QUOTIENT =QUOTIENT(numerator,denominator)
Used to divide one value into another and return only the integer portion of the result.

RADIANS =RADIANS(angle)
Used to convert angles measured in degrees into angles measured in radians.

RAND =RAND()
Generates a random number between 0 and 1.

RANDBETWEEN =RANDBETWEEN(bottom,top)
Used to determine a random integer within a specified range.

RANK =RANK(number,ref,order)
Returns the rank of a number in a list of numbers.

RATE =RATE(nper,pmt,pv,fv,type,guess)
Determines the interest rate of an annuity for a specific time period.

RECEIVED =RECEIVED(settlement,maturity,investment,discount,basis)
Returns the amount received at maturity for a fully invested security.

REGISTER.ID =REGISTER.ID(module_text,procedure,type_text)
Returns the register ID of the specified dynamic link library (DLL) or code resource that has been previously registered.

REPLACE =REPLACE(old_text,start_num,num_chars,new_text)
Used to replace a specified number of characters in a text string with a new text string.

REPLACEB =REPLACEB(old_text,start_num,num_bytes,new_text)
Replaces part of a text string, based on the number of bytes you specify, with a different text string.

REPT =REPT(text,number_times)
Used to repeat a text string a specified number of times.

RIGHT =RIGHT(text,num_chars)
Used to return a specific number of characters from the right side of a cell.

RIGHTB =RIGHTB(text,num_bytes)
Used to return the specific number of bytes that you specify from the right side of a cell.

ROMAN =ROMAN(number,form)
Converts a numeric value to a Roman numeral.

ROUND =ROUND(number,num_digits)
Used to round a numeric value to the specified number of decimal points.

ROUNDDOWN =ROUNDDOWN(number,num_digits)
Rounds a numeric value to a specified number of decimal points by rounding the number down.

ROUNDUP =ROUNDUP(number,num_digits)
Rounds a numeric value to a specified number of decimal points by rounding the number up.

ROW =ROW(reference)
Determines the row number of the specified row.

ROWS =ROWS(array)
Determines the number of rows in a specified array.

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

SEARCH =SEARCH(find_text,within_text,start_num)
Determines the location of a text string using specified criteria.

SEARCHB =SEARCHB(find_text,within_text,start_num)
Finds one text string (find_text) within another text string (within_text), and returns the number of the starting position of find_text.

SECOND =SECOND(serial_number)
Returns the number of seconds in a time value. The value returned will be between 0 and 59.

SERIESSUM =SERIESSUM(x,n,m,coefficients)
Used to calculate the sum of different kinds of series.
X - Numeric value indicating input value for the power series.
N - Numeric value indicating the power you want to raise the value of the X argument to.
M - Numeric value added to N for each successive term.
A - Specifies the array of values X is multiplied by for each term.

SIGN =SIGN(number)
Determines if a numeric value is negative, positive, or zero.

SIN =SIN(number)
Determines the sine of an angle expressed in radians.

SINH =SINH(number)
Determines the hyperbolic sine of a number.

SKEW =SKEW(number1,number2,...)
Returns the skewness of a distribution.

SLN =SLN(cost,salvage,life)
Used to determine the amount of depreciation for an asset during the specified time frame.

SLOPE =SLOPE(known_y's,known_x's)
Returns the slope of the linear regression line through data points in known_y's and known_x's.

SMALL =SMALL(array,k)
Locates the smallest value in an array by the specified value.

SQL.REQUEST =SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text, col_names_logical)
Connects with an external data source, and runs a query from a worksheet.

SQRT =SQRT(number)
Determines the square root of a positive numeric value.

SQRTPI =SQRTPI(number)
Determines the square root of a numeric value multiplied by Pi. 

STANDARDIZE =STANDARDIZE(x,mean,standard_dev)
Returns a normalized value from a distribution characterized by mean and standard_dev.

STDEV =STDEV(number1,number2,...)
Estimates standard deviation based on a sample.

STDEVA =STDEVA(value1,value2,...)
Estimates standard deviation based on a sample.

STDEVP =STDEVP(number1,number2,...)
Calculates standard deviation based on the entire population given as arguments.

STDEVPA =STDEVPA(value1,value2,...)
Calculates standard deviation based on the entire population given as arguments, including text and logical values.

STEYX =STEYX(known_y's,known_x's)
Returns the standard error of the predicted y-value for each x in the regression.

SUBSTITUTE =SUBSTITUTE(text,old_text,new_text,instance_num)
Used to replace specific text within a text string.

SUBTOTAL =SUBTOTAL(function_num,ref1,ref2,...)
Totals a series of values using one of eleven functions.
1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP 
9 - SUM
10 - VAR
11 - VARP

SUM =SUM(number1,number2, ...)
Determines the sum of a series of numeric values.

SUMIF =SUMIF(range,criteria,sum_range)
Returns the sum of a range of cells that meet specified criteria.

SUMPRODUCT =SUMPRODUCT(array1,array2,array3, ...)
Multiplies the corresponding elements of the specified arrays and then sums the results.

SUMSQ =SUMSQ(number1,number2, ...)
Adds the squares of multiple values.

SUMX2MY2 =SUMX2MY2(array_x,array_y)
Returns the sum of the difference of squares of corresponding values in two arrays.

SUMX2PY2 =SUMX2PY2(array_x,array_y)
Returns the sum of the sum of squares of corresponding values in two arrays.

SUMXMY2 =SUMXMY2(array_x,array_y)
Returns the sum of squares of differences of corresponding values in two arrays.

SYD =SYD(cost,salvage,life,per)
Determines the amount of depreciation for an asset during the specified time frame.

T =T(value)
Returns the text referenced by a value. Only used for exporting data to other database types.

TAN =TAN(number)
Determines the tangent of an angle expressed in radians.

TANH =TANH(number)


TBILLEQ =TBILLEQ(settlement,maturity,discount)
Determines the bond equivalent interest earned on a treasury bill.

TBILLPRICE =TBILLPRICE(settlement,maturity,discount)
Determines the value of a treasury bill for every dollars of face value.

TBILLYIELD =TBILLYIELD(settlement,maturity,pr)
Determines the interest earned on a treasury bill.

TDIST =TDIST(x,degrees_freedom,tails)
Returns the Percentage Points (probability) for the Student t-distribution where a numeric value (x) is a calculated value of t for which the Percentage Points are to be computed.

TEXT =TEXT(value,format_text)
Converts a value to text and displays in the specified format.

TIME =TIME(hour,minute,second)
Returns the decimal number for a particular time. The decimal number returned by TIME is a value ranging from 0 to 0.99999999, representing the times from 0:00:00 (12:00:00 A.M.) to 23:59:59 (11:59:59 P.M.).

TIMEVALUE =TIMEVALUE(time_text)
Rarely used. Determines the serial number Excel uses to store a time value as a decimal number.

TINV =TINV(probability,degrees_freedom)
Returns the t-value of the Student's t-distribution as a function of the probability and the degrees of freedom.

TODAY =TODAY()
Returns today’s date.

TRANSPOSE =TRANSPOSE(array)
Transposes the values in a vertical range of cells into a horizontal range or vice versa.

TREND =TREND(known_y's,known_x's,new_x's,const)
Returns values along a linear trend.

TRIM =TRIM(text)
Removes excess spaces from a text string.

TRIMMEAN =TRIMMEAN(array,percent)
Returns the mean of the interior of a data set. TRIMMEAN calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a data set.

TRUE =TRUE()
Returns a value of true.

TRUNC =TRUNC(number,num_digits)
Truncates a number to an integer by removing the fractional part of the number.

TTEST =TTEST(array1,array2,tails,type)
Returns the probability associated with a Student's t-Test.

TYPE =TYPE(Value)
Used to determine the type of value contained in a cell. Number = 1, Text = 2, Logical Value = 4,
Error Value = 16, Array = 64

UPPER =UPPER(text)
Convert a text string to all upper case text.

VALUE =VALUE(text)
Convert a text string of numbers to number format.

VAR =VAR(number1,number2,...)
Estimates variance based on a sample.

VARA =VARA(value1,value2,...)
Estimates variance based on a sample. In addition to numbers, text and logical values such as TRUE and FALSE are included in the calculation.

VARP =VARP(number1,number2,...)
Calculates variance based on the entire population.

VARPA =VARPA(value1,value2,...)
Calculates variance based on the entire population. In addition to numbers, text and logical values such as TRUE and FALSE are included in the calculation.

VDB =VDB(cost,salvage,life,start_period,end_period,factor,no_switch)
Used to determine the amount of depreciation for an asset across multiple periods of time by specifying a start and end time.

VLOOKUP =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Locates a row heading in the first column and then returns the value of the specified cell of that row.

WEEKDAY =WEEKDAY(serial_number,return_type)
Used to determine the day of the week that a specific date occurs.

WEEKNUM =WEEKNUM(serial_num,return_type)
Returns a number that indicates where the week falls numerically within a year.

WEIBULL =WEIBULL(x,alpha,beta,cumulative)
Calculates the probability of a Weibull distribution (used to perform a reliability analysis).

WORKDAY =WORKDAY(start_date,days,holidays)
Determines which date is a specific number of workdays before or after a specific date.

XIRR =XIRR(values,dates,guess)
Used to determine the internal rate of return for a series of cash flows that are not received regularly.

XNPV =XNPV(rate,values,dates)
Used to determine the present value for series of cash flows that are not periodic.

YEAR =YEAR(serial_number)
Returns the year portion of a date.

YEARFRAC =YEARFRAC(start_date,end_date,basis)
Determines a decimal values that represents the fraction of a year represented by the number of days between the specified dates.

YEN =YEN(number,decimals)
Converts a number to text, using the ¥ (yen) currency format, with the number rounded to a specified place.

YIELD =YIELD(settlement,maturity,rate,pr,redemption,frequency,basis)
Calculates the interest earned on a security.

YIELDDISC =YIELDDISC(settlement,maturity,pr,redemption,basis)
Determines the interest earned on a discounted security.

YIELDMAT =YIELDMAT(settlement,maturity,issue,rate,pr,basis)
Calculates the interest earned on a security that pays interest at maturity.

ZTEST =ZTEST(array,x,sigma)
Locates the two-tailed P-value of a Z-test. Primarily used to determine the probability that an observation is drawn from a specific population.

---