"Formula is too long"?? Any way to make it longer?

  • Thread starter Thread starter Joseph
  • Start date Start date
J

Joseph

I'm typing in a formula when, after about 8 or 9 lines a
message pops up that says that the formula is too long. Is
there any way to make it longer? I need it to be around 25-
30 lines long to complete the formula.
 
Perhaps you could explain what you are trying to do and post your formula.
DO NOT attach the file to the ng.
 
Joseph said:
I'm typing in a formula when, after about 8 or 9 lines a
message pops up that says that the formula is too long. Is
there any way to make it longer? I need it to be around 25-
30 lines long to complete the formula.
Click to expand...

A formula anything like that long is impossible to maintain! Apart from
obvious things (like shortening excessively long sheet names), I suggest you
should be looking at how you can achieve your aims within Excel's (quite
wide) limitations rather than how to extend these. Try reposting with a
clear description of what you are aiming to achieve.
 
Can you break your formula up into different pieces in
other cells then refer to those cells within your larger
formula? What is your formula trying to accomplish?
 
I need to be able to do more IF statements and for the
formula to be longer. We have seperate tabs for each month
and one for YTD info. It's easy enough to do with the
months that have passed, but it's the future months that
throw me off. I would be able to do it esaily, but my boss
wants 0's instead of blanks in future months, so that
throws off the YTD average. Theres probably a much
simpler way to do this, so let me know if you can. Here's
the formula I'd like to have in the YTD tab:

=IF(Feb.2003!b20=0,(Jan.2003!B20+Feb.2003!B20+Mar.2003!
B20+Apr.2003!B20+May.2003!B20+Jun.2003!B20+Jul.2003!
B20+Aug.2003!B20+Sep.2003!B20+Oct.2003!B20+Nov.2003!
B20+Dec.2003!B20)/1,IF(Mar.2003!b20=0,(Jan.2003!
B20+Feb.2003!B20+Mar.2003!B20+Apr.2003!B20+May.2003!
B20+Jun.2003!B20+Jul.2003!B20+Aug.2003!B20+Sep.2003!
B20+Oct.2003!B20+Nov.2003!B20+Dec.2003!B20)/2,IF(Apr.2003!
b20=0,(Jan.2003!B20+Feb.2003!B20+Mar.2003!B20+Apr.2003!
B20+May.2003!B20+Jun.2003!B20+Jul.2003!B20+Aug.2003!
B20+Sep.2003!B20+Oct.2003!B20+Nov.2003!B20+Dec.2003!
B20)/3,IF(May.2003!b20=0,(Jan.2003!B20+Feb.2003!
B20+Mar.2003!B20+Apr.2003!B20+May.2003!B20+Jun.2003!
B20+Jul.2003!B20+Aug.2003!B20+Sep.2003!B20+Oct.2003!
B20+Nov.2003!B20+Dec.2003!B20)/4,IF(Jun.2003!b20=0,
(Jan.2003!B20+Feb.2003!B20+Mar.2003!B20+Apr.2003!
B20+May.2003!B20+Jun.2003!B20+Jul.2003!B20+Aug.2003!
B20+Sep.2003!B20+Oct.2003!B20+Nov.2003!B20+Dec.2003!
B20)/5,IF(Jul.2003!b20=0,(Jan.2003!B20+Feb.2003!
B20+Mar.2003!B20+Apr.2003!B20+May.2003!B20+Jun.2003!
B20+Jul.2003!B20+Aug.2003!B20+Sep.2003!B20+Oct.2003!
B20+Nov.2003!B20+Dec.2003!B20)/6,IF(Aug.2003!b20=0,
(Jan.2003!B20+Feb.2003!B20+Mar.2003!B20+Apr.2003!
B20+May.2003!B20+Jun.2003!B20+Jul.2003!B20+Aug.2003!
B20+Sep.2003!B20+Oct.2003!B20+Nov.2003!B20+Dec.2003!
B20)/7,IF(Sep.2003!B20=0,(Jan.2003!B20+Feb.2003!
B20+Mar.2003!B20+Apr.2003!B20+May.2003!B20+Jun.2003!
B20+Jul.2003!B20+Aug.2003!B20+Sep.2003!B20+Oct.2003!
B20+Nov.2003!B20+Dec.2003!B20)/8,IF(Oct.2003!B20=0,
(Jan.2003!B20+Feb.2003!B20+Mar.2003!B20+Apr.2003!
B20+May.2003!B20+Jun.2003!B20+Jul.2003!B20+Aug.2003!
B20+Sep.2003!B20+Oct.2003!B20+Nov.2003!B20+Dec.2003!
B20)/9,IF(Nov.2003!B20=0,(Jan.2003!B20+Feb.2003!
B20+Mar.2003!B20+Apr.2003!B20+May.2003!B20+Jun.2003!
B20+Jul.2003!B20+Aug.2003!B20+Sep.2003!B20+Oct.2003!
B20+Nov.2003!B20+Dec.2003!B20)/10,IF(Dec.2003!B20=0,
(Jan.2003!B20+Feb.2003!B20+Mar.2003!B20+Apr.2003!
B20+May.2003!B20+Jun.2003!B20+Jul.2003!B20+Aug.2003!
B20+Sep.2003!B20+Oct.2003!B20+Nov.2003!B20+Dec.2003!
B20)/11,IF(Dec.2003!B20>0,(Jan.2003!B20+Feb.2003!
B20+Mar.2003!B20+Apr.2003!B20+May.2003!B20+Jun.2003!
B20+Jul.2003!B20+Aug.2003!B20+Sep.2003!B20+Oct.2003!
B20+Nov.2003!B20+Dec.2003!B20)/12)))))))))))))
 
