UrbanPro
true

Learn Microsoft Excel Training from the Best Tutors

  • Affordable fees
  • 1-1 or Group class
  • Flexible Timings
  • Verified Tutors

Create a Grading in Excel using Formulas (All versions)

Bhavna Philipose
23/03/2017 0 0

Topic: 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)

Pseudo Code, for N text values of gradings always N-1 if's
 
if (salesvalue(a3-cell) >= 5000 then
  result = " Excellent"  - Value if true (fo this if)
else
if (salesvalue(a3-cell) >= 3000 then  value if false(for prev if)
result = "Good"   value if true (for this If)
else    
if (salesvalue(a3-cell) >= 2000 then  value if false(for prev if)
result = "Average" -value if true (for this if)
else         Default Case
result = "Poor"
End )))

 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

 

Scores

Grade with IF

Grade With Lookup

0

Fail

Fail

34

Fail

Fail

35

pass

Pass

59

pass

Pass

60

A

A

74

A

A

75

A+

A+

90

Distinction

Distinction

91

Distinction

Distinction

   

 

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 !!!
 

0 Dislike
Follow 0

Please Enter a comment

Submit

Other Lessons for You


Pivot table - Transform numbers in calculated field
How to convert the absolute numbers in pivot table into Lakhs, Millions, Trillions without adding a spare column in the data source. Name the field in the Calculated field and apply the formula by Double-clicking...

Phanitha P. | 29/01/2022

0 0
0

Excel Conditional Formatting
In this video, learn about Conditional Formatting, Creating Conditional Formatting Rules, Use Conditional Formatting Presets, Managing & Removing Conditional Formatting.

Mukesh Chopra | 06/07/2021

0 0
0

Advanced Excel and VBA concept
In this video, learn about Advanced Excel: - Data Input and Hidden Features, - Worksheet Navigation, - Excel Formulas & Functions, - Data Cleaning and Formatting. VBA and its useful Built-in Functions,...

Nagadarshan | 06/07/2021

0 0
0

Vlookup in excel
Vlookup is a very useful formula in excel. Vlookup can be used in two ways Exact and approximate. We can find and fill in data with the help of Vlookup. The syntax of Vlookup is: Vlookup (Lookupvalue,...

Shilpa Jain | 25/03/2021

5 0
1

Looking for Microsoft Excel Training classes?

Learn from Best Tutors on UrbanPro.

Are you a Tutor or Training Institute?

Join UrbanPro Today to find students near you
Sponsored
X

Looking for Microsoft Excel Training Classes?

The best tutors for Microsoft Excel Training Classes are on UrbanPro

  • Select the best Tutor
  • Book & Attend a Free Demo
  • Pay and start Learning

Learn Microsoft Excel Training with the Best Tutors

The best Tutors for Microsoft Excel Training Classes are on UrbanPro

Book a Free Demo

This website uses cookies

We use cookies to improve user experience. Choose what cookies you allow us to use. You can read more about our Cookie Policy in our Privacy Policy

UrbanPro.com is India's largest network of most trusted tutors and institutes. Over 55 lakh students rely on UrbanPro.com, to fulfill their learning requirements across 1,000+ categories. Using UrbanPro.com, parents, and students can compare multiple Tutors and Institutes and choose the one that best suits their requirements. More than 7.5 lakh verified Tutors and Institutes are helping millions of students every day and growing their tutoring business on UrbanPro.com. Whether you are looking for a tutor to learn mathematics, a German language trainer to brush up your German language skills or an institute to upgrade your IT skills, we have got the best selection of Tutors and Training Institutes for you. Read more