Microsoft Excel to record exam grades?

Question

Now I'm creating an excel worksheet to keep a record of my test/exam results. After converting the results to 100%, I would like to have a column that gives the attained grade.

For e.g,

75% and above would be a grade of 'A1'

70%-74% would be A2

65%-69% would be B3

and so on and so forth, without having to manually enter in the grades.

Can someone help me please? Thanks.

Answer 1

Say the grade 74% is in cell A1:

This formula will look up 74% in the array and return whichever grade matches or the next match lower.  i.e. 74% will match with 0.70, resulting in "A2".  The example below will result in an error if a number lower than 65% is searched for, because there is nothing specified for that range.

=VLOOKUP(A1, {0.65,"B3" ; 0.70,"A2" ; 0.75,"A1"}, 2, TRUE)

Some people prefer to put the lookup table somewhere on the sheet instead of right in the formula.  For example, list the starting percentages in D1:D20 and the corresponding grades in E1:E20, and then you can refer to D1:E20 in the lookup formula:

=VLOOKUP(A1, $D$1:$E$20, 2, TRUE)

In both these cases, the table/array has to be in ascending order in order to enable it to find the closest match.