Excel Practice & Exercises with IF Function

This article contains seven Excel practice exercises related to the IF function. The problems are easy to solve, so you will only need a basic level of Excel knowledge to solve them. To solve all the problems, you should know the following: the IF function, the nested IF, the Fill Handle, structured reference with the IF function, and custom cell formatting. These are available in any version of Excel.

Download Practice Workbook

You can download the Excel file from the following link.

Problem Overview

The first four exercises use the same dataset, which represents order information for a fast-food restaurant. Additional information is provided in the “Reference Tables” sheet, and you can find the solutions in the “Solution” sheet. Relevant hints are given as notes in the file.

• Exercise 1 – Display Status Text: When the order costs more than $5, then a 10% tax will be applied. Use the IF function to display “Including Tax” in the Status column.
  • Solution: Type a condition inside a IF formula, if the value is less than $5 then it will return the text. Otherwise, it will deduct some value, which will be seen in the later exercises.
  • The following image shows the solution to the first problem.

• Exercise 2 – Calculate Tax Amount: When the product costs more than $5, then the company will apply 10% tax. Use the IF function to find the tax amount.
  • Solution: Multiply the price by 10% from $5 and up.
• Exercise 3 – Show Value from a Lookup Table: A small pizza costs less than $7, a medium one costs less than $10, and a large one costs more than $10. Input the pizza size according to these parameters.
  • Solution: You will need to incorporate three IF functions to solve this problem.
• Exercise 4 – Use Structured Reference with IF Function: We refer to a structured reference when we combine table and column names. You will convert the dataset into a table and compare the Date Time and Customer columns to check if the order is new. New orders in this case denote a different time and a different customer.
  • Solution: Convert the dataset into a Table. Then, use ampersand to join the Date Time and Customer name. Finally, compare the combined value with the previous value.
• Exercise 5 – Multiple Criteria with IF function: In this exercise you will find the letter grade based on subjects. Oftentimes, the grades are not fixed, the grades vary with the highest value. The range of the grade per subject is provided on the “Reference Table” sheet.
  • Solution: You will need to use two IF functions for each subject. So, there will be nine IF functions inside the nested IF function.
• Exercise 6 – Find Sales Commission: Your task is to calculate the sales commission based on the sales value. The commission for the sales value between $600,000 and $750,000 is 3%, between $750,001 and $900,000 is 5%, and more than $900,000 is 7%.
  • Solution: You will need to insert 3 IF functions to create a nested IF function to solve this problem. The return values are in percentages, so you will need to apply custom format to the output cell range.
• Exercise 7 – Find Student Grades: You will need to find the letter grades for this problem. 90 or more is graded as A, 75 to 89 as A-, 60 to 74 as B, 45 to 59 as C, 33 to 44 as D, and less than 33 as F.
  • Solution: The solution is similar to the previous solution. Instead of 2 IF functions, you will need to use 5 IF functions. Alternatively, you can use the IFS functions to do so.

The image below depicts the solution to the third problem from the “Solution” sheet.