Dealing with Long Formulas

[anonymous321]

Hi,

I have combined 30 "if formulas" with & operator. and the length of total resultant formula is more than 9000 characters. It error is "You can't use more than 8192 characters in excel formula". Please help me on this issue.

Kind Regards,

[MrShorty]

Without more information (what do the IF() functions look like? What is the overall goal of the formula?), I'm not sure we can help much.

Are you required to do it all in one cell? My inclination would be to put each IF() function in its own cell, then use something like CONCAT() function or TEXTJOIN() function (available in newer versions of Excel or in alternative spreadsheets like LO Calc or Google Sheets)

CONCAT() function: https://support.microsoft.com/en-us/...rs=en-us&ad=us
TEXTJOIN() function: https://support.microsoft.com/en-us/...3-0e8fc845691c

If, for some reason, you are limited to Excel 2007 as your profile states, then I would still be inclined to use the helper cells, then your final cell can be long concatenate string =cell1&cell2&cell3&...

[6StringJazzer]

It might also benefit from a VBA solution but as MrShorty notes there is not enough information here to provide a solution.

Also, you have 30 IF functions, 9000 characters. That is 300 characters per IF function. This suggests that there may be text in the IF function that could probably just go in a cell instead of baking it into the function.

Show us the formula or at least part of it.

[TMS]

As MrShorty says, without more info, it is difficult to offer advice. Do your IF functions refer to other sheets, or even other workbooks? How many Nested IFs are there? Could you combine any of the IF tests using AND or OR? Could you use LOOKUP, VLOOKUP, INDEX/MATCH, CHOOSE, or whatever functions to replace any or all of the IFs? Could you use (short) Dynamic Named Ranges to replace any of the range references?

[hrlngrv]

If one's using an additional column of supporting formulas, say actual data in A:G, then IF formulas for individual rows in column H, those could be along the lines of

H1: =IF(somecondition,something_with_positive_length,"")

H2: =IF(somecondition,IF(H1<>"",H1&", ","")&something_with_positive_length),H1)

From H2 down, formulas would only increase in length as the row portion of cell addresses clicked over from 9 to 10, 99 to 100, 999 to 1000, etc.

[anonymous321]

Now here is the formula I am trying to use. I can use concatenate function but that would not be good for me as it would contain alot of space. And my data is already contained in many columns.


I have attached the formula in text file as the form is not letting me paste the formula reason being stated as HTML content.

[MrShorty]

And my data is already contained in many columns.

Excel will allow you to use up to 16000 columns. How close are you to this limit? If needed, can you add another tab to the workbook (Excel's only limit to tabs in a workbook is available memory)?

What I see in this long unwieldy formula is a repetition of a "lookup" -- look at the value in G6, choose the parameters for the equation, then join together multiple computations from that equation. If you build a proper lookup table, all those IF(G6<...) functions can be replaced (as TMS suggested) with a lookup function [LOOKUP() or VLOOKUP() or HLOOKUP() or INDEX(coefficients,MATCH())], which can dramatically reduce the number of characters needed for this formula.

Since I don't know what you are allowed/required to do here, I haven't gone to great lengths to figure this out. Is that something you could do -- build a lookup table (or tables) and use a lookup function to extract the needed values from the lookup table?

If you are unfamiliar with Excel's lookup functions, most people start with VLOOKUP() when learning: https://support.microsoft.com/en-us/...rs=en-us&ad=us

[hrlngrv]

Is this REALLY the formula? Every outer/concatenated IF call has the EXACT SAME form, namely IF(ISNUMBER(SEARCH("School",A2)),IF(...)) ? So that if "School" doesn't appear in the value of cell A2, you want the result to be something like FALSEFALSEFALSE...FALSE ? You could save considerable space changing the overall formula to

The site is acting up, so I can't use the less than symbol, so I've rewritten all comparisons using the greater than symbol since G6 less than x == x greater than G6.

=IF(COUNTIF(A2,"*School*"),IF(101>G6,...)&IF(101>G6,...)&...&IF(101>G6,...),REPT("FALSE",27))

You could also reduce IF(101>G6,..a..,IF(OR(G6>100,25001>G6),..b..,IF(OR(G6>25001,60000>G6),..c..))) to

CHOOSE(MATCH(G6,{-1E300;101;25001;60000}),..a..,..b..,..c..,FALSE).

Then there's the common pattern of the 2nd arguments to the inner IF calls: 10^(d*LOG(G6)^2+e*LOG(G6)+f) . That could be reduced to 10^SERIESSUM(LOG(G6),0,1,{f;e;d}). More to the point,

IF(p>G6,10^(dp*LOG(G6)^2+ep*LOG(G6)+fp),IF(OR(G6>p-1,q>G6),10^(dq*LOG(G6)^2+eq*LOG(G6)+fq),IF(OR(G6>q-1,r>G6),10^(dr*LOG(G6)^2+er*LOG(G6)+fr))))

could be replaced by

IF(r>G6,10^SERIESSUM(LOG(G6),0,1,INDEX({fp,ep,dp;fq,eq,dq;fr,er,dr},MATCH(G6,{-1E300;101;25001}),0)))

Actually, since the r>G6 test is common to all the concatenated IF calls, the whole thing could be

=IF(AND(COUNTIF(A2,"*School*"),60000>G6),(10^SERIESSUM(...))&(10^SERIESSUM(...))&...&(10^SERIESSUM(...)),REPT("FALSE",27))

You really want either 27 numbers with fractional parts concatenated together with no separators OR 27 instances of FALSE concatenated together?