Hi guys
I can not solve a problem I currently have using the following formula in excel 2003,
=IF(E73<2,B15*0.02+55,IF(E73<3,B15*0.024+60,IF(E73<4,B15*0.03+65,IF(E73<5,B15*0.036+70,IF(E73<6,B15* 0.042+75,IF(E73<7,B15*0.05+80,IF(E73<8,B15*0.061+85,IF(E73<9,B15*0.071+90))))))))
It works perfect except for when cell E73 has a zero balance. When a zero balance is in cell E73 I need a zero in the target cell which is cell K73. I have tried several scenarios attached to the existing formula including IF & COUNTIF but can not seem to solve what appears such a simple problem. Any assistance would be greatly appreciated.
Regards
Mark
Hi Mark,
welcome to the forum.
You've already maxed out the 7 nested IFs Excel 2003 tolerates, so there's not much more you can do hanging on to that structure. Nested IFs can often be replaced by something more versatile. Like this:
=IF(B15=0,0,B15*LOOKUP(E73,{1,2,3,4,5,6,7,8},{0.02,0.024,0.03,0.036,0.042,0.05,0.061,0.071})+LOOKUP( E73,{1,2,3,4,5,6,7,8},{55,60,65,70,75,80,85,90}))
Instead of hardcoding the Lookup arrays into the formula, you could also enter them in a part of your sheet and refer Lookup to those ranges.
does that make sense?
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking theicon below the post.
Avoid pie charts with more than two data points. Why? See here (pdf, 559 kb). The only acceptable pie chart is here.
Hi Teylyn,
Thanks for the reply, now I understand why when I added an additional IF scenario it highlite the last IF. I have tried the formula suggested which works except it returns a #N/A instead of a 0 within the target cell. I have never had this before so I assume it is associated with LOOKUP. Once again, thanks for your input as it explained to me part of what I was doing wrong(too many IF's).
Regards
Mark
Ah, sorry, my bad. The formula has to start with
=IF(E73=0,0,B15*LOOKUP(E73,{1 ....
teylyn
Microsoft MVP - Excel
At Excelforum, you can say "Thank you!" by clicking the
Teylyn,
Thanks for that, works grouse now.
Regards
Mark
Michael, please take a moment to read the rules and then post your question in a new thread in accordance with rule #2. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Bookmarks