IF function is used to return a certain value if a specified condition is true, and return another value if the condition is false.
IF(condition, trueValue, falseValue) IF(condition, trueValue)
|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.
trueValueif the condition evaluates to TRUE.
falseValueif the condition evaluates to FALSE.
- BLANK if the condition is false, and the
falseValueis not provided.
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:
The calculated field could be used to count the number of minors and adults in your programme.
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:
|32||25 - 34|
|60||45 and up|
|18||18 - 24|
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:
When used in a pivot table and the
SUM statistic, this will yield the number of girl participants.