Ensuring All Formulas Calculate

[rawraw]

Hello!

I have a workbook where ~15k sumproduct formulas. Most of the time, these formulas refresh as expected. But sometimes Excel recalculates everything but it is clear some cells have not calculated. If I go in and press F9 or press enter, they often refresh. But saving or press calculate sheet does not appear to generate a value. Is there anyway to ensure these formulas are working as expected?

I'm currently storing a lot of data in an External workbook. I use sumproduct to pull the data from that workbook to the workbook where I need the data.

[Rev12]

Can you attached a sample of your worksheets so that any can figure out what you did though and give you more ideas with the experts around.

regards,

Rev12

[Zer0Cool]

The most likely scenario is you are interrupting the calculation process. When the calculations occur, you should see at the bottom right corner in the status bar something like "Calculating (8 processors): 45%", where the wording may be a little different but the gist is it gives you progress on how long calculations are taking and when they are complete. Any actions taken during this can interrupt or delay the process, especially when the calc chain is complex (IE: when many formulas require other formulas to calculate before they can).

So if you recalculate, then go and do something like select another cell, move to another sheet, enter more data, etc. while this is happening you may see the % start at 0% again. If that has happened its possible its restarting all over again.

The only other cause of some formulas not calculating/updating that I have seen is corruption in the file. If the file has excess content (cells, styles, formulas, named ranges) and/or actual corruption it can affect parts of the file like this. Thats harder to check for and evaluate much less fix. Hopefully its caused by my first point in which case you either wait for the process to complete or take actions to reduce the time it takes.

[FDibbins]

If you have 15k+ sumproduct formulas, that is in all likelihood slowing your file down, and they can probably be replaced using less resource-intense formulas and/or helper columns to speed things up

As requested. upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.