true

Learn Microsoft Excel Training from the Best Tutors

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

Search in

# 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)

Next Lesson Upcoming Soon, On Choose, Ranking !!!

0 Dislike

## Other Lessons for You

Working with Different Versions of VBA
Office 2010 introduces a new version of Microsoft Visual Basic for Applications (VBA) known as VBA 7.0 that is updated to work with 64-bit client installations. VBA 7.0 improves the performance of your...

Today WE LEARN a excel formula like UPPER FUNCTION
Here we are using upper case formula, these formula are used in advanced excel. A B C 1 Hansraj HANSRAJ =upper(TEXT) =UPPER(B1) 2 lakir LAKIR =UPPER(B2) 3 Seema SEEMA =UPPER(B3) 4 Mansi MANSI =UPPER(B4) 5 jyoti JYOTI =UPPER(B5) 6 mahima MAHIMA =UPPER(B6) 7 shashi SHASHI =UPPER(B7) ...

Lookup Reference Formulas
MS Excel Formulas - Lookup Reference Formulas

VBA Tip: Repeating Emp Names N number of times using 4 different loops
Hi All,This is a requirement from one of our student and here we are posting the question along with solution (this can be achieved using 4 different loops). In Column “J” there are few employee...

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,...

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

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