Calculation takes longer than Full Calc time

[Jeff Whitfill]

I have a complex model that I am trying to troubleshoot for speed issues. Was hoping to use the Excel Profiler to do that. When I run the Profiler, it says the full calculation time for my workbook is 76 seconds. Yet if I do anything, delete a row, copy and paste text, etc. it takes 5 or more minutes to complete a task.

I researched this and found out that this can sometimes be caused by the Excel Calculation Engine / Dependency Tree. I changed the Force Full Calculations option in the Visual Basic window and now the calculation time is pretty close to the 76 seconds that Fast Excel says. 

Long story short, is there a way to use FastExcel to determine what may be causing my performance issues with the Dependency Tree?  What could cause the Dependency Tree calculations to take 5 times longer than recalculating the entire workbook?

I've run the Workbook Cleanup, deleted invalid / hidden named ranges, and there are no volatile formulas. I do use named ranges and tables (not data tables) quite a bit but I can't find anything that suggests that these would be slowing things down.

Hoping FastExcel can help me somehow!

Thank you!

[Charles Williams]

Hi Jeff,

You can change Force Full Calculation using the FXL Calculation Settings -> Workbook Tab

Force Full Calculation skips dependency tracking. This has these side effects:

- each calc is a Full Calculation so each recalc is generally slower

- editing is faster

- the workbook opens faster (XL rebuilds the dependency tree at workbook open time)

- the Status Bar always shows "Calculate"

Any time you make a change like deleting a row Excel has to do 2 things:

- check all the formulas in the workbook and adjust any references that are affected by the change

- rebuild the dependency trees. This is done with a left-to-right top-to-bottom scan of the worksheets (in alphabetic order?)

Because it is fairly unusual to find cases where Force Full helps I don't really have much data on how to alleviate the slow dependency tracking.

But presumably it has something to do with the way the workbook is structured and the dependency flows between formulas and worksheets.

(It is not the total number of formulas that is the problem)

1) I would try Profiling Cross Refs and choose the Optimise worksheet sequence option to see if that helps

2) See if you can find worksheets with a lot of formulas that reference backwards to other formulas rather than following the left-to-right top-to-bottom pattern.

Let me know if you find anything interesting that works or does not work!

[Jeff Whitfill]

Thank you for the reply.

I tried your suggestions #1 and it didn't measurably help. I am working on going through the file to check for #2, but honestly I have references to everything everywhere.

It seems to be a combination of an issue with multiple tabs. I went one by one pasting each tab as values until the file was no longer slow. On about the 13th or so tab, the calculations went from minutes to instantaneous. So then I went back to the original file and pasted the 13th tab as values, but it was still slow. So it is some combination of tabs causing the issue.

Anyways, I appreciate your help and input and I will let you know if I figure anything out that I think you would be interested in.

Thank you!

Jeff