Hi, Here is my EXCEL formula issue:

IF cell is <=.09, .09

IF cell is >.09 but <=.33, .33

IF cell is >.33 but <=.67, .67

IF cell is >.67 but <= 0, .09Below is my formula that keeps returning FALSE

=IF(BP2<=0.09,0.09,IF(BP2>0.09<=0.33,0.33,IF(BP2>0.33<=0.67,0.67,IF(BP2>0.67<=0,0.09))))

Can anyone be of assistance with this? It's driving me crazy... thank you so much!

Try this: =IF(BP2<=0.09,0.09,IF(BP2<=0.33,0.33,IF(BP2<=0.67,0.67,IF(BP2<=0,0.09))))

MIKE

That formula is still coming up as false - plus I think each nested formula would override the other. Example: returned value for <=.33 would override the <=.09. :( Thanks for trying to help tho!

I entered the formula in cell BQ2 and it worked for me. Did you copy & paste or did you type it in?

The reason it works, is that once you hit a True statement, the formula stops and does not check any further along.

MIKE

I copied and pasted into excel. I'm not sure why it still isn't working. The value in BP2 is .69

I used the following formula:

=IF(BP2<=0.09,0.09,IF(BP2<=0.33,0.33,IF(BP2<=0.67,0.67,IF(BP2<=0,0.09))))

And the outcome is False.

My question to your formula is also this issue:

What if the value in my cell is .98? Anything greater than .67 but less than or equal to .99 should equal .09 within that formula. I am still puzzled.

You know what, I think I got it to work using your formula and I added IF(BP2<=.99,.09) to the rest of the formula. I didn't know that adding another condition to the IF statement trumps until it finds the true condition. Thanks a lot for your help!

To get rid of the FALSE statement coming up, simply put a zero as the last statement, like: BP2<=0,0.09,0))))

or in your case another 0.09

MIKE

I've had a long day.......... In your original formula, as the last test you had

BP2 <= 0but since your first condition already test for that with

BP <= 0.09It probably should have been BP < 1

So the who formula should be:

=IF(BP2<=0.09,0.09,IF(BP2<=0.33,0.33,IF(BP2<=0.67,0.67,IF(BP2<1,0.09,"Error in Number"))))

Since you only want decimals not whole numbers, I added the little warning "Error in Number" if anyone tries to use a whole number.

MIKE

What about this for "short and sweet"? =IF(OR(BP2>0.67,BP2<0.1),0.09,IF(AND(BP2>0.09,BP2<0.34),0.33,0.67))or just for fun (and still shorter than some other options...)

=IF(OR(BP2>0.67,BP2<0.1),0.09,IF(MATCH(BP2,{0.67,0.33},-1)=1,0.67,0.33))

What about this for "short and sweet"?Today, I had a hard enough time just getting it right. :-)

MIKE

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History