Why is 7 the magic number? (IF statements)

Posted by Fred Sayers on August 07, 2001 2:11 PM

I have a small problem, and would be REALLY gratefull if someone could show me the light.

This is using Excell 97.

I am using the IF command in a branching style to help me arrive at a final cost for something. Basically if the number (date) is higher or lower than a certain variable date then do this, or that... its also got to be compounding at a 5% increase every year.. The formula is great and relativly simple because once youve done one step, the rest just builds. The problem I am having is that if I would like to do more than 7 Nested IF statements, I cant because of that magic number.

I read a previous string about something similar about the formula being too long, and COUNTIF was suggested... however I am relativly new to all this and cant implement it into my formula, which is btw..

=IF(F8&LT;37347,(1+(((F8-36982))/365)*5%)*E8,IF(F8&LT;37712,(1+(((F8-37347))/365)*5%)*E8*1.05,IF(F8&LT;38078,(1+(((F8-37712))/365)*5%)*E8*1.05*1.05,IF(F8&LT;38443,(1+(((F8-38078))/366)*5%)*E8*1.05*1.05*1.05,IF(F8&LT;38808,(1+(((F8-38443))/365)*5%)*E8*1.05*1.05*1.05*1.05,IF(F8&LT;39173,(1+(((F8-38808))/365)*5%)*E8*1.05*1.05*1.05*1.05*1.05))))))

Please help me throw some light on this problem because its making things very tricky for me and I am sure there is a easier method of doing this!

Fred. =)

Posted by Cory on August 07, 2001 2:37 PM

If I could see your formulain action on your sheet, I could probably find a more efficient one for you to use, but for now I'll just tell you how to break that barrier...

In cell A1:
=if(a1,b1,if(b1,c1,if(c1,d1,if(d1,e1,if(e1,f1,if(f1,g1,if(g1,h1,"")))))))

With this If pattern (ending in the "" which means if no condition is met, then make the cell blank), you're limited to the 7 you're talking about. If you wanted to continue this formula with if(h1,g1,if... do this:

In cell B1, continue the formula:

=if(h1,i1,if(i1,j1,if(j1,k1,if(k1,l1,if(l1,m1,if(m1,n1,if(n1,o1,"")))))))

And then in your first formula, change the "" to "B1"(without the quotes...). This way if none of the conditions are met in the first formula, instead of running into a dead end because of the 7 rule, it'll refer to cell B1 and start using it's formula as the next 7 layers. If you wanted to continue it even further, change the "" in B1 to "C1", then continue your formula in cell C1.

Make sense?

Cory

Posted by Aladin Akyurek on August 07, 2001 3:30 PM

Fred,

Did you have a look financial functions that are available in Excel?

No finance background, whence what follows is just a guess.

I'll assume F7=36982.

=E8*(1+(F8-EDATE(F8,12)/365)*0.05)*1.05^(YEAR(F8)-YEAR(F7))

In order to use EDATE, you need to activate Analysis Toolpak via Tools|Add-Ins.

Am I close?

Note. I don't believe having more than 3 (at most 4) nested IFs is the right thing to do. More of them makes the formula undebuggable for the designer & unreadable for the user.

Aladin

==========

Posted by Mark W. on August 07, 2001 4:07 PM

Because... : )

there are seven deadly sins....
...seven seas...
God rested on the 7th day...
...Bill Gates likes 7*UP

Posted by Aladin Akyurek on August 07, 2001 4:16 PM

Re: Because... : )

Badly needed a good lough. Thanks. there are seven deadly sins....

Posted by fred on August 08, 2001 8:23 AM

Yeah that makes sense, thanks very much all of you for helping... The formula is something I did for a cost analysis scenario thing for construction costs... taking in a 5% escalation of costs. If anyone has the time (or is even reading this!!) could they please email me and let me show them the file, I would post it to the net, but i dont know how to do it properly!

Thanks again,

Fred =) fsayers@hotmail.com

Posted by Fred on August 08, 2001 2:53 PM

Aladin, thanks very much for that attempt to re-write the formula, I think I am almost at the brink of discovery, and i think you helped me along the way. as did the rest of you all! especially the ones that emailed me! =)

Thanks again, Fred =)