Large formulas possible in Named Range ?

[StanSz]

I've got a large ss (5000 rows) with many complicated formulas.

Since the wb has gotten quite large I've started thinking about ways to make the wb more efficient.

I currently have these large formulas copied throughout 5000 rows.

Is the following thinking worthwhile, and possible:

I thought about copying a large formula into a Name, "Formula1" for example. (Insert/Name/Define)

Then, rather than copying my complicated formula throughout 5000 rows, I would simply enter =Formula1 in the cell in question and copy this =Formula1 throughout the 5000 rows.

My thinking was that the complicated formula would only be entered once, somewhere in Excel's memory, and then the much simpler "Formula1" would be what would be entered in each cell.

Does this save 'space' or memory ??

Is this a worthwhile approach ??

In testing this "Formula1" idea I find that it does work, although there seems to be a limit to the amount of a formula that I can copy (or enter) into the Defining section of the Named Range. And this of course means that the largest and most complicated formulas don't seem to be able to be entered into a Name.

Is there a limit to the amout of characters that can be entered into a Named formula ??

Any other thoughts on how to save 'space' or memory ??

Thanks

StanSz

 

[Brian from Maui]

Why don't you post some the formulae you use and maybe someone can come up with more efficient ones.

 

[StanSz]

Brian,

OK, here's a sample formula, although I still would like to get some info on the limitations, and advantages of entering formulas in Names.

=IF(AND(BI31=1,INDEX(K:K,AB31)<>1,J31<60,'Prices'!J31<>1,
NOT(OR(INDEX(BA:BA,AB31)=1,INDEX(BD:BD,AB31)=1,INDEX(BE:BE,AB31)=1,
INDEX(DH:DH,AB31)=1,AND(INDEX(BF:BF,AB31)=1,INDEX(J:J,AB31)<60))),
NOT(AND(AND(J33<59,J32>59,J31<59,K31>0.3,NOT(AND(AJ31=1,AN31=1))),D31<>E31)),
NOT(AND(INDEX(BB:BB,AB31)=1,K31*100<J31))
NOT(AND(OR(INDEX(BC:BC,AB31)=1,D31<>E31),B31<B36)),
NOT(AND(INDEX(CN:CN,AB31)=1,J31<J32,D31<>E31)),
OR(J31<J32,AND(D31="L",D32="S"),AND(E31="L",E32="S"))),1,"")

 

[Brian from Maui]

See,

http://www.decisionmodels.com/optspeedb.htm

And maybe an explanation in words what you're trying to accomplish with some same data and expected results.