# Advanced Formulas

Scores and Scorecard Formula Columns use formulas to define their values. The formula editor is the same in both cases, and it is actually very powerful. Many advanced formula features are available. The only thing that changes between them is the data that is available as input – tallies of answers for the Scores and the value of other columns for Scorecards Formula Columns. Here’s how the formula editor looks like for a Score definition:

## Constants

The following constants are supported in formulas:

- NA: allows formulas to explicitly generate the "not applicable" value. The following example demonstrates a score which yields NA for a response total less than 50
- if({total} < 50, NA, {a1})
- TRUE
- FALSE
- PI
- E

## Comparison and Logical Operators

Several operator are available for comparison and logical testing.

Comparison operators are <, >, <=, >=, =, and <>. Those symbols follow Excel convention, but for the programmer types, == and != may also be used. The equals and not-equals operators have lower precedence than the others. If either operand to these operators 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, familiar to programmers, are !, &&, and || -- logical NOT, AND, and OR, respectively. The ! operator has the same precedence as unary minus negation. && has the second lowest precedence, and || the lowest of all. An operand is considered logically false if it is 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 with 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 Formulas below.

## Advanced Functions

Several advanced functions can be used, even if they are not available in the “Functions” drop down. 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 to true. Otherwise, returns false.
- or(c1, c2, c3, ...): Returns true if any of the expressions c1, c2, c3, … evaluate to true. Otherwise, returns false.
- abs(a): evaluates to a if a>=0, or –1 * a if a < 0
- avg(a1, a2, ...): evaluates to the average of the expressions
- ceil(a): evaluates to the smallest following integer to a
- floor(a): evaluates to the largest preceding integer to a
- ln(a): evaluates to the natural logarithm of a
- log(a, [b=10]): evaluates to the logarithm of a on base b. 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).