# Excel - Need (Excel) formula to make Mark sheet with Automatic Grading

Asked By Need Formula for Mark Shee on 20-Sep-08 04:38 AM
```A                B              C              D
SL. #	Subject 	Marks 	Grade
1	English	96
2	History	58
3	Math 	46
4	Chemistry	66
5	Physics	72
6	Urdu	85
7	Oncology	82
8	Biology	91
9	Drawing	57
10	Extra	89
11             HIJ           55
12             ABC         81
13             XYZ         49
14
15
so on        so on

Marks Range	Grade
90+	                  A+
80 TO 89	                  B
70 TO 79	                  C
60 TO 69	                  D
50 TO 59	                  E
40 TO 49	                  F

I want that when I put marks in Marks Columns i.e. C, Grade will be appear
automatically as per above mention range.

Please send me formula to apply. Looking forward for prompt and favorable
response.

Thanks and Regards
Hina```

David Biddulph replied on 20-Sep-08 05:00 AM
```Try the VLOOKUP function.
--
David Biddulph```
Mike replied on 20-Sep-08 05:07 AM
```hI,

Somewhere out of the way build a table that looks like this. In my case it's
in H1 - I7 and note it must remain sorted in the left column

0	F
59	E
69	D
79	C
89	B
90	A+

Then use this formula in D2 to get the grade. Drag down for all grades
=VLOOKUP(C2,\$H\$1:\$I\$6,2,TRUE)

Mike```
stev replied on 21-Sep-08 04:23 AM
```Hi,

I agree VLOOKUP; however, the lookup table should be set as follows:

Mark	Grade
0	Not Graded
40	F
50	E
60	D
70	C
80	B
90	A+

Headers are optional

Alternatively if you want to use a formula the following one can be
placed in D2 and copied down.

=IF(C2>=90,"A
+",IF(C2>=80,"B",IF(C2>=70,"C",IF(C2>=60,"D",IF(C2>=50,"E",IF(C2>=40,"F","Not
Graded"))))))

Cheers```
Teethlessmam replied on 20-Sep-08 09:44 AM
```Try this:

=LOOKUP(C2,{40,50,60,70,80,90},{"F","E","D","C","B","A+"})```
NeedFormulaforMarkShee replied on 21-Sep-08 05:44 AM
```Dear Steve "Cheers"

Many thanks for below formula !!!  It's working ..

=IF(C2>=90,"A+",IF(C2>=80,"B",IF(C2>=70,"C",IF(C2>=60,"D",IF(C2>=50,"E",IF(C2>=40,"F","Not Graded"))))))

But I found some problem in Grading in Vlookup formula ... explain below:

SL. #	Subject	Marks	Grade
1	AA	91	A+
2	AB	79	C
3	AC	81	C	it should be "B" as per range
4	AD	70	D	it should be "C" as per range
5	AE	50	F	it should be "E" as per range

Range of Grade
40-49	F
50-59	E
60-69	D
70-79	C
80-89	B
90-99	A+

Looking forward for your favorable response.

Regards
Hina```
NeedFormulaforMarkShee replied on 21-Sep-08 05:54 AM
```Dear Mike,

I put your given formula but I found some problem in Grading ... explain
below:

Result after putting Formula in Column "D"

A                B              C              D                  E

SL. #	Subject	Marks	Grade
1	AA	91	A+
2	AB	79	C
3	AC	81	C	it should be "B" as per range
4	AD	70	D	it should be "C" as per range
5	AE	50	F	it should be "E" as per range

coulumn---->               H               I
Range of Grade
0	  F
59	  E
69	  D
79	  C
89	  B
90     	  A+

I need Grade as per below mention chart:

Range of Grade
40-49	F
50-59	E
60-69	D
70-79	C
80-89	B
90-99	A+

Looking forward for your favorable response.

Regards
Hina```
NeedFormulaforMarkShee replied on 21-Sep-08 06:00 AM
```Dear Friend,

Many Thanks for reply and below mention formula.... it is working....

Thanks and Regards
Hina```
deepak mathur replied on 21-May-09 03:32 AM
`You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.`
suraj pratap replied on 17-Jun-09 03:44 AM
`You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.`
suraj pratap replied on 17-Jun-09 03:44 AM
`You are required to be a member to post replies.  After logging in or becoming a member, you will be redirected back to this page.`
Amol Jadhav replied to Need Formula for Mark Shee on 01-Jun-10 03:26 AM
```Hi Hina,

I have solution regarding ur problem.

my cell no. 9867439108.```
Amol Jadhav replied to Need Formula for Mark Shee on 01-Jun-10 03:30 AM
```if u want excel solution then please contact me.

E mail - audiologic_23@yahoo.com (9867439108)```
Arjun Kumar Vishwakarma replied to Teethlessmam on 27-Sep-11 06:14 AM
`Goods formula`