IF
An IF statement returns a value depending on whether the condition that is being evaluated is TRUE or FALSE.
Syntax
IF(logical_test, value_if_true, value_if_false)
Arguments
Arguments | Data Type | Condition |
---|---|---|
logical_test | Text / Number | The condition to be evaluated. |
value_if_true | Node reference / Number | The value to be returned if the condition is met. |
value_if_false | Node reference / Number | The value to be returned if the condition is not met. |
Return Value
Either value_if_true or value_if_false.
Remarks
Returns Blank when the formula is misspelled.
The specified node should contain Numerical data.
The IF function returns error in any of the following cases:
value_if_false argument is missing.
The argument value_if_true or value_if_false are not of numeric data type.
Not using appropriate operator in logical_test.
Example
Consider the Revenue and Cost of Goods Sold nodes to follow the trend as shown below.
Parameter | Jan | Feb | Mar | Apr | May | Jun |
---|---|---|---|---|---|---|
Revenue | 100 | 124 | 137 | 0 | 23 | 45 |
Cost of Goods Sold | 25 | 25 | 25 | 25 | 25 | 25 |
Now, a new node Gross Profit Margin can be computed using the IF function. Gross Profit Margin is calculated as (Revenue-Cost of Goods Sold)/Revenue. In case Revenue has period value of 0, Gross Profit Margin should return 0.
To achieve this, the following formula should be written in the [Gross Profit Margin] node:
IF
(
[Revenue]
==0,0%,(
[Revenue]
-
[Cost of Goods Sold]
)/
[Revenue]
)
Result
Parameter | Jan | Feb | Mar | Apr | May | Jun |
---|---|---|---|---|---|---|
Gross Profit Margin | 75% | 80% | 82% | 0% | -9% | 44% |
As you can see from the above table, if the logical test condition (Revenue==0) is True, it returns 0, i.e. the gross profit margin is 0 and if the logical test condition is False (Revenue is a non-zero number), it calculates the Gross Profit Margin from the formula and returns it.
Use SWITCH function instead of nesting multiple IF functions.
Last updated