# Excel Formula for Grading System

1. ## 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:

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  Register To Reply

2. ## Re: Excel Formula for Grading System

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.  Register To Reply

3. ## 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.  Register To Reply

4. ## 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?  Register To Reply

5. ## 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.   Register To Reply

6. ## 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.   Register To Reply