The IF function allows you to run a true-or-false test and return specific values based on the result. For example, if you’re doing annual employee reviews and whether each employee gets a raise is based on how well they’ve performed in the last year, you could run a yes-no test based on their score from their review.
In this example, each employee’s performance is rated on a scale of 1 to 10. Any employee who scored a 6 or higher will get a raise. Select cell D21, then click on the fx button to get a window of available functions. Select IF and click OK.
The logical test is whether the performance score is equal to or greater than 6. The value if true is yes, and the value if false is no. If you only type in the words, Excel will automatically put in the quotation marks.
Click OK, and copy the formula down for all employees.
You can also nest multiple IF functions in each other. There are a couple downsides to this, such as they are difficult to maintain and troubleshoot, and you need to manually write out the formula for the function, rather than using the IF function wizard shown above. For an example, let’s look at annual performance reviews again, and add the possibility of getting nothing (score 5 or lower), a raise (6-8), or a raise plus a bonus (9-10).
We’ll need to manually write out the formula for the nested IF function. If you look at the second image above, you’ll see the basic formula is =IF(C21>=6,”Yes”,”No”). C21>=6 is the logical test, Yes and No are respectively the values if true or false. For our nested IF function, we’ll modify the logical test, and the value if false will be another IF function.
The new logical test will be if the employee’s score on their review is 5 or less. The value if true will be Nothing. The value if false (i.e. score of 6 or higher) will be another IF function. The second logical test will be if the employee scored 9 or higher. The value if true is Raise and Bonus. The value if false (i.e. score of 6-8) will be Raise.
The formula for this will be =IF(C21<=5,”Nothing”,IF(C21>=9,”Raise and Bonus”,”Raise”)). When we copy it down for all employees, here are the results.