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.

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)

Thanks a lot Mr.RR. it works. now can you explain what does the digit 2 at the end of the string means? have a nice day.

On Thu, 1 Mar 2012 00:50:40 +0000, sumesh56 wrote:


'Ron Rosenfeld[_2_ Wrote:
;1499868']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)

Thanks a lot Mr.RR. it works. now can you explain what does the digit 2
at the end of the string means? have a nice day.

Glad to help, thanks for the feedback. As for the "2", and other explanations for how VLOOKUP works, look at HELP for the VLOOKUP function. 2 is the column number. The array constant is the table array, and is constructed as a 4 row, 2 column array. It is equivalent to the table_range referred to by VLOOKUP HELP.


-------------------------------

The VLOOKUP function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

lookup_value Required. The value to search in the first column of the table or range. The lookup_value argument can be a value or a reference. If the value you supply for the lookup_value argument is smaller than the smallest value in the first column of the table_array argument, VLOOKUP returns the #N/A error value.

table_array Required. The range of cells that contains the data. You can use a reference to a range (for example, A2:D8), or a range name. The values in the first column of table_array are the values searched by lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are equivalent.

col_index_num Required. The column number in the table_array argument from which the matching value must be returned. A col_index_num argument of 1 returns the value in the first column in table_array; a col_index_num of 2 returns the value in the second column in table_array, and so on.

thank you very much for the explanation. have a nice day.