If Statement

[Shona]

Hi

I have an if statement that returns false but I want it to return a figure

=IF(E6<0.1,0,IF(AND(E6<0.15,E6>0.1),E6-0.1,IF(AND(E6<0.16,E6>0.15),(E6-0.1)*
3,IF(AND(E6<0.17,E6>0.16),(E6-0.1)*4,IF(AND(E6<0.18,E6>0.17),(E6-0.1)*5,IF(A
ND(E6<0.19,E6>0.18),(E6-0.1)*7,IF(AND(E6<0.2,E6>0.19),(E6-0.1)*10,IF(E6>0.2,
0))))))))

Formula in column O needs to calculate a a figure (in % format) based on the
figure in column E:

If E is 10% or less then O = 0

If E is more than 10% but less then 15% then O = E - 10 (i.e. E =13.25% then
O = 3.25%)

If E is 15% or more but less than 16% then O = (E - 10) x 3

If E is 16% or more but less than 17% then O = (E - 10) x 4

If E is 17% or more but less than 18% then O = (E - 10) x 5

If E is 18% or more but less than 19% then O = (E - 10) x 7

If E is 19% or more but less than 20% then O = (E - 10) x 10

If E is more than 20% then O = 0

Any ideas would be great

Many thanks

Shona

 

[Mike]

Your first problem: You can only nest 7 IF statements.
You have 8. The first and last terms both result in zero
so you can combine them using IF(OR(E6<0.1,E6>0.2),0,....

Next, you use < and > but never =. So what happens when
E6 = 0.1 or E6 = 0.15, etc..? There's no condition for
this which is why you get false. You need to fix your
formula by using AND(E6>=0.1,E6<0.15)

 

[Shona]

thanks