Useful Excel functions

AVEDEV({x}) returns the average deviation of a data set

AVERAGE({x}) returns the mean of a data set

COMBIN(n,x) returns the binomial coefficient

CORREL({x},{y}) returns the product moment correlation coefficient between data sets {x} and {y}.

COVAR({x},{y}) returns the covariance between data sets {x} and {y}.

FREQUENCY({x},{bins}) is an array function that counts the number of data points in the data set {x} that are in each bin. You have to be careful of how the function behaves at the ends of the bin array.

KURT({x}) + 3 returns the kurtosis of a data set

MAX({x}) returns the maximum of a data set

MEDIAN({x}) returns the median of a data set

MIN({x}) returns the minimum of a data set

MULTINOMIAL({s}) returns the multinomial coefficient  

for which the binomial coefficient COMBIN is a special case.

PERCENTILE(array,k) returns the k-th percentile of values in a range. You can use this function to establish a threshold of acceptance. For example, you can decide to examine candidates who score above the 90th percentile.

Rank =RANK(x,array,order) order: 0=descending; 1=ascending

SKEW({x}) returns the skewness of a data set

STDEV({x}) returns the standard deviation of a data set

VAR({x}) returns the variance of a data set

See TREND

INTERCEPT({y},{x}) returns the estimated c

RSQ({y},{x}) returns the square (r2) of Pearson's product moment correlation coefficient r.

SLOPE({y},{x}) returns the estimated m

STEYX({y},{x}) returns the estimated Syx

TREND({y},{x},new-x's,constant) returns an array of y-values corresponding to the new x-values, after attempting a regression fit on {y}, {x}. For example:

The Excel function FORECAST serves the same purpose.

ABS(x) returns the absolute value of x, so:

ABS(-4) = 4, ABS(4) = 4.

AND(condition1, condition2,...) returns a TRUE if al conditions are met, and a false otherwise. For example:

 

A1: 10

A2: 12

A3: 6

 

=AND(A1>=10, A3>2*A1,A2>=2*A3) returns a TRUE

=AND(A1>=10, A3>2*A1,A2>2*A3) returns a FALSE

 

CONCATENATE (text1,text2,...) joins several text strings into one text string.

COUNT({x}) counts the number of cells that contain numbers and also numbers within the list of arguments. Use COUNT to get the number of entries in a number field that's in a range or array of numbers.

COUNTIF({x},criterion) counts how many cells in the range that {x} satisfy the criterion.

EXP(x) returns the natural exponent of x, i.e. ex.

FACT(x) returns the factorial of x, i.e. 1*2*...*x. For example:

FACT(4) = 12

FACT(1) = 1

FACT(0) = 1 That might surprise you - it's a strange result that comes from the gamma function definition of a factorial:

FACT rounds x to the nearest value, but mathematically you can have a factorial of any positive real number. If you want the factorial of say 3.3, you can write:

= EXP(GAMMALN(3.3+1)) = EXP(GAMMALN(4.3))

For any non-negative integer:

FACT(x) = EXP(GAMMALN(x+1))

FALSE( ) returns the logical value FALSE.

GAMMALN(x) returns the log of the gamma function of x. Actually, this IS useful though rather esoteric, as several probability distributions contain a gamma function. It is also an alternative to calculating very large factorial:

= GAMMALN(n+1)

So this function is closely related to Excel's FACT function.

HLOOKUP(v,A,i) looks up in a table delineated by the area A. It searches for the value v in the first row, and then returns the value in the ith row of the same column. For example:

 

This is a useful function in combination with a probability distribution for selecting random or conditional values from tables. However, it can be somewhat tricky in predicting what it will do at the end of tables, and won't work properly unless the first column is put in ascending order. For these reasons we tend to use the functions OFFSET and INDEX for this purpose.

IF(condition,A,B) returns the value A if the condition is true, and the value B otherwise. For example:

 

A1: 10

A2: 12

A3: 6

 

=IF(A1>0,2,5) returns a 2

=IF(OR(A1>10, A3>A1,A2>=2*A3),1,0) returns a 1

=IF(AND(A1>=10, A3>2*A1,A2>2*A3),1,NA()) returns #N/A

This function is extremely useful for creating conditional links between random variables so that a model remains internally consistent no matter what values are being generated from the input distributions.

INDEX(array,r,c) returns the value in the rth row and the cth column of the specified array. Very useful for picking up values from tables of data when r,c or both are random variables. There is also another parameter format for INDEX.

ISNA(ref) checks whether a value is #N/A, and returns TRUE or FALSE.

LN(x) returns the natural log of x, so LN(EXP(x)) = x

LOG(x, base) returns the logarithm of x to the specified base. The base is taken as 10 if the optional parameter base is omitted, so aLOG(x,a) = x.

LOG10(x) returns the logarithm of x to base 10, so 10LOG10(x) = x

NA() returns the error #N/A which is useful for tracking dependent cells, and for excluding certain results from a simulation.

OFFSET(reference, r,c,w,h) returns the value r rows below and c columns to the right of reference. If the optional parameters w and h are used, it becomes an array formula and returns an array staring at the same cell, but extending w cells wide and h cells high. For example:

OR(condition1, condition2,...) returns a TRUE if any of the conditions are met, and a false otherwise. For example:

A1: 10

A2: 12

A3: 6

 

=OR(A1>10, A3>A1,A2>=2*A3) returns a TRUE

=OR(A1>10, A3>A1,A2<2*A3) returns a FALSE

 

PI( ) returns the number 3.14159265358979, the mathematical constant pi, accurate to 15 digits.

ROUND(x,digits) rounds off x to a certain number of decimal places:

 

ROUND(1234.5678,2) = 1234.57

ROUND(1234.5678,0) = 1235

ROUND(1234.5678,-2) = 1200

ROUNDUP(x,digits) rounds a number up, away from 0 (zero).

SIN(x) returns the sine of the given angle.

SQRT(x) returns the square root of x, e.g. SQRT(16) = 4.

SUM({x}) returns the sum of the cells in the array {x}

SUMPRODUCT({x},{y},{z}, ...) multiplies corresponding components in the given arrays, and returns the sum of those products.

TODAY( ) returns the serial number of the current date. The serial number is the date-time code used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.

TRUE( ) returns the logical value TRUE.

VLOOKUP(v,A,i) looks up in a table delineated by the area A. It searches for the value v in the first column, and then returns the value in the ith column of the same row. For example:

 

This is a useful function in combination with a probability distribution for selecting random or conditional values from tables. However, it can be somewhat tricky in predicting what it will do at the end of tables, and won't work properly unless the first column is put in ascending order. For these reasons we tend to use the functions OFFSET and INDEX for this purpose.

YEAR(serial_number) returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.