On Wed, 29 Feb 2012 00:59:39 +0000, sumesh56 wrote:


i have to find out grades from the marks of students. i have the
following worksheet.
--------------------------------------------------------------
slno name mk-1 mk-2 mk-3 mk-4 total grade

1 ABC 20 24 22 21 87 A
2 DEF 0 0 0 0 0 ABS
3 EFG 18 20 9 20 67 B
4 JKL 22 9 7 16 54 B
-------------------------------------------------------------------
i tried the following formula
=IF ISNUMBER(G3),IF(G3=75,"A",IF(G3=51,"B",IF(G3=1, "C","ABS"))))
I get the results. of course "ABS" for the second record.

there are records of four students. the second one is absent for the
examinations and he doesnot have any marks. if i put zero in all the
marks colums i get the result as "ABS"
but if i leave the marks columns of the absentee blank can i get the
result as "ABS" ? i tried the following but failed to get it accepted by
excel.
=IF
ISNUMBER(G3),IF(G3=75,"A",IF(G3=51,"B",IF*G3=1 ,"C","ABS")))),"ABS")
Kindly help. thanks and have a nice day.

G3: =SUM(C3:F3)
Fill down.
If the Marks column are left blank, or if they are all zero's, the functionwill still add to 0,

Then try this formula for the grades:

=VLOOKUP(G3,{0,"ABS";1,"C";51,"B";75,"A"},2)