Excel 2010 Not Calculating

[lauser]

I'm working in Excel 2010 with a file (6.93MB) that is not calculating. The calculation settings are set to "Automatic", I've tried Ctrl + Alt + F9, Alt + M + B, etc., but whenever I do that, in the bottom right it will show "Calculating: (4 Processor(s)): 0%" and the "Calculate" label is constantly showing in the bottom left. When trying to save the file, the same thing happens, but the calculations never get beyond 0%, so I can only save the file without recalculating. I tried restarting my computer, and I tried recalculating in a separate file which worked fine. Just seems to be this one file and have no idea what would be preventing the workbook from ever recalculating. I'm talking about leaving it for minutes and it never gets above 0%, and at 6.93MB that just shouldn't be the case.

I've even tried opening an older version of this file and that works fine. All I've been doing today is updating and adding formulas, but don't understand why that would impact the workbook's ability to recalculate.

Any help would be appreciated!

 

[FDibbins]

Hi, welcome to the board

If it is taking that long to calcs, sounds like it is running some serious formulas there, maybe even a circ ref. What sort of formulas are you using? Care to share some?
What happens if you set calcs to manual?

 

[lauser]

The formulas in general are not too intense, but there are certainly a lot (thousands) of formulas running in the model. There are no circular references in the workbook, though I did try enabling iterative calculations and recalculated the workbook (it ran through the iterations), but with that enabled you always see "Calculate" in the bottom left. I then unchecked "Enable Iterative Calculations" and there's no message about circular references, and still always seeing "Calculate" in the bottom left regardless of the calculation type. Manual calculation works as it should, but I want to recalculate the workbook so that setting doesn't help me. The problem is with recalculating the workbook.

Not sure if it's one of those things where there were too many changes and there aren't enough system resources to save the file, but don't think this file is big enough, I save my work frequently as well so shouldn't have been too many changes to save.

 

[FDibbins]

What sort of formulas are you using? Care to share some?

If you have 1000's of formulas, what they are my be important.

If you have formulas based on old data that wont change, consider "valuing" some of them

 

[lauser]

Never mind - was able to solve it. Think there may have inadvertently been circular references in one of the sections I had changed, but since the workbook wasn't recalculating it wasn't identifying that as a problem. I hard-coded one section of formulas that I had recently changed and now everything is working!

 

[Michael M]

Do you have any worksheet macros running ??
How much data is in the workbook....sheets / used cells ?

 

[lauser]

Not sure if this could be it, but the formulas causing the problem were within the ROUND() function, so not sure if that adds to the complexity of excel identifying that as a circular reference

 

[Michael M]

sounds more like what was within the ROUND, rather than the ROUND itself !!

 

[lauser]

sounds more like what was within the ROUND, rather than the ROUND itself !!

Yep, think that's true, but would expect excel to highlight when there are circular references, which it did not.

 

[Michael M]

Are you sure it's a circular rather than an infinite calculation.
Maybe if you posted the formula ??