IF Statement

[Robbo1971]

I am trying to write an 'IF Statement' where I9 is the variable ranging from 0 to 5000.

R9 and S9 return a different answer depending on the figure I9 returns.

Currently if I9 is '0' R9 and S9 default to 0.20 due to the formula in R9 and S9 respectively.

If I9 is equal to 0 I want I9 to show a 0 and not the 0.20 it currently is.

The formula I currently am using is:

=IF(I9>1500,S9,R9)

 

[MintJulep]

If I9 is equal to 0 I want I9 to show a 0 and not the 0.20 it currently is.

This doesn't make any sense. If I9 = 0 then I9 is 0, not 0.2.

 

[Robbo1971]

REVISED...

I am trying to write an 'IF Statement' where I9 is the variable ranging from 0 to 5000.

R9 and S9 return a different answer depending on the figure I9 returns.

Currently if I9 is '0' R9 and S9 default to 0.20 due to the formula in R9 and S9 respectively.

If I9 is equal to 0 I want X9 to show a 0 and not the 0.20 it currently is.

The formula I currently am using is:

=IF(I9>1500,S9,R9)

the formula I devised, but not seem to work:

=IF(AND(I9>0,I9<1500,R9,IF(I9>1500,S9,0)))

 

[handleman]

Unless I'm totally misreading your intention, you have a very simple nested if. The first check is to see if I9=0. If I9=0, you want the formula result to be 0. Therefore you begin with:

=IF(I9=0,0

If I9 is not 0, you want your original IF statement to apply. Therefore, you get:

=IF(I9=0,0,IF(I9>1500,S9,R9))

 

[Robbo1971]

RESULT... many thanks

these things are easier than you think!

 

[NormPeterson]

You were closer than you think - all you needed to do was close the logical 'AND' with a right hand parenthesis before telling X9 to display the contents of R9. Then remove the third right hand parenthesis at the end.

=IF(AND(I9>0,I9<1500),R9,IF(I9>1500,S9,0))

I think that the number of right hand parentheses at the end of the 'IF' formula needs to be the same as the number of IF's in the formula.



Norm

 

[GregLocock]

Yes, in this case, not in general

eg if(a=2,if(foo),if(bar))

Cheers

Greg Locock

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.