Formula to long error

[bradmcq]

Hi, I have a long if formula in excel, but can't finish as I get the error
that the formula is too long. Is there any way to extend the length a
formula can be?

Otherwise I guess the only way around it is to put parts of the formula in
other cells?,

or

As a big part of the formula repeats, as the logic part of the if can you
create a name for a formula and store it as a macro or somewhere else and
refer to it as the name in the if statement to reduce the length ?

 

[Sean Timmons]

Sounds like you need a different formula altogether. IF can only handle 7
levels, and formulas can only be so many characters. Perhaps if you provide
the issue you are tryign to resolve, we can give a smaller formula?

 

[Shane Devenshire]

Hi,

Excel 2003 allows formulas up to 1024 in 2007 it is 8192. Which means you
could upgrade.

In most cases formulas are too long because they are not written efficiently
using the correct approach. For example, long IF statements often would be
better handled with VLOOKUP.

You can sometimes shorten formulas by using range names. You can also
define components of the formula as a name and use those. For example,
suppose your formula uses INDEX(B1:B100,MATCH(A1,L1:L100,0)) a number of
times you could define it as IM in the Insert, Names dialog box (include an =
sign) and substitute that for each occurance.