If statement help **result showing #value!

[andycreber]

Hi

I'm tying to do this Nested IF statement and I get the error #VALUE ! which I cannot find out why, please could someone help me.

Formula

=IF(AND(R5>=0.05,R5<=0.0699),-40),IF(AND(R5>=0.07,R5<=0.09),0),IF(AND(R5>=0.0901,R5<=0.012),60),IF(AND(R5>=12.01,R5<=15),120,0)

I want my my cell in R5 to by shown as a percentage, hence the weird ranges.

Many thanks in advance

 

[shaowu459]

=IF(AND(R5>=0.05,R5<=0.0699),-40,IF(AND(R5>=0.07,R5<=0.09),0,IF(AND(R5>=0.0901,R5<=0.012),60,IF(AND(R5>=12.01,R5<=15),120,0))))

Please check the () in the formula.

 

[shaowu459]

The second IF should be the third parameter of the first IF. Like IF(AND(),-40,IF()).

you formula =IF(AND(R5>=0.05,R5<=0.0699),-40) , IF(AND(R5>=0.07,R5<=0.09),0) , IF(AND(R5>=0.0901,R5<=0.012),60) , IF(AND(R5>=12.01,R5<=15),120,0) is 4 IF conected with ",".

 

[andycreber]

Thanks for the reply, I used your first example and only the first criteria range works, not the rest, any ideas???

much appreciated

 

[andycreber]

The second IF should be the third parameter of the first IF. Like IF(AND(),-40,IF()).

you formula =IF(AND(R5>=0.05,R5<=0.0699),-40) , IF(AND(R5>=0.07,R5<=0.09),0) , IF(AND(R5>=0.0901,R5<=0.012),60) , IF(AND(R5>=12.01,R5<=15),120,0) is 4 IF conected with ",".

This now works.

thanks for all your help

 

[jtakw]

The second IF should be the third parameter of the first IF. Like IF(AND(),-40,IF()).

you formula =IF(AND(R5>=0.05,R5<=0.0699),-40) , IF(AND(R5>=0.07,R5<=0.09),0) , IF(AND(R5>=0.0901,R5<=0.012),60) , IF(AND(R5>=12.01,R5<=15),120,0) is 4 IF conected with ",".

This now works.

thanks for all your help

Hi,

Are you sure it now works? I don't see how that's possible.

As far as correct syntax for the formula, it is as shown in R1, R2 is what you now say is working.

Book1
	R
1	0
2	#VALUE!
3
4
5	0.011
Sheet96

Cell Formulas
Range	Formula
R1	=IF(AND(R5>=0.05,R5<=0.0699),-40,IF(AND(R5>=0.07,R5<=0.09),0,IF(AND(R5>=0.0901,R5<=0.012),60,IF(AND(R5>=12.01,R5<=15),120,0))))
R2	=IF(AND(R5>=0.05,R5<=0.0699),-40) , IF(AND(R5>=0.07,R5<=0.09),0) , IF(AND(R5>=0.0901,R5<=0.012),60) , IF(AND(R5>=12.01,R5<=15),120,0)

HOWEVER, for the formula to work properly, you have to Correct the ranges.

=IF(AND(R5>=0.05,R5<=0.0699),-40,IF(AND(R5>=0.07,R5<=0.09),0,IF(AND(R5>=0.0901,R5<=0.012),60,IF(AND(R5>=12.01,R5<=15),120,0))))

In Red above, if R5 is greater than or equal to .07 And less than or equal to .09, you have 0 (zero) as the result, is this intentional?

Furthermore, if R5 is greater than or equal to .0901 AND less than or equal to .012, then you want 60, this is Impossible, since .0901 is Greater than .012