IF

Description

The IF function is used to return a certain value if a specified condition is true, and return another value if the condition is false.

Usage

IF(condition, trueValue, falseValue)
IF(condition, trueValue)
Argument Required Expected Type Description
condition Yes Boolean The condition to test
trueValue Yes Any The value to return if the condition is true
falseValue No Any The value to return if the condition is false

If both a trueValue and a falseValue are provided, then they must have the same type.

Result

  • trueValue if the condition evaluates to TRUE.
  • falseValue if the condition evaluates to FALSE.
  • BLANK if the condition is false, and the falseValue is not provided.

Examples

Re-code quantities to a category

We will be using the IF function to evaluate a condition and return different values for a TRUE and FALSE case.

If you have a form with a quantity field with the code AGE, you could use the IF function in a calculated field to break down participants into age groups. For example:

IF(AGE < 18, "Minor", "Adult")

If you add this as a calculated field, the results would look like:

AGE GROUP
8 Minor
32 Adult
6 Minor
60 Adult
18 Adult

The calculated field could be used to count the number of minors and adults in your programme.

Multiple IF function calls can be nested to split a quantity into multiple categories, for example:

IF(AGE < 18, "< 18",
  IF(AGE < 25, "18 - 24",
    IF(AGE < 35, "25 - 34",
      IF(AGE < 45, "35 - 44",
         "45 and up"))))

The formula above would be calculated for each record and might yield the following table:

AGE GROUP
8 < 18
32 25 - 34
6 < 18
60 45 and up
18 18 - 24

Counting participants that meet a specific condition

The IF function can be useful in counting beneficiaries that meet certain criteria. For example, you may need to count the number of girls in your participant registry, you could add the following calculated field to your pivot table:

IF(Gender.Female && Age < 18, 1, 0)

This results in a table with either a one or a zero for each record:

AGE GENDER # Girls
8 Female 1
32 Male 0
6 Male 0
60 Female 0
18 Male 0

When used in a pivot table and the SUM statistic, this will yield the number of girl participants.

Next item
VALUE