Originally Posted by
rbrhodes
Beaver,
Here's the IF statement for your first example:
=IF(M2<0.141,"BAD",IF(M2<0.1751,"NP",IF(M2<0.19,"PT",IF(M2<0.2,"BAD",IF(M2< 0.241,"H","BAD")))))
It simply checks each boundary condition in sequence from smallest to largest. In other words if M2 is not less than .14 it checks to see if M2 is less than .1751, then if M2 is less than .19, etc. Any test that answers True puts the answer between the previously tested numbers.
For example: .173 when tested is as follows
Less than .14 = FALSE
Less than .1751 = TRUE
So must be between .141 and .175.
Make sense?
Here's your second query:
=IF(AND(AND(AND(AND(G7>=0.06,G7<=0.1),I7>0.05),J7>0.01),AND(P7>=0.181,P7<=0 .19)),"C","BAD")
and the third:
=IF(AND(AND(AND(AND(G8>=0.06,G8<=0.1),AND(I8>=0,I8<=0.09)),AND(J8>=0.01,J8< =0.06)),AND(P8>=0.181,P8<=0.19)),"NC","BAD")
The secret to building these is to start with the IF, then add the internal AND's, and finally the external AND's.
By internal I mean: G2=>.06 AND G2 <=.10 Actually written as:
AND(G2=>.06,G2 <=.10)
By external I mean: I2>.05 AND J2>.01 Actually written as:
AND(AND(AND(G7>=0.06,G7<=0.1),I7>0.05),J7>0.01)
Still with me?
This could be done with VBA as well but (to me at least) the formulas are easier.