Spreadsheet Formulas - Math and Statistical Functions

When you can use predefined Math and Statistical functions in expressions:

  • Recoding
  • Case selection  
  • Data verification
  • Calculating variable values in spreadsheet formulas

Functions are not case sensitive. Example: Log(x) is the same as log(x) or LOG(x).

The return value of the function replaces the position of the function in the expression or spreadsheet formula.

Math functions

List of math function arguments:

Math Function Description  
Abs absolute value of x ATAN2(y, x)
Arccos arc cosine of x ArcCos(x)
ArcSin arc sine of x ArcSin(x)
ArcTan arc tangent of x ArcTan(x)
ATAN2 Calculates the angle in radians, in (-pi, pi], to the given point ATAN2(y, x)
Ceiling Returns the smallest integer not less than x  Ceiling(x)
Combin Returns the number of combinations of n things taken k at a time Combin(n,k)
Cos cosine of x Cos(x)
CosH hyperbolic cosine of x CosH(x)
DMSToDecimal  Converts angles in degrees, minutes, and seconds to decimal degrees DMSToDecimal(degrees, minutes, seconds)
Euler Value of e=2.71828182845905 Euler
Exp e to the power of x Exp(x)
Fact Returns n! Fact(n)
Floor Returns the largest integer not greater than x Floor(x)
gammln Returns the log of the gamma function (generalized factorial) Function gammln(x As Double) As Double
Haversine Calculates the distance between two points, specified with latitude/longitude. If units=1, returns kilometers; with 2, statute miles; with 3, nautical miles Haversine(lat1, lon1, lat2, lon2 [, units])
Hypot returns hypotenuse of x and y (square root(x2 + y2)) Hypot(x,y)
Log natural logarithm of x Log(x)
Log10 common logarithm of x Log10(x)
Log2 binary logarithm of x Log2(x)
Max returns the greater of x and y.See statmax for variable lists. Max(x,y)
Min returns the lesser of x and y. See statmin for variable lists Min(x,y)
NthMax Returns the nth largest of the supplied arguments  NthMax(n, v1, v2, ...)
NthMin Returns the nth smallest of the supplied arguments   NthMin(n, v1, v2, ...)
Permut Returns the number of permutations of n things taken k at a time Permut(n,k)
Pi Value of Pi=3.14159265358979 Pi
Rnd random number in the range of 0 to x Rnd(x)
RndNormal random number from a normal distribution with mu=0 and sigma=x RndNormal(x)
RndPoisson random number from a Poisson distribution with parameter x RndPoisson(x)
Round Returns the number x rounded to n decimal places. Use negative n to round to nearest 10, 100, etc.  Round(x, n)
RoundEven returns the number x rounded to n decimal places, breaking ties by rounding half to even (Banker's Rounding.) Use negative n to round to nearest 10, 100, etc. RoundEven(x)
Sign sign of x:  if x>0 then +1, if x<0 then -1, if x = 0 remains 0 Sign(x)
Sin sine of x Sin(x)
SinH hyperbolic sine of x SinH(x)
Sqrt square root of x Sqrt(x)
Tan tangent of x Tan(x)
TanH hyperbolic tangent of x TanH(x)
  truncate x to an integer "towards zero" Trunc(x)
  random value in range 0 to x (same as Rnd(x)) Uniform(x)
 
  • Interpolates an arbitrary point X located between X1 and X2, using the linear equation Y = A + B*X, where A and B are the coefficients of the linear function which can be estimated from (X1, Y1) and (X2, Y2).
  • Function call requires 5 parameters:
  • Y = INTERPOLATION (X1, X2, Y1, Y2, X)
  • Where X is the point where we want to estimate Y, given the linear equation above and the function arguments (X1, Y1) and (X2, Y2). Note that X must fall into the range (X1, X2). The returned value of the function is the estimated value of Y.
INTERPOLATION
STEPFUNCTION
  • Estimates the value of the step function Y at an arbitrary point X.
  • Function call requires 3 parameters:
  • Y = STEPFUNCTION([X1], [Y1], X)
  • Where [X1] is an array of X values, [Y] is an array of Y values (both have arbitrary but same number of dimensions). X is the point where Y is to be estimated, given the function arguments [X1] and [Y1]. The returned value of the function is the estimated value of Y.
 

Math functions can accept either one to two arguments, depending on the function.

When using Math functions, if the value of any variable you use in the expression or formula is missing (in the current case), then the expression evaluates to missing data (for the current case).

Acceptable arguments:

Math Functional Arguments Example
Numeric values. Sqrt(155)
Variable names Max(SCORE1,SCORE9)
Variable numbers Log(v8)
Expressions that evaluate to a number Max(v7,(v5+v8-BASELINEVALUE)/3)
Functions that return a numeric result Sin(Sqrt(v5)
Some commonly used constants, specified in expressions and formulas by reference. Pi = 3.14... Euler (e) = 2.71...

Statistics functions

These nine statistics functions can accept lists of values and ranges and arguments and adjust to missing data:

Statistics Functions Definition
mean Mean(x1, x2,..xn) mean of n arguments

Returns mean for specified variable range and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names

median Median(x1, x2,..xn) 50th percentile of n arguments

 Returns median for specified variable range and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names.

perc25 Perc25(x1, x2,..xn) 25th percentile of n arguments

Returns 25th percentile (lower quartile) for specified variable range and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names.

perc75 Perc75(x1, x2,..xn) 75th percentile of n arguments

Returns 75th percentile (upper quartile) for specified variable range and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names.

statmax Statmax(x1, x2,..xn) maximum of n arguments

Returns maximum value among the specified arguments and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names.

statmin Statmin(x1, x2,..xn) minimum of n arguments

 Returns minimum value among the specified arguments and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names.

stdev Stdev(x1, x2,..xn) standard deviation of n arguments

  Returns standard deviation for specified variable range and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names

sum Sum(x1, x2,..xn) sum of n arguments

Returns sum for specified variable range.

  Variables in argument can be referenced by vXXX convention or by names.

validn Validn(x1, x2,..xn) number non-missing of n arguments

Returns number of valid cases for specified variable range and/or list (e.g., v1:v3, AGE, sqrt(v9+100),v11:v15, will include 10 elements).

  Variables in argument can be referenced by vXXX convention or by names

VARMAX   VARMAX(varref) Returns the max of the specified variable/column
VARMEAN VARMEAN(varref) Returns the mean of the specified variable/column
VARMIN VARMIN(varref) Returns the min of the specified variable/column
VARSTDDEV   VARSTDDEV(varref) Returns the std. dev. of the specified variable/column
VARSUM  VARSUM(varref) Returns the sum of the specified variable/column
VARVALIDN VARVALIDN(varref) Returns the valid N of the specified variable/column
Note: The stat prefix in the Statmin and Statmax functions distinguishes them from the arithmetical Max and Min (math) functions discussed above. Like math functions, the names of statistics functions are not case sensitive when used in expressions and formulas.

Ways statistic functions differ from math functions:

  • They ignore arguments that contain missing values. They base their results on non-missing data only.
  • Each statistics function accepts any number of arguments placed in parentheses and separated by commas.
  • Statistics functions do not evaluate to a missing result except in the following circumstances:

    All arguments are missing values.

    An argument is encountered that evaluates to an undefined value. Example: Square root of a negative number or division by zero.

List of acceptable statistics function arguments:

Statistical Function Arguments Example
Numbers Mean(18,80,120,68,40)
Variable names Mean(SALARY,1050,BONUS,500)
Variable numbers Stdev(120,v3,v2,v8,255)
Ranges of variables designated by name or number (use colons to define ranges) Sum(v54,COST1:COST5,2550,v23:v35,1575,OVERHEAD)
Expressions that evaluate to a number Mean(500,(v6+456)/v3),TRAVELEXPENSE,v8)
Functions that return a numeric result Mean(220,Sqrt(v8+v7),v12)

See also: Distributions and their Functions, Spreadsheet Formulas - Overview, Spreadsheet Formulas - Syntax Summary, Spreadsheet Formulas - Examples, Spreadsheet Formulas - Syntax Operators, Spreadsheet Formulas - Distributions and their Functions.