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:

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:

Do you still need help?

We are always available to answer your questions. For the speediest service, please email support@civicscience.com.