The Decisive IF Function in Excel

Indecisive?  Have difficulty making decisions?  You need the IF function to do it for you!

Seriously, though, the IF function can take a lot of the drudgery out of your formulas by applying simple yes/no rules.

For example, say you’re calculating the velocity of flow in a water pipeline.  In line with good practice, we want to keep the fluid velocity to not more than 2 m/s.  If the pipeline consists of many pipes, it will be tedious and error-prone to check the velocity in each pipe by hand.

Better have an error message that appears when the velocity exceeds the allowed value.

A simple formula like this will do the trick:

=IF(V>2, “Velocity exceeds 2 m/s!”, “”) where “V” is the named range containing the velocity.

Syntax of IF

=IF(logical_test, [value_if_true], [value_if_false])

Logical_test (required) is a comparison that produces a TRUE or FALSE logical result. A number also works: a blank cell or 0=FALSE and any other number = TRUE.

Value_if_true (optional) is the result to return if logical_test is TRUE.  It can be text (in quotes), a numerical value, or a cell reference.  If omitted, then the function will return 0 if logical_test is TRUE, which is not helpful.

Value_if_false (optional) is the result to return if logical_test is FALSE.  It can be text (in quotes), a numerical value, or a cell reference.  If omitted and logical_test is FALSE, then the function will return 0.   If the preceding comma is also omitted, the function will return FALSE.  Neither of these is useful.

Examples

Note: Using Nested IF Functions

Be careful to use a logical order if you are using nested IFs, as in the last example.

=IF(A13>5, A$11, IF(A13>3, A$12, 0)) works because we look for values exceeding 5 then, if that fails, values exceeding 3.  However, if we swapped the two conditions [IF(A13>3 first, and then IF(A13>5 ], the formula would not work. Values >5 would be trapped by the first IF along with values >3, and the second logical test would never be TRUE.

Note 2: Summary “IF” Functions

The IF function itself operates on a single cell. There are several statistical functions that operate on a range of cells, summarising the data in various ways. Here are some of them.

SUMIF and SUMIFS total only those cells in a range that match a given criterion or criteria.

COUNTIF, COUNTIFS count those cells in a range that match a given criterion or criteria.

AVERAGEIF, AVERAGEIFS average numbers that match a given criterion or criteria.

For the full list and instructions for using them, see the following post.