Formula Limit?

[VinnySx]

I have been working on a sheet and everything seem to work well. However with that said check this out

=IF(D50+D51=0,"",IF(D50+D51>=56,"8 Days",IF(D50+D51>=48,"7 Days",IF(D50+D51>=40,"6 Days",IF(D50+D51>=32,"5 Day",IF(D50+D51>=24,"4 Days",IF(D50+D51>=16,"3 Days",IF(D50+D51>=8,"2 Days",IF(D50+D51<=8,"1 Day")))))))))

This formula worked until I added IF(D50+D51>=56,"8 Days", is there a limit for how long a formula can be??

Thank You
VinnySx

[ConneXionLost]

Nesting limits do exist. Hitting them usually indicates you should try a different approach.

Try this instead:

Please Login or Register  to view this content.

Cheers,

[HerryMarkowitz]

Hi,
2003 version has 7 if function limit.
2007-2010-2013 versions has no if function limit.
Check following link...
http://www.youtube.com/watch?v=5V_JYGfCIDE

[martindwilson]

works for me but you have a conflict at the end
IF(D50+D51>=8,"2 Days",IF(D50+D51<=8 cant be both for 8
try this
=IF(SUM(D50:D51),LOOKUP(D50+D51,{0,8,16,24,32,40,48,56},{1,2,3,4,5,6,7,8})&IF(D50+D51<8," day"," days"),"")

[VinnySx]

Hi ConneXionLost

That seemed to work and a lot less code too, THANK YOU. HerryMarkowitz I am using 2007 not sure why its not working?? However ConneXionLost approach seem to do the trick.

Thank You
VinnySx

[FDibbins]

While yes, there is a limit to the length of a formula, I dont think you are even close to that.

Having said that, I would create a small table and use a vlookup for this, instead...

	A	B
26	41	6
27	0	1
28	8	2
29	16	3
30	24	4
31	32	5
32	40	6
33	48	7
34	56	8

A26 contains the value to look up, in this case 41, but you would put that into your formula as D50+D5
B26=VLOOKUP(A26,$A$27:$B$34,2,1)

Using this, you can grow your table with minimum impact of your formula

[Tony Valko]

Hi,
2003 version has 7 if function limit.
2007-2010-2013 versions has no if function limit.

Excel 2003 and earlier has a limit of 7 nested levels for ALL functions, not just the IF function.

http://office.microsoft.com/en-us/ex...005199291.aspx


Excel 2007 and later the limit is 64 levels of nested functions.

http://office.microsoft.com/en-us/ex...010073849.aspx