How is the Length of a Formula calculated ?

mstuf

Active Member
Joined
Feb 4, 2003
Messages
315
Good Morning -

I have a Long Formula that I use -- I have had to divide it into two Cells and then Combine the results to display in a third Cell.

I am close to the Maximum Formula Length on both of the Work Cells - Trying to add even a few more characters to either results in a "Formula too Long" Message.

I'm Puzzled - the formula in the First Work Cell is Much Longer than the Formula in the Second. It has more Cell Refrences - More Text - More Functions yet both are right at the Maximum before recieving the "Formula too Long" Message.

In the past I have seen some massive formulas displayed here in threads that are shown without reference to being divided or being too long.

Can Anyone help me to understand how that is Calculated - and,

Can that Length Setting be Changed Somehow ??



I'd like to add some text to what I have and would like to do it without using another Column.

Thanks ( 3AM here - will look in in the morning )


WIN XP SP3 EXCEL 2002 SP3
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Colin Legg

MrExcel MVP
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi,

In Excel 2003 the limit of a formula's length is 1,024 characters.

There are other limitations, such as 7 nested functions, as outlined on the below link which may be affecting your formulas:
http://office.microsoft.com/en-us/excel/HP051992911033.aspx


Perhaps if you post your formulas we can try to trim them down or make some other suggestions?

Hope that helps...
 
Last edited:

mstuf

Active Member
Joined
Feb 4, 2003
Messages
315
Thank you - I attempted to post it but need to invalidate HTML on my post to display the formula --

Do you know how to Turn off HTML for my Post ?

Thanks
 

mstuf

Active Member
Joined
Feb 4, 2003
Messages
315
Figured out how to post the formula with HTML in it


Do other versions of Excel have larger Allowances ?

My Formula combines contents of my worksheet cells with HTML for Import into Web Usage.


1st Cell
HTML:
="</head><table align=center bgcolor=000000><tr><td valign=top align=Center>  <font face='Algerian' color=#FF0000 size=7><B> "&C22166&"</font><br></B><font face='Comic Sans MS' color=#FF0000 size=6><B> "&B22166&" <br> "&D22166&" </font><br></B><font face='Comic Sans MS'><FONT color=LIME size=5><B><br>   Media Condition = "&AE22166&"  <br>  Cover Condition = "&AF22166&"  <br><br> Label and Release Number = "&AR22166&" "&AS22166&"  <br>  Made in "&AU22166&" in "&AT22166&" <br> Number of Pieces in Set = "&AW22166&"<br><br> Description <br> "&Z22166&" <br><br>"


2nd Cell
HTML:
="Mono = (M) Stereo = (S) - This one =<font face ='Comic Sans MS' color=#FF0000 size=5><b>  "&AV22166&" </font>   <br><br></b></font><br><font face='Comic Sans MS' color=#FFFFFF size=3> <! - -#3 WHITE - - >Stock # and Location Info  Gm"&R22166&" - "&AX22166&" </font><br><br><font face= 'Comic Sans' MS color=#FFFFFF size= 5> If you need to see a photo of this item please contact me and allow 24 - 48 Hours <br><br><br></tr></td></Table>"



Combined by the third Cell

=L22166&M22166


Thanks for offering to look
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,114
Messages
5,640,196
Members
417,130
Latest member
Darion2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top