how to find out the grade from marks-functions

[sumesh56]

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

1 ABC 20 24 22 21 87
2 DEF 0 0 0 0 0 AB
3 EFG 18 20 9 20 67
4 JKL 22 9 7 16 54
------------------------------------------------------------------
i tried the following formul
=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 th
examinations and he doesnot have any marks. if i put zero in all th
marks colums i get the result as "ABS
but if i leave the marks columns of the absentee blank can i get th
result as "ABS" ? i tried the following but failed to get it accepted b
excel
=I
ISNUMBER(G3),IF(G3>=75,"A",IF(G3>=51,"B",IF*G3>=1,"C","ABS")))),"ABS"
Kindly help. thanks and have a nice day

 

[Ron Rosenfeld]

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)

 

[sumesh56]

;1499868']On Wed, 29 Feb 2012 00:59:39 +0000, sumesh5

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 accepte 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, th
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
at the end of the string means? have a nice day

 

[Ron Rosenfeld]

;1499868']On Wed, 29 Feb 2012 00:59:39 +0000, sumesh56

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, A28), 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.

 

[sumesh56]

;1508028']On Thu, 1 Mar 2012 00:50:40 +0000, sumesh5

'Ron Rosenfeld[_2_ Wrote: -

;1499868']On Wed, 29 Feb 2012 00:59:39 +0000, sumesh56
--

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 fo the
examinations and he doesnot have any marks. if i put zero in al the
marks colums i get the result as "ABS"
but if i leave the marks columns of the absentee blank can i ge the
result as "ABS" ? i tried the following but failed to get i 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 digi 2
at the end of the string means? have a nice day.-

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


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

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

lookup_value Required. The value to search in the first column of th
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 tha
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. Yo
can use a reference to a range (for example, A28), or a range name
The values in the first column of table_array are the values searched b
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 argumen
from which the matching value must be returned. A col_index_num argumen
of 1 returns the value in the first column in table_array;
col_index_num of 2 returns the value in the second column i
table_array, and so on.

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

 

[CNWatsonJr]

Does C3 have a value or a formula in it? If it is a value, click on C
and then press CNTRL-C (if you are on a PC or Command-C on a Mac) an
then click on C4, C5 and C6 (by either holding the Shift or Control ke
down) and then press CNTRL-V (or Command-V on a Mac)

If C3 has a formula and you haven't locked the cell reference with
($C$3) then the cells you copy into will self adjust the formula. If C
is a formula and you want to copy the value, then press CNTRL-C and the
highlight the cells you want to copy into, right-click and then choos
Paste Special / Values

You can also put the formula =C3 in the cells C4, C5 and C6 and tha
will place the data that is in C3 in the selected cells

Hope this helps you some

 

[sumesh56]

Does C3 have a value or a formula in it? If it is a value, click on C
and then press CNTRL-C (if you are on a PC or Command-C on a Mac) an
then click on C4, C5 and C6 (by either holding the Shift or Control ke
down) and then press CNTRL-V (or Command-V on a Mac).

If C3 has a formula and you haven't locked the cell reference with
($C$3) then the cells you copy into will self adjust the formula. If C
is a formula and you want to copy the value, then press CNTRL-C and the
highlight the cells you want to copy into, right-click and then choos
Paste Special / Values.

You can also put the formula =C3 in the cells C4, C5 and C6 and tha
will place the data that is in C3 in the selected cells.

Hope this helps you some.

sorry, i want the data which i am going to type on c3 shoul
automatically come to c4,c5,and c6 cells. for that i have to format th
said cells. how can it be accomplished

 

[Gord Dibben]

You missed this part..............

In C4 enter =$C$3

Copy to C5 and C6


Gord