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

N

#### Need Formula for Mark Sheet

A B C D
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

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

M

#### Mike H

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

S

#### steve

Hi,

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

40 F
50 E
60 D
70 C
80 B
90 A+

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

Cheers

T

#### Teethless mama

Try this:

=LOOKUP(C2,{40,50,60,70,80,90},{"F","E","D","C","B","A+"})

N

#### Need Formula for Mark Sheet

Dear Steve "Cheers"

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

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

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

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

N

#### Need Formula for Mark Sheet

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

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
0 F
59 E
69 D
79 C
89 B
90 A+

I need Grade as per below mention chart:

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

N

#### Need Formula for Mark Sheet

Dear Friend,

Many Thanks for reply and below mention formula.... It's working....

Thanks and Regards
Hina

A

#### ashfaquememon01

A B C D
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

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
Ashfaque

R

#### roshanhole

Hi,

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

40 F
50 E
60 D
70 C
80 B
90 A+

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

Cheers

G

#### GS

Not sure why you're reposting a 6-year old reply, but...

My "GradesTable" is laid out as follows...

ScoreAvg 0 50 55 60 65 70 75 80 90
PointAvg 0.00 0.50 1.00 1.50 2.00 2.50 3.00 3.50 4.00
Grade U D D+ C C+ B B+ A A+

...so it occupies the top 3 (hidden) rows only of my grades sheet, and
is defined with a local scope name.

The formula I use for 'PointAvg' is...

...and the formula I use for 'Grade' is...

...where "TotalMark" is a column-absolute, row-relative local scope
defined name range that collects values in a 'Summary' module from all
course outline modules to arrive at a final 'ScoreAvg' for each student
in the class list.

Note that all defined name ranges use local scope so I can have several
class sheets in the same workbook without name conflicts. The class
sheet is inserted from a template and so all 'like' areas use the same
defined names.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

G

#### Gord Dibben

Enter in D2

=LOOKUP(C2,{0,40.1,50.1,60.1,70.1,80.1,90.1},{"Not

Gord

S

#### sabirbarijo

SUM(C4:G4)
H4/500*100
IF(I4>=68,"pass","Fail")
IF(I6>90,"A",IF(I6>80,"B",IF(I6>70,"C","F")))

S

#### shivajivagh

A B C D
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

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

2 40 49 F 59 E =VLOOKUP(E2,\$B\$2:\$D\$7,3,TRUE)
3 50 59 E
4 60 69 D
5 70 79 C
6 80 89 B
7 90 100 A+

P

A B C D
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

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

please give formula one student mark sheet view in one class student

P

Science
Ram 30
Syam 30
Kally 20

English
Ram 40
Syam 50
Kally 30

Maths
Ram 55
Syam 45
Kally 35

See Details on students Marks summery on click

Ram
Science 30
English 40
Maths 55

#### Bibek Shrestha

I just want to ask some questions . I want to make grade sheet. The problem is that, can we measure GPA If the full marks of different subject is not same. For eg. full marks of English 50, science 50, social 40, computer 25, conversation 10.