0Topic: Applying Grading /Ranking in Excel (All versions
Let’s Say you need to rate the Sales with some grade text like POOR, AVERAGE, GOOD and EXCELLENT
Based on a range of values as mentioned below:
0 to 1999 ---Poor
2000 to 2999 –Average
3000 to 3999---Good
4000 and 5000 or greater – Excellent
| Sales value | What is the ranking Using If? | What is the ranking Using Lookup? |
| 0 | Poor | Poor |
| 1999 | Poor | Poor |
| 2000 | Avg | Avg |
| 2999 | Avg | Avg |
| 3000 | Good | Good |
| 3999 | Good | Good |
| 5000 | Excellent | Excellent |
| 5001 | Excellent | Excellent |
We can use 2 commonly used functions in this scenario
- IF (Nested)
- Lookup
Let's Understand the First method using If.
METHOD 1: IF Function (NESTED)
Consider the Logic to be used here in Plain English (pseudo Code)
The Syntax of the IF function in Excel is :
=if(condition,what-action-if-true, what-action-if-false)
To Apply a nested if , using the above syntax and the logic explained by the English Code ,In Excel use :
=if(a3>=5000,"Excellent",if(a3>=3000,"Good",if(A3>=2000,"Avg","poor")))
METHOD 2 : Lookup Function
Create a Tabular form in Excel as a reference for Lookup Function
| F | G |
| 0 | poor |
| 2000 | avg |
| 3000 | good |
| 5000 | excellent |
Let’s says the Value to Lookup Value is in the Cell O2(the Sales Value to be Ranked)
The Lookup Function Syntax
=lookup(Lookup_Value,Lookup_Value_range,lookup_result_range)
Apply the Function Syntax as below
=LOOKUP(A3,F3:F6,G3:G6)
Let's Take Another Example
| Example 2
|
Create a Table for Lookup function to lookup values
Column F Column G
| 0 | Fail |
| 35 | Pass |
| 60 | A |
| 75 | A+ |
| 90 | Distinction |
I have Created A Named Range for frequently used cells by selecting the columns and typing a name in the Name Box and press Enter
| Named Ranges ====> | L_range(For Column F) | R_range(For Column G) |
Consider the The Formula For IF as given below :
=IF(A13>=90,"Distinction",IF(A13>=75,"A+",IF(A13>=60,"A",IF(A13>=35,"pass",
"Fail"))))
Consider the The Formula For lookup as given below :
=LOOKUP(A13,L_range,R_lookup)
So which Function is Simpler or Best to use ? You may say lookup , well actually Depends on the usage
Keep it Simple.
When to use IF
- Use IF, when you need to formulate /evaluate the result, and when the grades are more than 7 – 15, you may have to write many nested ifs (N-1), this makes the formula longer and complex
- You need to use Comparison operators in the test conditions
- Need No order to evaluate, from Larget to smallest or Smallest to Largest Order will do, both orders if will work
When to Use Lookup
Use a Lookup, when you have smaller reference Tables Like we created above, typically 2 dimensional, 1 column to lookup and the other to return a result.
- The Lookup column range must be always sorted in the Ascending order(smaller to larger)
- Also, the Lookup Function, can be used In MACROS (very useful)
- Lookup Function can use Arrays (so when your values are in the form of arrays, use lookup instead of if)
So the above Lookup Formula can also be written as below using an Array syntax (you don’t need to create a Table reference for lookup, in this case)
=LOOKUP(A3,{0,2000,3000,5000}, {"Poor","Average","Good","Excellent"})
Note: The Braces in red are the dataSets (Arrays),1 each for the lookup range and the Result Range
Some Other Functions, to Consider For Ranking /Grading
- CHOOSE ()
- RANK(2007 or older versions)
- EQ(2010 onwards)
Hope you found this Lesson interesting and helpful.I would appreciate your comments and feedback
Next Lesson Upcoming Soon, On Choose, Ranking !!!
Happy Grading !!!
