What slows down Excel

[mkw4949]

i have a general question about the type of formulas that will slow excel down and inflate the file size. For example from what I've heard using an arrey formula will generally (i.e. sumproduct, sumif or anything inside []) slow down you spread sheet but will it also increase the file size? What about the Offset formula? If I use ALOT of Offset functions will that severely impact my spread sheets performance. And I do mean ALOT as in thousands.

Also what directly impacts the file size and how can I better manage this? I am a pretty advanced user and I've looked in books but have not found the answer. I would greatly appreciate any help. Thanks.

 

[Smitty]

See http://www.decisionmodels.com/ for some good tips.

HTH,

 

[Jonmo1]

All the volatile functions will decrease performance.
Here's a list
http://www.bettersolutions.com/excel/EDK153/ME810324332.htm

And yes, array formulas definately decrease performance.
I don't know about the SIZE, but performance at least.

But sumif is not considered an array formula..

SUMPRODUCT and anything enlosed in {} (requiring Ctrl+shift+enter) is an array. Probably some others, but I'm not sure...

Also, using entire column references will decrease performance.
like Vlookup(A1,B:C,2,FALSE), instead of Vlookup(A1,A1:B1000,2,FALSE)

Consider using dynamic named ranges instead
http://www.cpearson.com/excel/excelF.htm#DynamicRanges

Hope this helps...

 

[mkw4949]

Thanks for your imput! not the answer I was hoping for but oh well.

 

[iliace]

Here's an article on MSDN (for Excel 2007 but largely applicable to previous versions). Also from the Decision Models folks.

http://msdn.microsoft.com/en-us/library/aa730921.aspx

Another useful article on the calculation chain in general:

http://msdn.microsoft.com/en-us/library/aa140058.aspx

 

[PA HS Teacher]

My understanding is that the Direct and Match functions are not volatile, and hence do not recalcuate unless directly affected by changes in the sheet.

Are you using Indirect to deal with variable references within a sheet? Or linked to other files? Could you provide an example of a couple types of Indirect based formulas you are using?

 

[mkw4949]

The formula in question is:

=IF(OR(U$9<$C13,U$9>$C14),0,NORMDIST(U$9,AVERAGE(OFFSET($T$9,0,$C13):OFFSET($T$9,0,$C14))^$C23,STDEV(OFFSET($T$9,0,$C13):OFFSET($T$9,0,$C14)),$D23)^$C22)

What it does is set up a curve over which I am distributing costs. based on simple input cells the user is able to change the type of curve (i.e. bell curve, straight line, front and back loaded) and the time frame over which the costs will be incurred. Specifically the time frame is driven by the Offset function. So the right now it's not a huge "Speed" problem but it is creating a very large file. so I end up needing the above formula for each month of cost for each cost. so I have say 5 different cost line items and each cost runs for 10 years (120 columns one for each month). so you can see how the number muliplies FAST.

 

[mkw4949]

The Crux of the above formula is the NORMDIST function.

 

[Smitty]

What I generally do with "mega formulas" is keep a spare or master formula and only apply it where and when I need it. Then paste values for everything that's been updated.

Once you figure out a methodology that works for you, you can write a macro to apply it when and where needed and then value it out.

 

[PA HS Teacher]

Try Replacing These types of reference:
OFFSET($T$9,0,$C13)

With something more like this:
Index($T$9:$IV$9,$C13)