If all of your sheets are contiguous, try this 3D reference to add all B20s
from Jan.2003 - Dec.2003:

=SUM(Jan.2003:Dec.2003!B20)

That should make your formula a LOT shorter.

/i.
 
Using Immanuel's recommendation the formula is shortened,
but I still have the problem of too many IF statements.
The formula now reads:

=IF(Feb.2003!b20=0,SUM(Jan.2003:Dec.2003!B20)/1,IF
(Mar.2003!b20=0,SUM(Jan.2003:Dec.2003!B20)/2,IF(Apr.2003!
b20=0,SUM(Jan.2003:Dec.2003!B20)/3,IF(May.2003!b20=0,SUM
(Jan.2003:Dec.2003!B20)/4,IF(Jun.2003!b20=0,SUM
(Jan.2003:Dec.2003!B20)/5,IF(Jul.2003!b20=0,SUM
(Jan.2003:Dec.2003!B20)/6,IF(Aug.2003!b20=0,SUM
(Jan.2003:Dec.2003!B20)/7,IF(Sep.2003!B20=0,SUM
(Jan.2003:Dec.2003!B20)/8,IF(Oct.2003!B20=0,SUM
(Jan.2003:Dec.2003!B20)/9,IF(Nov.2003!B20=0,SUM
(Jan.2003:Dec.2003!B20)/10,IF(Dec.2003!B20=0,SUM
(Jan.2003:Dec.2003!B20)/11,IF(Dec.2003!B20>0,SUM
(Jan.2003:Dec.2003!B20)/12)))))
 
I suggest that instead of putting a numeric 0 in the cells for future months, make it text, i.e.
="0". That way you can take advantage of the fact that AVERAGE ignores text and write the
formula as

=AVERAGE(Jan.2003:Dec.2003!B20)

Another way: if some cell, say A5, contains the date through which you want to average, like
9/30/2003,

=SUM(Jan.2003:Dec.2003!B20)/MONTH(A5)

OTOH, if A5 contains text -- "Sep" -- rather than the full date,

=SUM(Jan.2003:Dec.2003!B20)/MONTH(DATEVALUE(A5&" 01, 2003"))

Or setup a lookup table to translate the month abbreviations into numbers: put the month names
(Jan, Feb, ... Dec) in a column, say K1:K12, then

=SUM(Jan.2003:Dec.2003!B20)/MATCH(A5,K1:K12,0)
 
Or if the cells for future months are formulas that return 0, or need to remain
0 or numeric for whatever reason, AND 0 is not a potential value for a month:-

=SUM(Jan.2003:Dec.2003!B20)/SUMPRODUCT(--((N(INDIRECT({"Jan","Feb","Mar","Apr","
May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}&".2003!B20")))<>0))

Based on one of Harlan's from the other day.
 
Back
Top