Excel - Recalculate workbook before Saving

Post Reply
CiskoWalt
Posts: 46
Joined: Thu Oct 16, 2008 4:02 pm

Excel - Recalculate workbook before Saving

Post by CiskoWalt »

Hello,

I am using TM1 9.5.2 Fix pack 3, with Excel 10, 32- bit.

It can take a long time (10- 15 minutes) to save a Financial model when the following 2 conditions are met:

1.There were a significant number of changes made to the model. For example, including inserting new Worksheets or a refresh of data.
2.Excel’s ‘Recalculate workbook before saving’ is selected.

It takes 21 seconds to perform a full (F9) calculation of the model.

Is there something wrong with the financial model or is this just expected behavior with TM1?

This is not an issue with saving a file to a share on our LAN, since I can reproduce the issue saving the workbook to the local PC.

Resolution:
Do not click recalculate before saving.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Excel - Recalculate workbook before Saving

Post by Alan Kirk »

CiskoWalt wrote: It can take a long time (10- 15 minutes) to save a Financial model when the following 2 conditions are met:

1.There were a significant number of changes made to the model. For example, including inserting new Worksheets or a refresh of data.
2.Excel’s ‘Recalculate workbook before saving’ is selected.

It takes 21 seconds to perform a full (F9) calculation of the model.
Recalculate Before Save is a pain; I understand the reason for it being there but I hate the thing and always prefer that it's off; it slows me down. It has always seemed to me (though I admit that I haven't tested this objectively) that a save with "Recalculate Before Saving" is slower than a comparable [F9] calculation, though maybe not by the magnitude that you're citing. However it's worth noting that an [F9] calculation is not in fact a full "full calculation". That would be [Ctrl] [Alt] [Shift] [F9] which does a dependency tree rebuild as well. So the question arises... does the Recalculate Before Save option do the former, or the latter? If you're changing the sheet structure it's entirely possible that Excel will decide that it wants to do a full calc + dependency tree rebuild (given that the new sheets can have an effect on the tree), though we'd probably need a mole in the Redmond campus to know that for certain one way or another. However it would tend to explain the huge difference between the two. The next time you do a stack of changes it may be worthwhile to do a Ctrl-Alt-Shift-F9 and see what the time is on that.

As to other possibilities; obvious question, but do you perchance have either:
(a) Other workbooks open; or
(b) Links to other workbooks that might have arisen from the changes that you made, even ones that you may not have realised are there? To check the latter I recommend downloading Bill Manville's FindLink add-in.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Post Reply