|
Help again on an if nested formula
CAn someone detect what I am doing wrong on the following formula:
=IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
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 "CBZ" wrote: CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the
formula to return 9.1%. Celeste "CBZ" wrote: CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
Then change 83.2 in the table back to 80
"CBZ" wrote: Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the formula to return 9.1%. Celeste "CBZ" wrote: CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
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 "CBZ" wrote: Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the formula to return 9.1%. Celeste "CBZ" wrote: CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
if you want an if statement use:-
=IF(F7=80,"9.1%",IF(F7=75,"13.6%",IF(F770,"16.9 %",IF(F740,"17.3%",0)))) Mike "CBZ" wrote: 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 "CBZ" wrote: Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the formula to return 9.1%. Celeste "CBZ" wrote: CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
how about if(f7=.8,.091,if(f7=.75,.136,if(etc -- Don Guillett SalesAid Software "CBZ" wrote in message ... CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
Mike my ref. cell is reading 71.3 and it is returning a 0? Comments?
"Mike H" wrote: if you want an if statement use:- =IF(F7=80,"9.1%",IF(F7=75,"13.6%",IF(F770,"16.9 %",IF(F740,"17.3%",0)))) Mike "CBZ" wrote: 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 "CBZ" wrote: Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the formula to return 9.1%. Celeste "CBZ" wrote: CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
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. -- David Biddulph "CBZ" wrote in message ... Mike my ref. cell is reading 71.3 and it is returning a 0? Comments? "Mike H" wrote: if you want an if statement use:- =IF(F7=80,"9.1%",IF(F7=75,"13.6%",IF(F770,"16.9 %",IF(F740,"17.3%",0)))) Mike "CBZ" wrote: 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 "CBZ" wrote: Sorry... for the confusion if ref. cell f7 is 83.2% as an example I want the formula to return 9.1%. Celeste "CBZ" wrote: CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
Don, we are getting there... I am using the folowing:
=IF(E70.8,0.091,IF(E7=75,0.136,IF(E770,0.169,IF (E740,0.173,IF(E710,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 "Don Guillett" wrote: how about if(f7=.8,.091,if(f7=.75,.136,if(etc -- Don Guillett SalesAid Software "CBZ" wrote in message ... CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
Any idea guys... I have a bus. case due for 2pm and really could benefit from
your expertise and help. Celeste "CBZ" wrote: Don, we are getting there... I am using the folowing: =IF(E70.8,0.091,IF(E7=75,0.136,IF(E770,0.169,IF (E740,0.173,IF(E710,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 "Don Guillett" wrote: how about if(f7=.8,.091,if(f7=.75,.136,if(etc -- Don Guillett SalesAid Software "CBZ" wrote in message ... CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
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. -- David Biddulph "CBZ" wrote in message ... Any idea guys... I have a bus. case due for 2pm and really could benefit from your expertise and help. Celeste "CBZ" wrote: Don, we are getting there... I am using the folowing: =IF(E70.8,0.091,IF(E7=75,0.136,IF(E770,0.169,IF (E740,0.173,IF(E710,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 "Don Guillett" wrote: how about if(f7=.8,.091,if(f7=.75,.136,if(etc -- Don Guillett SalesAid Software "CBZ" wrote in message ... CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
This is PERFECT!!!! Thank you . :+)
"Don Guillett" wrote: Forgot a few dots for percentage IF(E70.8,0.091,IF(E7=75,0.136,IF(E770,0.169,IF( E740,0.173,IF(E710,0.19))))) IF(E70.8,0.091,IF(E7=.75,0.136,IF(E7.70,0.169,I F(E7.40,0.173,IF(E7.10,0.19))))) -- Don Guillett SalesAid Software "CBZ" wrote in message ... Don, we are getting there... I am using the folowing: =IF(E70.8,0.091,IF(E7=75,0.136,IF(E770,0.169,IF (E740,0.173,IF(E710,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 "Don Guillett" wrote: how about if(f7=.8,.091,if(f7=.75,.136,if(etc -- Don Guillett SalesAid Software "CBZ" wrote in message ... CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
Help again on an if nested formula
Glad it helped
-- Don Guillett SalesAid Software "CBZ" wrote in message ... This is PERFECT!!!! Thank you . :+) "Don Guillett" wrote: Forgot a few dots for percentage IF(E70.8,0.091,IF(E7=75,0.136,IF(E770,0.169,IF( E740,0.173,IF(E710,0.19))))) IF(E70.8,0.091,IF(E7=.75,0.136,IF(E7.70,0.169,I F(E7.40,0.173,IF(E7.10,0.19))))) -- Don Guillett SalesAid Software "CBZ" wrote in message ... Don, we are getting there... I am using the folowing: =IF(E70.8,0.091,IF(E7=75,0.136,IF(E770,0.169,IF (E740,0.173,IF(E710,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 "Don Guillett" wrote: how about if(f7=.8,.091,if(f7=.75,.136,if(etc -- Don Guillett SalesAid Software "CBZ" wrote in message ... CAn someone detect what I am doing wrong on the following formula: =IF(F7=40%<=70%,"17.3%",IF(F770%<=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 |
| All times are GMT +1. The time now is 04:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com