GPA calculation with grading rules given in range

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.

Originally Posted by AliGW

Welcome to the forum.

Your screenshot looks newer than Excel 2007, so please update your profile.

There are instructions in the yellow banner at the top telling you how to attach your workbook.

Thanks AliGW.

I am editing this file at home where I have libreoffice. At the workplace, I have MS-Excel. Anyhow file has been attached as directed..

Originally Posted by wk9128

Cell C2 formula , Drag down and across

HTML Code: 

Another option

HTML Code: 

Dear wk9128, let me rephrase my query. The file GPA-Calculation.xlsx‎ (Attached in post#3) is working well with simple grade rules given in table range $A$17:$C$21. What I have to do is change the rules as given in the table in post#1 and then apply vlookup or whatever.

Originally Posted by josephteh

With the new grading rules laid out in a table H4:M12, GPA value in cell F4 =VLOOKUP(B4,$H$4:$M$12,4)+VLOOKUP(B4,$H$4:$M$12,6)*(B4-VLOOKUP(B4,$H$4:$H$12,1))

Attachment 738932

Sometimes words simply fail to convey the true and complete meanings.

. However, let me say I am very much thankful to you Sir.

... however, the story doesn't end here!

As per institute's convention, the numerical values for grades is to be shown with only one decimal point. So I did appropriate changes in column F (Number format > one decimal point). And I noticed that if someone got 49 marks, its alphabetic grade is F (fail) yet its numerical grade is 1.0. Obviously, this is because the value 0.9704 is rounded off to 1.0 when I changed its number format from 4 decimal points to 1 decimal point.

However, only a little tweak resolved this. Deleting

Formula:

Please Login or Register  to view this content.

from Column M4 and changing its value to 0.0.

... however, the story doesn't end here, too !

As per institute's convention again, the numerical values for grades 1,2,3, and 4 is to be shown with no decimal points. May we refine this?
The file is attached again.

Kind Regards.

As suggested by Ali, ROUNDDOWN to 1 decimal place and change cell formatting to General.