Help again on an if nested formula

[Guest]

CAn someone detect what I am doing wrong on the following formula:


=IF(F7>=40%<=70%,"17.3%",IF(F7>70%<=75%,"16.9%",IF(F7>=75%<=80%,"13.6%",IF(F7>=80%<=100%,"9.1%",0))))

Ideally if the ref cell (f7) has 83.2% I want 13.6% returned.

Thanks so much.

Celeste

 

[Guest]

I would do it differently. You may care to try this:-

Build a simple table in (say) A1 to B6 with your conditions:-

0 0
40 17.30%
70 16.90%
75 13.60%
83.3 9.10%

Then a simple Vlookup looking at F7

=VLOOKUP(F7,A1:B6,2,TRUE)

Mike

 

[Guest]

Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the
formula to return 9.1%.

Celeste

 

[Guest]

Then change 83.2 in the table back to 80

Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the
formula to return 9.1%.

Celeste

 

[Guest]

Mike I need to capture any data pt. in b/w 0 to 100 so that is why I used < &
<. So not sure how to do this on a lookup?

Celeste

 

[Guest]

if you want an if statement use:-

=IF(F7>=80,"9.1%",IF(F7>=75,"13.6%",IF(F7>70,"16.9%",IF(F7>40,"17.3%",0))))

Mike

 

[Don Guillett]

how about
if(f7>=.8,.091,if(f7>=.75,.136,if(etc

 

[Guest]

Mike my ref. cell is reading 71.3 and it is returning a 0? Comments?

 

[David Biddulph]

Are you sure that your reference cell has a number and not text? What does
=ISNUMBER(F7) give you?

If you've done the same elsewhere as in your formula below and put your
numbers in quotes, they'll be treated as text and evaluated as zero.

 

[Guest]

Don, we are getting there... I am using the folowing:

=IF(E7>0.8,0.091,IF(E7>=75,0.136,IF(E7>70,0.169,IF(E7>40,0.173,IF(E7>10,0.19)))))

however when E7 is 83% it is returning 9.1% - perfect but when E7 reads
71.3% it is returning "false" vs. 16.9%

Celeste

 

[Guest]

Any idea guys... I have a bus. case due for 2pm and really could benefit from
your expertise and help.

Celeste

 

[David Biddulph]

Look again at your formula.

You've tested initially against 0.8 (i.e. 80%). If E7 is greater than that
you get your 9.1% result.
If E7 is less than 0.8 you then test it for being greater than 75, 70, 40,
and 10 in turn, none of which can be true if it is less than 0.8, so the
answer FALSE is what you've asked for.

 

[Don Guillett]

Forgot a few dots for percentage
IF(E7>0.8,0.091,IF(E7>=75,0.136,IF(E7>70,0.169,IF(E7>40,0.173,IF(E7>10,0.19)))))
IF(E7>0.8,0.091,IF(E7>=.75,0.136,IF(E7>.70,0.169,IF(E7>.40,0.173,IF(E7>.10,0.19)))))

 

[Guest]

This is PERFECT!!!! Thank you . :+)

Forgot a few dots for percentage
IF(E7>0.8,0.091,IF(E7>=75,0.136,IF(E7>70,0.169,IF(E7>40,0.173,IF(E7>10,0.19)))))
IF(E7>0.8,0.091,IF(E7>=.75,0.136,IF(E7>.70,0.169,IF(E7>.40,0.173,IF(E7>.10,0.19)))))

 

[Don Guillett]

Glad it helped