Nested If's not working

[Ch1ll1]

Hi All

trying to get this formula to work but it is just not playing ball

=IF(OR(R24="Internal"),IF(Y24<1,"N/A",IF(Y24<$U$3,"Too Low",IF(Y24>$W$3,"Too High",IF(AND(Y24>$U$3,Y24<$W$3),"OK",IF(OR(R24="External"),IF(Y24<1,"N/A",IF(Y24<$U$8,"To Low",IF(Y24>$W$8,"To High",IF(AND(Y24>$U$8,Y24<$W$8),"OKA",""))))))))))

The formula is looking for two things within column R - now depending on what it finds it will calculate Too Low, Too High, OK based on the values in a separate table ($U$3, $W$3 for internal and $U$8, $W$8 for external)

The formula works fine for internal but as soon as it see's external it give a "False"

Any ideas ??

Thanks all

 

[FormR]

The formula works fine for internal but as soon as it see's external it give a "False"

Any ideas ??

Hi, it's not easy to debug, long non-working formula - but the nub of the problem is you effectively say IF R4=Internal then do all the rest, i.e. you never get into the else part of that first IF().

Without digging too deep, is this any better:

=IF(R24="Internal",IF(Y24<1,"N/A",IF(Y24<$U$3,"Too Low",IF(Y24>$W$3,"Too High",IF(AND(Y24>$U$3,Y24<$W$3),"OK")))),IF(R24="External",IF(Y24<1,"N/A",IF(Y24<$U$8,"To Low",IF(Y24>$W$8,"To High",IF(AND(Y24>$U$8,Y24<$W$8),"OKA",""))))))

 

[steve the fish]

I havent looked at the other parts but maybe like this:

=IF(Y24<1,"N/A",IF(R24="Internal",IF(Y24<$U$3,"Too Low",IF(Y24>$W$3,"Too High",IF(AND(Y24>$U$3,Y24<$W$3),"OK",""))),IF(R24="External",IF(Y24<$U$8,"Too Low",IF(Y24>$W$8,"Too High",IF(AND(Y24>$U$8,Y24<$W$8),"OKA",""))))))

 

[GingerStepChild]

Possibly too many IF statements.

When I separate the formula into two different cells and use

=IF(R24="Internal",IF(Y24<1,"N/A",IF(Y24<$U$3,"Too Low",IF(Y24>$W$3,"Too High",IF(AND(Y24>$U$3,Y24<$W$3),"OK")))))

and

=IF(R24="External",IF(Y24<1,"N/A",IF(Y24<$U$8,"To Low",IF(Y24>$W$8,"To High",IF(AND(Y24>$U$8,Y24<$W$8),"OKA","")))))

then it works fine.

If Y24<1 is always the same for internal or external then you could get rid of that easily.

Try this:

=IF(AND(R24="Internal",Y24>1),IF(Y24<$U$3,"Too Low",IF(Y24>$W$3,"Too High",IF(AND(Y24>$U$3,Y24<$W$3,Y24>1),"OK"))),IF(AND(R24="External",Y24>1),IF(Y24<$U$8,"To Low",IF(Y24>$W$8,"To High",IF(AND(Y24>$U$8,Y24<$W$8),"OKA"))),"N/A"))

 

[Ch1ll1]

Thanks everyone - all working now