Hello there,

I learnt vlookup formula for calculating GPA from simple grading rules. Below is the screenshot on vlookup in action with simple grading rules.

GPA1.png

But now I have new grading rules in which the percentage and numerical values for grades are given in a range form, not in number. Here are the new grading rules that I have to implement:

Percentage Grade Value
0 - 49.99 F 0.0
50 - 54.99 D 1.00 - 1.49
55 - 59.99 D+ 1.50 - 1.99
60 - 64.99 C 2.00 - 2.49
65 - 69.99 C+ 2.50 - 2.99
70 - 74.99 B 3.00 - 3.49
75 - 79.99 B+ 3.50 - 3.99
80 - 89.99 A 4.00
90 - 100 A+ 4.00


I have no ideas how to solve this. Will only an IFS formula will solve this? But it will become very complex, isn't it? I cannot use SWITCH as it us used for exact matches.
Any help will be appreciated.

Kind Regards.