0

I have a sheet contain grade boundaries, For example, the first row says ‘A* | 100 | 90’, the next row is ‘A | 90 | 80 ’, the next ‘B | 80 | 70’ and so on. Another sheet contains a list of student in the first column and the total marks they have achieved in the second column.

Which formulas should I use to turn the students’ total marks into grades?

Improve this question
4
  • Try to look into INDEX and MATCH.
    – Jerry
    Jan 3, 2014 at 16:19
  • Did you keep score of their exact value. The average of an A* student would be different if they made all 90's vs various grades between 90 and 100.
    – Ramhound
    Jan 3, 2014 at 16:19
  • I need to be able to adjust the grade boundaries often so I assume it must reference something. I have try to use 'Vlookup' with the last parameter as 'true', although this did not work correctly.
    – Jack
    Jan 3, 2014 at 18:13
  • There are a ton of resources for excel gradebook if you take a look. One of the first ones offers a free template which might get you started. No reason to reinvent the wheel.
    – CharlieRB
    Jan 3, 2014 at 18:26

1 Answer 1

Reset to default
0

There's probably some "better" solutions doable with VLOOKUP or INDEX/MATCH. But, for a fairly small list like a grading scale, a simple series of nested IFs should do the job just fine.

Here, I started on the outside with "F" and worked my way in to "A". The formula simply works its way up through the grading tiers to see if the student's grade is less than the maximum for each tier. Once it finds the right one, it stops. With this formula, the minimums and the specification of range for "A" are technically unnecessary. It will also automatically adjust the students' letter grades when the maximums are changed (though there may be some erroneous results if you've accidentally left some overlap).

=IF(A2<$F$6,$D$6,IF(A2<$F$5,$D$5,IF(A2<$F$4,$D$4,IF(A2<$F$3,$D$3,$D$2))))

enter image description here

Keep in mind that every reference to the grade being checked should be relative (e.g.: A2) while all references to the grading chart need to be absolute (e.g.: $D$2). This way, the formula will automatically adjust to whichever grade is on the row it's in while always pointing to the right cells it needs to look up the letter value.

If the grading chart is on a separate sheet, the formula is pretty much the same but it looks a bit messier. For each reference out to the grading chart, you'll need to include the sheet name, like this: 'Grading Chart'!$A$2. See the formula below.

=IF(A2<'Grading Chart'!$C$6,'Grading Chart'!$A$6,IF(A2<'Grading Chart'!$C$5,'Grading Chart'!$A$5,IF(A2<'Grading Chart'!$C$4,'Grading Chart'!$A$4,IF(A2<'Grading Chart'!$C$3,'Grading Chart'!$A$3,'Grading Chart'!$A$2))))

An easy way to have Excel help you construct this is if you can start with the grading chart on the same sheet as the grades list. Then, after you've written the formula and got it working, Cut & Paste the grading chart onto the other sheet. Excel will automatically adjust the formula to point to the chart in its new location. This even works if you move the chart to a different file, so long as the one containing the grades list is open when you do so.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .