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. Therefore, you can solve these exercises without any compatibility issues.
Download Practice Workbook
You can download the Excel file from the following link.
Problem Overview
There are seven exercises in this article. The first four exercises use the same dataset. This dataset represents the order information for a fast food restaurant. Additional information is provided in the “Reference Tables” sheet, and the worked out exercises are in the “Solution” sheet. Moreover, relevant hints are given as notes in the file.
- Exercise 01 Display Status Text: When the product is more than $5, then a 10% tax will be applied. Use the IF function to find the 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 anything. 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 02 Calculate Tax Amount: When the product is 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 03 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. Using this table, you will find the pizza size.
- Solution: You will need to incorporate three IF functions to solve this problem.
- Exercise 04 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 return the check if the order is new. New order in this case denotes a different time and a different customer.
- Solution: Firstly, convert the dataset into a Table. Secondly, use ampersand to join the Date Time and Customer name. Finally, compare the combined value with the previous value.
- Exercise 05 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. Therefore, it will be feasible for that scenario. 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 06 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 to $750,000 is 3%, $750,001 to $900,000 is 5%, 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. Now, the return values are in percentage, so, you will need to apply custom format to the output cell range.
- Exercise 07 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. Additionally, this image shows the reference data tables for the exercises.
The image below depicts the solution to the third problem from the “Solution” sheet.
Conclusion
Thank you for reading this article. By completing these IF function Excel practice exercises, you will learn about various ways to think. We hope that you have gained knowledge about Excel. You can find more articles similar to this on the ExcelDemy website. If you have any questions or suggestions, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Keep excelling!
Fantastic work! thank you
Dear Kapil,
Thanks for your appreciation.
Regards
ExcelDemy
where can I find the answers to the questions from this exercise?
Dear Sara,
You will find the solutions in the Excel file which is given in Download Practice Workbook section.
Regards
ExcelDemy