In the simplest terms, IF statements are logic based formula which tests a condition and returns one value for a true statement and another for a false statement. If statements can be nested within each other, meaning that if the first argument isn’t true it will move on to the second argument and so on and so forth until it either finds the criteria matches the condition or defaults to the last ‘false’ value.

There is also the flexibility to create IF statements that must match multiple criteria, or to match one one of many arguments.

This article will explain the fundamentals of the IF statement before moving on to more complex formula with multiple arguments.

Syntax

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

logical_test: the logical test can be any argument put forth such as an equation (e.g. greater or equal to argument) or a text based argument
value_if_true: refers to the value that will be returned if the argument is true
value_if_false: refers to the value that will be returned if the argument is false

Usage

IF statements have a lot of uses, from simple mathematical equations, number counts or looking up data in a table. Below are some arguments that are frequently used and how the IF statement would be written for them.

Nesting IF statements within IF statements

Sometimes there are a series of arguments that you may want to structure, like a decision tree. There is no limit as to how many IF statements you can nest within one another, but it is critical to have them in the correct order.

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

Nesting IF formulas within one another is done by replacing the ‘value_if_false’ argument with another complete IF statement such as the statement above. If the first argument is false, it will test the second argument.

IF statements using AND/ OR

There may be occasions where the value being returned must only occur when multiple arguments must be true simultaneously. In these cases we use the AND function before laying out the logical arguments.

Other times you may choose for only one of a series of argument to be true to return a particular value. These would use the OR argument.

They are nested into the IF statement as below.

• =IF(AND(logical1,[logical2], …),[value_if_true],[value_if_false])
• =IF(OR(logical1,[logical2], …),[value_if_true][,[value_if_false])

The logic statement is an argument which…

Conclusion

IF statements are powerful Excel formula that allows you to return a value based on multiple arguments. The most common uses are for validation of data based on those criteria, and a specific action for true or false results. This can result in creating one formula that can be dragged up, down and across tables of data which are calculation based and apply different calculations based on requirements. An example of this would be a table of monthly salary cost where the employer’s national insurance contribution increases at the start of the new tax year, or a budgeted salary increment on a particular month.