+ Reply to Thread
Results 1 to 6 of 6

Excel Formula for Grading System

  1. #1
    Registered User
    Join Date
    09-13-2018
    Location
    Philippines
    MS-Off Ver
    2021
    Posts
    30

    Excel Formula for Grading System

    Good day!

    I want to ask for help with the formula on INDEX.

    This is my main sheet, named Gradebook.

    Located on my TG Column on Grade:

    =INDEX(Grade!$C$6:$C$30,MATCH(AH11,Grade!$B$6:$B$30,1))

    Capture.JPGCapture.JPG

    However, this formula can't show the matching grade needed. Regardless of the value, it only shows 3.0.

    Here is my sheet for Grade:

    Capture1.JPG

    Please help me, I'm puzzled on this. Thank you!

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel Formula for Grading System

    try changing this =INDEX(Grade!$C$6:$C$30,MATCH(AH11,Grade!$B$6:$B$30,1))
    to 0 like this... =INDEX(Grade!$C$6:$C$30,MATCH(AH11,Grade!$B$6:$B$30,0))

    EDIT: the use of 1 at the end means that the formula looks for an approximate match and the "table" you are referencing needs to be in ascending order for that to work while yours is in descending order. 0 means the formula looks for an exact match and the order for the values doesn't matter. (this is the same as in lookups like lookup, vlookup and hlookup.) A 1 would work if for example you have a grade of 99.8 and you are looking in your scale for that and it would fall between 99 and 100. But the minimum column and grade column would need to be changed into descending order. Hope that helps.
    Last edited by Sam Capricci; 11-29-2018 at 08:23 AM.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Registered User
    Join Date
    09-13-2018
    Location
    Philippines
    MS-Off Ver
    2021
    Posts
    30

    Re: Excel Formula for Grading System

    Capture1.JPG

    I added another column for the Rounding Off of the grade sir. But that is the result.

  4. #4
    Registered User
    Join Date
    09-13-2018
    Location
    Philippines
    MS-Off Ver
    2021
    Posts
    30

    Re: Excel Formula for Grading System

    I see now sir, the value is 98.35 which on my Grade Sheet, don't have. How can I round off the number to make it 98? So that the formula can find a match?

  5. #5
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel Formula for Grading System

    See my edited comment in post 2 and see if that helps clarify because you changed your data in AG from 100 to 98.35, that means you have to use the 1 at the end of the index/match formula BUT you need to resort your grade table to ascending order for column B. So the 100 should be at the bottom of the table.
    let me know if this doesn't correct things.

  6. #6
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Excel Formula for Grading System

    rounding the number (your post 4) won't matter (depending on how you are rounding it) if you re-sort the data in the grade table and you will HAVE to use the 1 at the end so it will get the approximate match.
    Last edited by Sam Capricci; 11-29-2018 at 08:47 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert percentage into a letter grading system
    By thursday140 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-13-2016, 08:08 AM
  2. Excel Formula for grading employee
    By titanus1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-10-2014, 01:24 AM
  3. Grading system for teachers
    By lasso88 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-13-2013, 05:42 AM
  4. Grading Formula
    By Nomnol2 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-27-2013, 03:37 PM
  5. Student's grading system
    By imranlah in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-19-2010, 06:04 AM
  6. grading system conditional formatting
    By Gratitude in forum Excel General
    Replies: 7
    Last Post: 04-17-2010, 10:39 AM
  7. How do I create a grading system in Office Excel?
    By Miss Dunkley in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-29-2005, 11:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1