Calculate option at the bottom left (excel 2010)

[Pofski]

Hi everybody.


I was just wondering if somebody would know why the option to calculate that is located at the bottom left of the screen (underneath the tabs, next to the word ready) sometimes is visible, and sometimes isn't.


At this moment i have a pretty big calculation sheet, and when i change certain options, the different charts and tables on the dashboard change (well, they should)


Now, maybe it's because it's too much data, but sometimes, the charts aren't correct, and i have to let excel recalculate by using the calculate option (with f9, it doesn't seem to work, and i have the workbook calculation on automatic in the options menu)


After i use the calculate option though, everything is fixed.


Now, if the option to calculate isn't there, then there's a big problem, because the numbers can't be trusted.


Thank you in advance

 

[jeffreyweir]

Can you post a screenshot? I'm not exactly sure what you're referring to. Also, what version do you have?


In Excel 2010, on the right hand side in my version the word 'calculating' might show, depending on how many formulas Excel is currently recalculating. Maybe you have another version that has it on the left? If this is what you are referring to, here's the answer why it shows only sometimes.


There might be several hundred thousand formulas – or even several million – in a large Excel model, with long chains of dependencies that might run across worksheets or even workbooks.


Excel determines dependencies by looking at the cells referred to by each formula and by the argument list of each function. Excel then updates this dependency tree whenever a formula is entered or changed, which means it can then smartly recalculate a subset of formulas affected by those dependencies, rather than the whole workbook (which would take for ever)


If you change a formula that has many thousands of cells 'downstream', then the word 'calculating' will show while Excel works its way through the dependency tree, until it's finished recalculating each of those cells.


But if you change a formula that only has a few hundred or thousand cells downstream, then - and if you don't have any Volatile formulas in your workbook that have large numbers of functions downstream of them - the recalculation happens so fast that this word doesn't even show.


So what functions are volatile, and what does that mean? THe main ones you'll come across are OFFSET, TODAY, NOW, RAND, RANDBETWEEN and a few other functions.


If you have volatile functions in your workbook, any time you make a change anywhere at all on the spreadsheet, Excel recalculates the value of all the volatile functions too. Excel then recalculates every applicable formula downstream of these functions too – regardless of whether anything upstream actually changed or not.


That last part – “regardless of whether anything upstream actually changed or not” – is worth a demonstration.

• Say you have the function =TODAY() in cell $A$1. Obviously that value is only ever going to change once per day, at midnight.

• Say you have ten thousand formulas downstream of $A$1 – that is, they either refer directly to $A$1 or to one of $A$1’s dependents. Those ten thousand formulas will get recalculated each and every time any new data gets entered anywhere on the spreadsheet, even though the value of $A$1 itself only changes one per day!


For this reason, too much reliance on volatile functions can make recalculation times very slow. As Charles Williams (Excel MVP who is an expert on worksheet bottlenecks) "Use them sparingly. Try to get out of the habit of using them at all". In fact, there are usually alternatives to every volatile funciton (sometimes requiring VBA code), including INDIRECT.

 

[Pofski]

Hey Jeffrey


i hope the image in this link can shed some light on what i'm talking about.


https://docs.google.com/file/d/0B0naOkYo4pCmQzRuZ0Z2aHB1Nms/edit?usp=sharing


I just find it really weird that excel doesn't show the data correctly without having to recalculate it all.


i tried to stay away from volatile formula's because i was thinking that when the complete implementation of the data (at the moment we have it limited to +/- 200.000 rows) would slow it down tremendously.


I don't have any array formula's and i don't have any macro's in the workbook, so i would think that that would also be ok, and i try to do calculations according to the principle of "it's better to do a reference to a cell then to let it calculate a complete array" (trying to avoid the sum where possible, but have to use it on occasions, because there's no other way).


Because of your suggestions i just stay clear of anything like INDIRECT, and just look for alternate ways to do them.


Sincerely

 

[bobhc]

Good day Pofski


It all depends as to what is going on in your work books as jeffery has pointed out


The Calculate message may remain in the status bar when you enter formulas that include cell references

applies to:

Microsoft Office Excel 2003

Microsoft Excel 2002 Standard Edition

Microsoft Excel 2000 Standard Edition

Microsoft Excel 97 Standard Edition


This link may be of help


http://support.microsoft.com/kb/243495

 

[Colin Legg]

Five reasons for calculate in the status bar are listed here:

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