# 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 CosH(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)