Would you like to combine multiple functions into one formula or use the IF function with several conditions?
In Excel, to use multiple functions, you can create multiple columns: take the result of one to populate another column. Or it is possible to combine several functions in the same formula.
Indeed, when you write a formula, you can insert a function as an argument (elements of the function): for example, the combination INDEX + MATCH.
Through several different examples, let‘s see how to combine functions in a formula using the IF function.
IF function answers the question If… So… If not.
As a reminder, the IF function looks like this:
=IF(logical_test, [value_if_true], [value_if_false])
Use Multiple Conditions in IF Function Logic Testing
Using the IF function, if you want the logic test to meet several criteria, you can use the AND/OR functions.
=IF(AND(logical1, [logical2]), [value_if_true], [value_if_false])
In this case, the logic test will have to be checked in case 1 AND case 2.
=IF(OR(logical1, [logical2]), [value_if_true], [value_if_false])
In this case, the logic test will have to be checked in case 1 OR case 2.
Multiple nesting IF Function
It is possible to nest several SI functions to link the questions.
By combining several IF functions, we obtain:
=IF(logical_test, [value_if_true], IF(logical_test2, [value_if_true], IF(logical_test3, [value_if_true], [value_if_false])))
1st example: depending on a range of ratings you want to put an appreciation.
| Rating ≥ 16 | Very good |
| Rating ≥ 14 | Good |
| Rating ≥ 10 | Fair |
| Rating < 10 | Insufficient |
Assuming that the grade is in A1 cell, the formula becomes:
=IF(A1>=16, “Very good”, IF(A1>=14, “Good”, IF(A1>=10, “Fair”, “Insufficient”)))
The IF function can be nested up to 64 levels !!!
The IFS function can be an easier to read alternative.
