Problem Overview
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.
Exercise 1 – Assigning Comments
Your task is to return relevant comments based on the score. The comment range is as follows: A score of more than 80 is outstanding, between 40 and 79 inclusive is good, and below is bad.
Solution:
To solve this, we will use the nested IF function and the Fill Handle to AutoFill the formula in the rest of the cells.
Exercise 2 – 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. This image shows the reference data tables for the exercises.
Exercise 3 – Finding Sales Commission
Your task is to calculate the sales commission based on the sales value. The commission for sales between $600,000 and $750,000 is 3%, $750,001 to $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 percentage, so you must apply a custom format to the output cell range.
Exercise 4 – 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. 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. There will be nine IF functions inside the nested IF function.
The completed Excel file will look like this.
Download the Practice Workbook
You can download the Excel file from the following link.
Get FREE Advanced Excel Exercises with Solutions!
