Spreadsheet Formulas - Predefined 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 will replace the position of the function in the expression or spreadsheet formula.

 

Math functions.

 

List of math function arguments:

 

Math Function

Description

Abs(x)

absolute value of x

Arccos(x)

arc cosine of x

Arcsin(x)

arc sine of x

Arctan(x)

arc tangent of x

Cos(x)

cosine of x

hyperbolic cosine of x

Exp(x)

e to the power of x

Hypot(x,y)

returns hypotenuse of x and y (square root(x2 + y2))

Log(x)

natural logarithm of x

Log2(x)

binary logarithm of x

Log10(x)

common logarithm of x

Max(x,y)

returns the greater of x and y

Min(x,y)

returns the lesser of x and y

Rnd(x)

random number in the range of 0 to x

RndNormal(x)

random number from a normal distribution with mu=0 and sigma=x

RndPoisson(x)

random number from a Poisson distribution with parameter x

RoundEven(x)

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.

Sign(x)

sign of x:  if x>0 then +1, if x<0 then -1, if x = 0 remains 0

Sin(x)

sine of x

SinH(x)

hyperbolic sine of x

Sqrt(x)

square root of x

Tan(x)

tangent of x

TanH(x)

hyperbolic tangent of x

Trunc(x)

truncate x to an integer "towards zero"

Uniform(x)

random value in range 0 to x (same as Rnd(x))

INTERPOLATION

  • 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.

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/or ranges and arguments and adjust to missing data:

 

Statistics Functions

Definition

Mean(x1, x2,..xn)

mean of n arguments

Median(x1, x2,..xn)

50th percentile of n arguments

Perc25(x1, x2,..xn)

25th percentile of n arguments

Perc75(x1, x2,..xn)

75th percentile of n arguments

Statmax(x1, x2,..xn)

maximum of n arguments

Statmin(x1, x2,..xn)

minimum of n arguments

Stdev(x1, x2,..xn)

standard deviation of n arguments

Sum(x1, x2,..xn)

sum of n arguments

Validn(x1, x2,..xn)

number non-missing of n arguments

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 will 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.