In this article, you will get an Excel file to with practice exercises using the nested IF function. There are four exercises for you to solve. The problems are beginner friendly, anyone can solve these with a basic knowledge of Excel. You will need to know the following things to solve the problems: the IF function, the Nested IF, the Fill Handle, 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.
Our dataset has three columns for the first exercise: “Name”, “Score”, and “Comment”. We will slightly change the dataset for four exercises. The first two columns are filled. You will need to use the nested IF function to return comments based on the score. Moreover, the practice exercises are provided on the “Problem” sheet, and the solution to these is on the “Solution” sheet. Additionally, the reference tables are in the “Reference Tables” sheet.
Now, we will walk you through the problem statements for the four exercises.
- Exercise 01 Assigning Comments: Your task is to return relevant comments based on the score. The comment range is as follows: Score more than 80 is outstanding, between 40 and 79 inclusive is good, and below is bad.
- Solution: you can solve this problem in various ways. For example, you can use the VLOOKUP function or combine the INDEX MATCH functions to do so. However, we will use the nested IF function to solve this. Moreover, we will use the Fill Handle to AutoFill the formula to the rest of the cells.
- Exercise 02 Finding Grades: The dataset is similar to the previous exercise, but 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.
- Exercise 03 Finding 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 04 Assigning Comments (Based on Subject): The final exercise is to 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.
Finally, the completed Excel file will look like this.
Thank you for reading this article about nested IF function excel practice exercises. 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!