Tip: Use multiple criteria with the IF formula function

September 16, 2017

The IF formula function allows you to make logical comparisons when calculating values. You can use multiple criteria, but we’ll first show how to use a single criterion.

Using a single criterion

Let’s say you have two fields, Field1 and Field2, where Field1 is an input field where users enter values and Field2 is an output field which calculates values. To make Field2 multiply the Field1 value by 2 if the Field1 value is less than 10 and multiply it by 4 otherwise, use this formula for Field2:

Technically, the IF formula function takes three parameters (separated by commas in the formula above). The first parameter is a condition, which must evaluate to either TRUE or FALSE, the second parameter is the value to use if the condition is TRUE and the third parameter is the value to use if the condition is FALSE. Field1 < 10 uses the logical operator < (“less than”), so it yields either TRUE or FALSE depending on the value of Field1.

Using multiple criteria

Now, what if you want to combine multiple criteria? Let’s say you want to multiply Field1 by 2 only if it is less than 10 but is also greater than 2.

To combine multiple criteria, Excel users use the logical formula functions AND, OR and NOT (also called boolean functions). AND takes any number of parameters and returns TRUE if all parameters are TRUE and FALSE otherwise. OR returns TRUE if one or several parameters are TRUE and FALSE only if all parameters are FALSE. NOT negates its one parameter, meaning that NOT(TRUE) yields FALSE and NOT(FALSE) yields TRUE. (There’s an additional logical formula function named XOR which you can read more about in the reference sidebar.)

We can use the AND formula function to achieve the goal we set for ourselves earlier, that is, changing the formula so that it only multiplies Field1 by 2 if it is less than 10 but also greater than 2:

To multiply Field1 by 2 only if its value is 3 or 5, use this formula:

To multiply Field1 by 2 only if its value is not 1, use this formula:

(NOT(Field1 = 1) is more naturally written as Field1 <> 1 or Field1 != 1.)

Using logical operators instead of logical functions

Calcapp also supports logical operators that are equivalent to the logical functions AND, OR and NOT. An operator is the symbol that typically appears between two numbers (known as operands), such as the numeric operators + (addition) and / (division). While those two operators yield numbers, logical operators yield TRUE or FALSE. The three operators that are equivalent to the AND, OR and NOT functions are, in order, &&, || and !. && is used instead of AND, and || is used instead of OR. Both are written between their operands. ! is used instead of NOT and is written just before the operand.

This is what the formulas above look like when they are rewritten using operators:

We think that the logical operators read better than the logical functions, so we recommend using them. If you’re used to the traditional spreadsheet way of doing things, though, feel free to continue using the functions. (There’s no operator that is equivalent to the XOR formula function, because it is rarely used.)

Complex rules

You can combine the logical operators or functions to create arbitrarily complex rules. For instance, here’s what you would write if you wanted to multiply Field1 by 2 if Field1 is greater than 2 and is an even number, or if it is 5:

(MOD(Field1, 2) divides Field1 by 2 and returns the remainder. The remainder is zero if the number is even and 1 if it is odd.)

Criteria can get arbitrarily complex. We created an app for a health care company some time ago that needed complex validation logic to flag potentially incorrect values. Here’s a complex formula we associated with the Valid property of the field Sodium:

(VolumeChoice = "Whole bag") &&
((((FirstOutputSupplement > 10) ||
   (SecondOutputSupplement > 5) ||
   ((ThirdOutputSupplement > 10) && (BagVolume = 1000)) ||
   (FourthOutputSupplement > 15)) &&
  (((BagVolume = 775) && (Sodium * 4 + Phosphate * 2 > 32.0)) ||
   ((BagVolume = 1000) && (Sodium * 4 + Phosphate * 2 > 104.9)))) ||
 (((BagVolume = 775) && (Sodium * 4 + Phosphate * 2 > 49.9)) ||
  ((BagVolume = 1000) && (Sodium * 4 + Phosphate * 2 > 104.9))))

This validation rule only applies if the user has selected “Whole bag” from a drop-down field. It references a large number of other fields to determine what rules to use for the Sodium field. With a rule this complex, it’s important that you use parentheses to clearly communicate to Calcapp what your intentions are.

Do you want to share a tip with other Calcapp users through this blog? Let us know!