If condition Help

[gokzee]

Hello,

I have attached a sheet explaining my output requirement which is based on grades of a course.

Please check and suggest

Many thanks

[Fotis1991]

Perhaps..

=IF(H6=MAX(E6;F6;G6;H6;I6);"A";IF(J6<60;"F";IF(AND(J6>=60;J6<=64);"D";IF(AND(J6>=65;J6<=74);"C";IF(AND(J6>=75;J6<=79);"B-";IF(AND(J6>=80;J6<=89);"B";IF(AND(J6>=90;J6<=94);"A-";"A")))))))

[benishiryo]

hi gokzee, you could actually make your IF formula much shorter without using AND, because the IF eliminates parts by parts:
=IF(J6<60,"F",IF(J6<=64,"D",IF(J6<=74,"C",IF(J6<=79,"B-",IF(J6<=89,"B",IF(J6<=94,"A-","A"))))))
that means if J6 is LESS than 60, it will immediately show you "F". it won't go on to do other tests. if J6 is NOT lesser than 60, it will test if it's lesser or equals to 64. and so on.

but you could shorten it even further with a table of the minimum values to get the grade & the grade itself lined up like this. say in Q12:R18
0 F
60 D
65 C
75 B-
80 B
90 A-
95 A
then:
=LOOKUP(J6,$Q$12:$Q$18,$R$12:$R$18)
if you don't want the table, then simply hard code it:
=LOOKUP(J6,{0;60;65;75;80;90;95},{"F";"D";"C";"B-";"B";"A-";"A"})

not sure if this is your requirement. i'm guessing if the value in column H is the max, then the person will get "A-" regardless what he gets for column J?
=IF(H6=MAX($H$6:$H$15),"A-",LOOKUP(J6,$Q$12:$Q$18,$R$12:$R$18))

so the only person affected is Martin?

[gokzee]

hiiii,

i guess u got it wrong...

the highest grade in column H will get "A-".

for ex: if te student gets b and if thats the highest, then final will be "a-"

and if student gets A, then final will be "A" itself.

hope u got it now

[gokzee]

Yess...u got it right now!!!

but if we use max...what happens when the column H actually has high value that corresponds to "A"

[gokzee]

with the present formula, student who is eligible for "A" will also get "A-".

[gokzee]

so we need to put in one more if condition that checks if the grade is less than 95 in column H and the max ..then grade will be "A-"

[benishiryo]

that was what you mentioned in the file?

Thehighest mark in paper will automatically get A- no matter what other grades are.

to help us understand better, type out the desired answers manually. ideally, it should have different scenarios you are expecting. try this
=IF(AND(H6=MAX($H$6:$H$15),J6<$Q$18),"A-",LOOKUP(J6,$Q$12:$Q$18,$R$12:$R$18))

[gokzee]

The formula Works!!

Thanks a lot