EXCEL IF statement NESTING HELP REQUEST

[Mitchyb1]

Hi!

Below is my discrepency..

IF cell is <=.09, .09 should be the outcome.
IF <NOBR>cell</NOBR> is >.09 but <=.33, .33 should be the outcome.
IF cell is >.33 but <=.67, .67 should be the outcome.
IF cell is >.67 but <=.99, .09 should be the outcome.
If cell is = 0, .09 should be the outcome.


Below is my formula that keeps returning FALSE
=IF(BP2<=0.09,0.09,IF(BP2>=0.1<=0.33,0.33,IF(BP2>=0.34<=0.67,0.67,IF(BP2>0.67<=0.99,IF(BP2<=0,0.09)))))
Can anyone be of <NOBR>assistance</NOBR> with this? It's <NOBR>driving</NOBR> me crazy... thank you so much!!!!

 

[mikerickson]

If A1 is the cell with the value, perhaps something like
=HLOOKUP(A1,{-0.1,0.09,0.33,0.67,0.99},1)

 

[lenze]

Mike, you are right on, but I think his teacher wants an IF statement

lenze

 

[Mitchyb1]

No, I am far from being good at excel and I am not trying to prove that I am.

The value I am looking at is in cell BP2 which is 0.69

I'm trying to use an IF statement because that's basically what I thought would be the best solution. I'm guessing it's not the best solution?

 

[mikerickson]

If your input data is going to be in a limited range (e.g. no values > 1), V/-/H LOOKUP is a good way to go. If you want it rounded up to the nearest 1/3, CEILING would be better.

Browsing through the Insert Function dialog box is a good way to see the possible Excel functions that might meet your needs.

 

[wsjackman]

Hi!

Below is my formula that keeps returning FALSE
=IF(BP2<=0.09,0.09,IF(BP2>=0.1<=0.33,0.33,IF(BP2>=0.34<=0.67,0.67,IF(BP2>0.67<=0.99,IF(BP2<=0,0.09)))))
Can anyone be of <NOBR>assistance</NOBR> with this? It's <NOBR>driving</NOBR> me crazy... thank you so much!!!!

you need the and() fuction for this style of if statement

=IF(BP2<=0.09,0.09,IF(and(BP2>=0.1,BP2<=0.33),0.33...
and so forth

the best formula is one which works. experienced excel users would not use this format, but almost all will have used something sililar while they were learning.

 

[lenze]

Actually, the AND's are not needed!!!!!

=IF(BP2<=0.09,0.09,IF(BP2<=0.33),0.33...

Let the IF statement do the work
lenze

 

[wsjackman]

Actually I agree with Lenze on not needing the and()s. One of the weaknesses of the and() function as written, is what happens if there is something between 0.09 and 0.1.

the modification to
=IF(BP2<=0.09,0.09,IF(and(BP2>0.09,BP2<=0.33),0.33...
would cover this, but Lenze's formula is better, because it is simpler. the >0.09 is already covered by the first if section.

mikerickson's formula will not work for two reasons.
first, for example 0.08 would be -.1 not 0.09 and 0.1 would be 0.09 not .33
additionally values greater than .99 would be .99 not .09.

(Note the only reason I did not recommend a similar wrong formula was that it was already proposed. I did not recognize the error until just now. )

 

[lenze]

=IF(BP2<=0.09,0.09,IF(and(BP2>0.09,BP2<=0.33),0.33...
would cover this, but Lenze's formula is better, because it is simpler. the >0.09 is already covered by the first if section.

You would not believe how many instructors at the college level I have run into that do not understand this principle.

lenze

 

[wsjackman]

I do believe.
I once taught a lunch and learn on "Quick Excel Macros" and used "formatting a sheet for consistant look" as my example,
None of the attending RDE managers (all with PHDs and "experienced" Excel users) knew you could format individual cells in an Excel spread sheet.
One of them later modified a five color conditional formatting macro I had made, to have a greater than and less than format similar to the proposed formula, because he was more "comfortable" with it.