Some cells won't recalculate when their dependents have changed value

[pwrmnd]

I've started having a problem where Excel won't recalculate the values of cells that depend on other cells (which have changed value). The “recalculate all” command doesn’t do anything for these cells, even though it runs for a minute or so, as usual. It is updating SOME of the cells but, unfortunately, have some correct values isn’t good enough. Even CTRL + ALT + SHIFT + F9 doesn't update the values, despite running for a long time (many minutes), apparently doing something. I’m really surprised that one didn’t work.

Sometimes I can get the cells to update simply by recalculating ONLY that sheet (not the entire workbook). It’s weird that refreshing the sheet works when refreshing all the sheets does not.

Other times, the only way I can get the naughty cells (sorry if that’s not the technical term) to actually update is to manually enter the formula again. I don’t need not to change the formula; I just go into the editing bar and hit return. Then the number magically updates like it’s supposed to. However, the other cells in the sheet still won’t update. I have to basically paste the formulas over again to get into work.

Some context: my workbook has an unusually large number of sheets (over 700...I feel it makes my life much easier having multiple sheets rather than combining them into a single sheet) with a lot of formulas. It takes about 90 seconds to recalculate the entire sheet on a decent laptop. The file size is about 100MB.

I have it set to manual calculation, for obvious reasons. But I get the same problem even when I switch to automatic calculation.

I do have some circular references, with the number of iterations set to 3 (had the same problem when set to not iterate at all). I was guessing that the problem is related to this, but I’m not sure.

Now I have no idea whether I can trust any of my Excel data. Can anyone help?

P.S. I did try using the repair command upon opening the file. That didn’t work.

[¯\_(ツ)_/¯]

While there doesn't appear to be a set limit on the number of calculations Excel can perform at once, you may be running into one of the other limits within the program, e.g. max # of cell formats, or "available RAM". See:

https://support.office.com/en-us/art...AA=Office_2010

[joeu2004]

I do have some circular references, with the number of iterations set to 3 (had the same problem when set to not iterate at all). I was guessing that the problem is related to this, but I'm not sure.

Yes, that is very likely the problem.

CRs are a bad thing; a "bad programming practice", IMHO.

They might okay when they are self-referencing, or when there are just two cells involved. (Even then, I never use CRs.)

But where there is a chain of 3 or more cells, in many cases, Excel does not know when to stop. And when it stops, the results are likely to be inconsistent. After all, there are no "ends" in circles.

I avoid CRs by duplicating calculations and by relying on helper cells as needed. I have never encountered a situation where CRs are the "only" way to implement the calculation. In fact, honestly, the thought never crosses my mind. I very familiar with "recursion". So I immediately seek an alternate solution.

PS.... And for problems that can only be solved by iteration -- Excel IRR when pmt <> 0, for example -- I implement the iteration in a VBA function.