1.12 Nested IF Statements

You have likely used IF statements in prior assignments, or other courses. Recall that in a basic IF statement, you would need to input three pieces of information. First, you put what the condition is that you are testing to be true or false. The second piece of information in the formula is the value that should be used if the criteria is true, and the third piece of information is the value that should be used if the criteria is false.

Nested IF statements are used when there are more than two options to choose from. For example, in the figure below assume that an employee at Waves, Inc. has recorded the sales price of each item for the week. Notice that Waves sold some surfboards, some paddle boards, and some boogie boards. The sales price of each item was recorded at the time of sale. The sales price differs for each item because of coupons or other promotions that are sometimes used. In order to calculate their total profit for the week, Waves will need to input the cost of each item and then subtract the cost from the sales price to determine the profit. While this could be done in a variety of ways, the Waves employee has decided to use a nested IF statement to automatically fill in the cost for each item.

In this example, the IF statements will be placed in cell F3 in order to return the cost of the first surfboard sold. Notice the format of the IF statement is just like you have seen before with one exception. The second parameter in the IF statement, the value if the condition is false, is another IF statement. In other words, the IF statement says that if cell D3 says "Surfboard" then return the value that is in cell B3 (the cost of a surfboard). If cell D3 is NOT a surfboard, then use the next IF statement. The second IF statement says that if the value in cell D3 says "Paddle Board" then return the value in cell B4 (the cost of a paddle board). If cell B3 is NOT a paddle board, than return the only remaining value which is in cell B5 (the cost of a boogie board). We can ignore the surfboards in this second IF statement, because we already ruled out the surfboards in the first IF statement.

By using this approach of having an IF statement inside of another IF statement (nested IF statements), the employee can now copy the formula down (notice the absolute cell references used) and automate the process of computing the cost of each item. Note that you can nest additional IF statements as needed. For example, if Waves had also sold kite boards, they could have put another IF statement inside of the second IF statement where the $B$5 reference currently is. While this may seem complicated or unnecessary in this setting, nested IF statements are valuable and important tools that can be used to save time in a variety of use cases.