Excel Nested If Statement

June 23, 2009 at 14:34:29
Specs: Windows XP
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, .09

Below 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!


See More: Excel Nested If Statement

Report •


#1
June 23, 2009 at 14:51:00
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

http://www.skeptic.com/


Report •

#2
June 23, 2009 at 14:54:23
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!


Report •

#3
June 23, 2009 at 15:21:56
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

http://www.skeptic.com/


Report •

Related Solutions

#4
June 23, 2009 at 15:29:45
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.


Report •

#5
June 23, 2009 at 15:41:31
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!


Report •

#6
June 23, 2009 at 15:44:45
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

http://www.skeptic.com/


Report •

#7
June 23, 2009 at 16:28:09
I've had a long day..........

In your original formula, as the last test you had
BP2 <= 0

but since your first condition already test for that with
BP <= 0.09

It 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

http://www.skeptic.com/


Report •

#8
June 23, 2009 at 19:23:39
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))


Report •

#9
June 23, 2009 at 21:16:31
What about this for "short and sweet"?

Today, I had a hard enough time just getting it right. :-)

MIKE

http://www.skeptic.com/


Report •


Ask Question