Nested =IF formula help please!

[PhoenixJR]

Hey guys, in a nutshell, I work in a School, and I need excel to return a TRUE statement, if a student is getting an A*-C grade in a particular given subject.

The grades we use here are
A*
A
B
C3
C2
C1
Pass
Merit
Distinction
Distinction*
All other grades would show FALSE.

I thought the easiest way to do this would be to use a Nested IF formula with 10 arguments. I got this far with it, and it was working fine, but as I try and increase the amount of statements that it searches for, I keep returning an error.

=IF(A2="A*","True",IF(A2="A","True"))

One I have all the IF statements working, the plan is to do simple countIF's and easily work out which students are due to get the golden number of exam passes.

If any one can enlighten me on how I can get this working easily, It would be massively appreciated!

[Pete_UK]

So, should Pass, Merit or Distinction also be included as if they were A to C ?

What other grades might you have recorded (i.e. the FALSE grades)?

Pete

[PhoenixJR]

Yeah pass is equivalent to C, and so on upwards.

Failed grades would have to include,
Fail,
U,
D,
E,
F,
and blanks (as not all students do all subjects)

Although leaving the false field blank seems to be returning FALSE at the moment when its not one of the listed statements anyhow.

[PhoenixJR]

hey Pete, thanks for replying so quick, I just been fiddling with it and managed to get it working myself, had completely forgot about using =IF(OR( !!!

Below is what I have at the moment which seems to be working for what I need!!! Thanks for replying though!


=IF(OR(A9="A*", A9="A", A9 ="B", A9="C3", A9="C2", A9="C1", A9="Distinction*", A9="Distinction", A9="Merit", A9="Pass" ),"TRUE")

[Pete_UK]

You can avoid multiple nested-IF statements by putting those pass grades in a column somewhere (eg X1 to X10). Then you could use this formula:

=IF(ISNA(MATCH(A2,$X$1:$X$10,0)),0,1)

which will return 0 or 1 (which can then be SUMmed quite easily), or you can change these to FALSE and TRUE respectively.

Hope this helps.

Pete

[Pete_UK]

By putting TRUE in quotes, you are returning a text value. You can return a proper TRUE or FALSE value like this:

=OR(A9="A*", A9="A", A9 ="B", A9="C3", A9="C2", A9="C1", A9="Distinction*", A9="Distinction", A9="Merit", A9="Pass" )

One drawback in putting the criteria explicitly within the formula is that if the grade descriptions change then you have to amend the formula and then copy it again, whereas if you use a table you only need to amend the table.

Pete

[PhoenixJR]

I see! Makes sense, thanks Pete!

[PhoenixJR]

Will definately save masses of time in the future, infact, next academic year I think they are scrapping C3,C2,C1 in favour of returning to the older C+, C, C-, feature, so I will definately use your table version.


Thanks again Pete, always here to save me! lol

[Pete_UK]

Thanks again Pete, always here to save me! lol

Well, I think this one was a bit easier than last time <bg>

Pete