Nested IF Function

[Louise]

Hi again!

I have created a nested IF function to calculate student
scores. For example, if they achieve a score of 80+, they
get an A, between 70 and 80 = B etc. Unfortunately, for
some reason, the formula I have created doesn't work
properly. The only scores they are returning are either
A or B but there are clearly some low scores.

Can you please have a look at my IF function below and
tell me where I'm going wrong. This is the first nested
IF Function I have ever created.


=IF(I3>=80,"a",IF(I3<80>70,"B",IF(I3<70>60,"C",IF
(I3<60>40,"D",IF(H3<40,"U")))))

Thanks.

 

[Govind]

Hi Louise,

Use this

=IF(I3>=80,"A",IF(I3>70,"B",IF(I3>60,"C",IF(I3>40,"D","U"))))

The error in your formula is that you can't use such syntax like
I3<80>70. In fact such of check of I3 less than 80 is not required at
all, bcos the first IF checks if I3 is more than 80, returns A if it is
, and moves to the next IF only if I3 is less than 80.So you dont have
do that check again.

In case you want to check more than one condition, then use something
like =IF(AND(I3<80,I3>70),"B","C")

Regards

Govind.

 

[Frank Kabel]

Hi
just as alternative to Govind's solution have a look at VLOOKUP for
this. See:
http://www.mvps.org/dmcritchie/excel/vlookup.htm

 

[Louise]

Hi

Thanks very much, that worked perfectly. Just one more
question that you may be able to help me with, once all
these scores are returned, I will have a column
containing either A, B, C or D in each cell. How can I
ask Excel to work out what the Average grade is? I have
tried the Average function but it won't work with letters?

Thanks.

Louise

 

[Govind]

How can you arrive at an average grade ? Do you mean the grade which
maximum number of students got ?

Regards

Govind.

 

[hgrove]

Louise wrote...

. . . once all these scores are returned, I will have a colum containing either A,
B, C or D in each cell. How can I ask Excel to work out what th Average grade
is? I have tried the Average function but it won't work wit

letters?

So tempting to ask what kinds of grades you managed.

If you have the scores to begin with, DO NOT SCREW AROUND AVERAGIN
GRADE LETTERS, average the underlying scores. Then apply Govind'
formula to the average numeric score