Scores and multiscore reports use formulas to define their values. The formula editor is the same in both cases and is quite powerful. Many advanced formula features are available.
Constants
The following constants are supported in formulas:
- NA: allows formulas to generate a value of not applicable explicitly. This example demonstrates a score that yields NA for a response total less than 50: if({total} < 50, NA, {a1})
- TRUE
- FALSE
- PI
- E
Comparison and Logical Operators
Several operators are available for comparison and logical testing.
Comparison operators are:
- < less than
- > greater than
- <= less than or equal to
- >= greater than or equal to
- = equals
- <> not equals
Those symbols follow Excel conventions, but == and != may also be used. The equals and not-equals operators have lower precedence than the others. If any operand evaluates to NA, the comparison evaluates to NA. Otherwise, the expression evaluates to 1.0 if the comparison is true and 0.0 if the comparison is false.
The logical operators are:
- ! - logical NOT. Has the same precedence as unary minus negation.
- && - logical AND. Has the second lowest precedence.
- || - logical OR. Has the lowest precedence of all.
An operand is considered logically false if it evaluates to 0.0 or NA. Otherwise, the operand is considered to be true.
Because comparison operators may return any of 1.0, 0.0, or NA, and because both 0.0 and NA are treated as false, there may be ambiguity regarding which false value was tested. Thus, an isna(a) function is provided, which returns true if and only if the expression a evaluates to NA. See Advanced Functions below.
Advanced Functions
Several advanced functions can be used, although they are not available in the Functions dropdown. In general, functions have a case-insensitive name, used like this: function(arg, arg, ...) for zero or more arguments. These functions are:
- if(c, a, b): The if() function evaluates the expression c. If c is true (neither NA nor 0), then the function evaluates and returns a. Otherwise, b is returned.
- ifna(a, b): The ifna() function evaluates expression a. If a is anything other than NA, it is returned as-is. Otherwise, if a is NA, then b is returned instead.
- not(c): Returns true if the expression c evaluates to false or false otherwise.
- isna(a): Returns true if and only if the expression a evaluates to NA.
- and(c1, c2, c3, ...): Returns true only if each and all of the expressions c1, c2, c3, … evaluate as true. Otherwise, it returns false.
- or(c1, c2, c3, ...): Returns true if any of the expressions c1, c2, c3, … evaluate as true. Otherwise, it returns false.
- abs(a): evaluates to a if a>=0, or –1 * a if a < 0
- avg(a1, a2, ...): evaluates as the average of the expressions
- ceil(a): evaluates as the smallest following integer to a
- floor(a): evaluates as the largest preceding integer to a
- ln(a): evaluates as the natural logarithm of a
- log(a, [b=10]): evaluates to the logarithm of a on d. If b is omitted, base 10 is assumed.
- max(a1, a2, ...): evaluates to the largest of a1, a2, …
- min(a1, a2, ...): evaluates to the smallest of a1, a2, …
- round(a): evaluates to the closest integer to a
- sqrt(a): evaluates to the square root of a
- random(): generates a random number between 0 (inclusive) and 1 (exclusive)