IF Function with greater than and less than

[daisymoschop]

Hi, I'm trying to get this IF function to work, it's not giving out an error message but using the wrong figure to do the calculation. Checked all the reference cells and can't work out what is going on with it!
[h=3]=IF(H3=1, G3*T3, IF(H3=2, U4*U3, IF(H3=3, V4*V3, IF(H3=4, W4*W3, IF(H3=5, X4*X3, IF(H3=6, Y4*Y3, IF(H3=7, Z4*Z3, IF(H3=8, AA4*AA3, IF(H3=9, AB4*AB3, IF(H3=10, AC4*AC3, IF(20 < H3 > 10, AD4*H3, IF(50 < H3 > 21, AE4*H3, IF(H3 > 51, AF4*H3, 0)))))))))))))

It all works fine for any values of H3 <20
However, when H3>20 it's using the wrong number to multiple H3 by.
AD4 = 1.5, AE4 = 1.1 and AF4= 0.87; for anything above H3=20, it's using 1.5 as the multiplier for all calcs and not applying the other rules.

For example - For 25, it should be giving an answer of 27.5 (If H3>20 = AE4*H3 = 1.1*25 = 27.5), it's actually giving 37.5.
I can only assume it's doing the calculation - AD4*H3 = 1.5 * 25 = 37.5

Any help will be much appreciated!
[/h]

 

[Reggie74]

I'm not very clued up with functions BUT my 'gut' is telling me that the problem lies in 'reading' in both directions ...

=IF(H3=1, G3*T3, IF(H3=2, U4*U3, IF(H3=3, V4*V3, IF(H3=4, W4*W3, IF(H3=5, X4*X3, IF(H3=6, Y4*Y3, IF(H3=7, Z4*Z3, IF(H3=8, AA4*AA3, IF(H3=9, AB4*AB3, IF(H3=10, AC4*AC3, IF(20 < H3 > 10, AD4*H3, IF(50 < H3 > 21, AE4*H3, IF(H3 > 51, AF4*H3, 0)))))))))))))

Try changing the red highlighted parts to read in one direction only (like the rest of your IF statements) ..

Hi, I'm trying to get this IF function to work, it's not giving out an error message but using the wrong figure to do the calculation. Checked all the reference cells and can't work out what is going on with it!
=IF(H3=1, G3*T3, IF(H3=2, U4*U3, IF(H3=3, V4*V3, IF(H3=4, W4*W3, IF(H3=5, X4*X3, IF(H3=6, Y4*Y3, IF(H3=7, Z4*Z3, IF(H3=8, AA4*AA3, IF(H3=9, AB4*AB3, IF(H3=10, AC4*AC3, IF(20 < H3 > 10, AD4*H3, IF(50 < H3 > 21, AE4*H3, IF(H3 > 51, AF4*H3, 0)))))))))))))

It all works fine for any values of H3 <20
However, when H3>20 it's using the wrong number to multiple H3 by.
AD4 = 1.5, AE4 = 1.1 and AF4= 0.87; for anything above H3=20, it's using 1.5 as the multiplier for all calcs and not applying the other rules.

For example - For 25, it should be giving an answer of 27.5 (If H3>20 = AE4*H3 = 1.1*25 = 27.5), it's actually giving 37.5.
I can only assume it's doing the calculation - AD4*H3 = 1.5 * 25 = 37.5

Any help will be much appreciated!

 

[timfritsch]

Try if(and(H3>10,h3 <20),ad4*h3, if(and(h3>20,h3<50),ae4*h3,...


Tim

 

[daisymoschop]

Hi,

Thanks, I've just tried this.. still doesn't seem to give the right number

 

[daisymoschop]

Thanks - Tried this too now, still not giving the right answer

 

[Jonmo1]

Change
IF(20 < H3 > 10
to
IF(AND(H3 > 10,H3 < = 21)

And change
IF(50 < H3 > 21
to
IF(AND(H3 > 21,H3 < = 51)

 

[daisymoschop]

Thank you!!!!!! :d

 

[Jonmo1]

You're welcome.