Workbook constantly wants to calculate

[Johnny C]

This isn't a problem, just curiosity.

I've got a reasonably large Excel forecast model (10Mb). I recently rebuilt the model to FAST standards, so it now uses a ton of SUMIFS over 1200 cell ranges and these feed a lot of other sheets. Calculation is Automatic.

At bottom left, where it says 'READY', it permanently says 'CALCULATE' as well. Even if I do a CalculateFullRebuild, it says CALCULATE. It didn't before I rebuilt it.

When I run a timer the time for a Calculate is the same as for a CalculateFull. This suggests Excel has just decided that there's too many formulae and links to keep track of changes when it checks the calculation tree to see what needs to be calculated by a base Calculate so has given up.

Is there some limit that has been exceeded so Excel can't keep track of what's been changed? it takes just over a second for a Calculate/Full and 5 secs for a calculatefullrebuild.

PS I know it shouldn't be in Excel but they don't have a forecasting tool and no appetite for even an Access Db, and the Risk policy is that models should conform to FAST.

 

[thisoldman]

There has been a bug in the status bar display of Calculate since Excel 2007. See:

https://fastexcel.wordpress.com/2013/12/19/excel-2013-sdi-bug-calculate-in-status-bar-strikes-again/
http://www.decisionmodels.com/calcsecretsf.htm

 

[Johnny C]

Cheers. It's not the bug, as I thought the workbook has exceeded tracking limits

The number of different areas in a sheet that may have dependencies is limited to 65,536.
The number of cells that may depend on a single area is limited to 8K.

It blows both limits out of the water, 250K dependencies on 1 sheet alone (and there are 9 such sheets), and the number of cells that depend on one area is at least 20k, and it counted 300k links between sheets (which took 12 hrs to work out that will teach them to insist on FAST...)

 

[Joe4]

Yes, I have had co-workers run into that before. Excel can only track so many formula dependencies. Once you exceed that, it is forced to do a complete re-calculation with every change, and it really bogs the workbook down.

I have never run into that issue myself. As I told my co-worker, if you run into this issue, it is a sign that you are using the wrong program for the task (which you already know). In their case, they were using Excel as a database, and had about 10 sheets of a couple hundred thousand rows of data that referenced each other. I told them that is what Access is designed for!

I don't know the intimate details of your project, but perhaps if they are unwilling to let you have what you want, you may want to see if Access is a better solution for you too.

 

[Johnny C]

Unfortunately I'm a contractor brought in to create this financial model, it's something I need to hand over to them when I leave.

I think they have had some bad experiences with contractors before, leaving them with things they can't maintain. Hence VBA or Access aren't allowed, it's enshrined in Risk & Compliance manuals and all models have to be signed off as compliant.

They are doing exactly what you encountered, using Excel as a database. It's a shame, even if they can't use Access I could write VBA to upload tables of data from Excel into Access and then query it so they didn't even know Access was involved (I've done that before).

Just saw your signature

TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access

I'm using Excel 2013 and the excellent offline help seems to have gone. Maybe the people here didn't load the Excel VBA help, but even the regular offline Excel help seems to be dumbed down. Places seem to be increasingly restrict access to sites like this and stack overflow, and mobile phones are often disallowed from the office, which all leaves you a bit stuck without good offline help.

 

[Joe4]

They are doing exactly what you encountered, using Excel as a database. It's a shame, even if they can't use Access I could write VBA to upload tables of data from Excel into Access and then query it so they didn't even know Access was involved (I've done that before).

I would point that out to them - Excel is NOT a database program, so doing something like this (creating a database in Excel) of large magnitude is probably going to be clunky and poor performing.
If not Access, they may want to consider other database programs like SQL or even the free MySQL. Of course, you still need to have a front-end for those.

It reminds me something that a fellow-MVP used to say about using the wrong tool for the job - "You could use a wrench to drive in a nail, but I wouldn't recommend it"