Thread: Lo-o-ong Calculation Times

I recently converted to Home editions of Vista and Office 2007 because I needed to replace my old machine and couldn't wait. No big problems, except this one Excel workbook.

I manage my investments with an Excel workbook. I converted the workbook from 2003 to 2007. I can't go back to Office 2003.

There are currently eleven sheets with a number of equations working on data pulled across sheets. All my formulas are fairly straight arithmetic. There are some cells using finance functions, some of which I had to define myself. Those can involve a series of nested conditionals. These use some basic statistical functions -- COUNT and ACOUNT, AVERAGE, and similar functions. There are two graphs covering about 50 periods each. There are some custom numeric formats controlled by IF-THEN clauses to highlight unusual results.

Problem: Any data entry anywhere causes a 15-20 second pause. Mike 1001 had the same type of problem but his solution doesn't seem to apply to me.

What do I need to do to find the formula(s) causing the problem?

The main culprits in these scenarios are (in order of impact):

1 - Volatile Arrays (includes SUMPRODUCT)

2 - Non-Volatile Arrays (includes SUMPRODUCT)

3 - Volatile Functions used en masse (INDIRECT, OFFSET etc...)

4 - excessive Conditional Formatting

5 - excessive Named Ranges

(though the impact of 4 & 5 is not normally a huge problem)

Have a read through:

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

in which some code is available for you to try and identify the bottlenecks

See also the above authors' own site in full:

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

EDIT: There is also of course the possibility of VBA Events being triggered - do you use VBA at all in your model ?

I'll go through the workbook and read your reference and let you know what I find. I recall none of these.

A definition of 'volatile' as it applies to Excel would help me.

Frankly, I'm wondering why Redmond would screw up perfectly fine programs like Excel and Word. All the 'improvements' seem disfunctional although the new menus are probably helpful to a novice. If I didn't have to send material to so many people with weak Office skills, I'd migrate to another product.

A Google search on "Excel Volatile" brings up some great detailed explanations of the volatility issue.

http://www.google.com/#hl=en&source=...b3655b1bbc91d8

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

In a nutshell, volatile functions are recalculating every time you change anything in your workbook anywhere...even if the function has nothing to do with what you updated, it recalculates. Most Excel functions don't do that.

Originally Posted by unionjunkman

A definition of 'volatile' as it applies to Excel would help me.

There is a link in my sig. re: Volatility (referenced by JB separately)