Aladin and Mark W - question on volatile functions
Posted by Dustin on April 03, 2001 7:40 AM
Good morning,
I was researching why my spreadsheet was taking so long to calculate, save, etc and I came across this message thread from last month.
11676.html
You two were discussing the volatile functions and their impact on causing sheets to recalculate after every data entry.
Here is my dilemma:
I have a spreadsheet that is quite large (4MB) by my experience, containing 7 tabs that all pull information from a workbook that is not the same as the spreadsheet being discussed here. There are roughly 3 to 4 thousand cells (on the 7 sheets) that contain formulas to calculate values.
Any time I try to open, save, or close the workbook it takes several minutes at the very least.
Almost all formulas contain the TODAY() function for the purpose of calculating if there should be a value displayed in a cell or not depending upon if the date has actually passed or not.
Example: (Mark W - I have tried to give you a representative set of sample data this time ;-)
---------A----------B-----------C-----------D---------
1---------------1/5/01-------1/12/01-----1/19/01------
2--Reqs Rec'vd----------------------------------------
3--Submittals-----------------------------------------
4--Interviews-----------------------------------------
The TODAY() functions compare today's date with the date at the top of the column and display the appropriate value if today is greater than the date at the top of the column or display a blank cell if today is less than the date at the top of the column.
I am quite sure that I have not designed the spreadsheet to be the most efficient anyway, but in your expert opinion(s) would the high volume of volatile functions cause this sheet to take a long time to calculate, and if so, could I just use one TODAY() formula in a single cell on each sheet and then have all of the other formulas reference it to minimize the recalculation (open, close, save, etc) time?!?!?!
Sorry for being so long-winded.
Thanks in advance.
Dustin
Posted by Aladin Akyurek on April 03, 2001 8:22 AM
Hi Dustin
TODAY() is indeed one of the volatile functions that gets re-computed whenever something is changing in the workbook. Since you said that you have a TODAY() in some cell on every sheet, I'd urge you to create another sheet, say BLACKBOARD, enter =TODAY() in A1, and name it CurDay. Then go to all formulas where you have a reference to TODAY() directly or indirectly and replace them with CurDay. Remove the "=TODAY()" from all other worksheets.
BLACKBOARD is a nice place to put all your constants and intermediate computations, to which you should attach meningful names and use these names in all of the relevant formulas.
Do you have other formulas with volatile functions?
How about VBA? Lots of VBA slow also a spreadsheet model.
Aladin
Posted by Dave Hawley on April 03, 2001 8:59 AM
Hi Dustin
If you have that many formulas within a single Workbook you should definately try and use only the one TODAY() formula. Then reference all cells to this.
One of (if not THE)the worst offenders though for slow saving, recalculation etc are Array formulas. Try and get the hang of Excels database formulas e.g DSUM, DCOUNT etc. These are fairly simple to use and are far better in most cases than array formulas.
Pivot Tables are another great way to eliminate the need for many formulas.
The bottom line though may be that it is time to move into VBA, this can automate Workbooks and keep the file size to less than 70% in a lot of cases.
Dave
OzGrid Business Applications
Posted by Dustin on April 03, 2001 9:26 AM
Thanks for the ideas Dave. I will see if I can use some of the database formuals...
Posted by Dustin on April 03, 2001 9:29 AM
Aladin,
I will try the Blackboard concept. As far as VBA, I do not have much in the workbook at all. As for other volatile functions, not many in this particular workbook, but many in the workbooks from which this book pulls its information. I will try to streamline those as well.
Thanks for the great idea.
Dustin
