Functions work fine, then don't

Trying to simplify an Excel file that I've inherited from an long-gone employee. No one can get it to work so I'm trying to strip down all of the fancy functions and just leaving functions that are actually performing calculations.

I am having some issues with stability. When I first enter the functions and formulas into the worksheet it seems to work well. The numbers look correct, no errors. But when I click into a cell and click out of it, the correct value then changes to a zero.

Any advice is appreciated.



EDIT - SOLVED! I found the answer as a Google search pointed me to another post in this forum. It was just a matter of enabling iterative calculations.

Just an observation. The fact that iterative calculations are seemingly necessary to process yuor formula suggests that there are circular references somewhere.

In general circular references should be avoided. They may be necessary in some circumstances but in my experience for most tasks there are ways of avoiding them so it may prove worthwhile if you were to identify and change methodology as necessary. Upload the workbook here if you need specific help.

You are correct. The file uploaded is a .xlsx file not a .xlsm file which would contain macros.

But are macros relevant to your request which was essentially one about iterative calculations?

The file uploaded does not have any circular references so it doesn't exhibit the behaviour you describe.

The UDFs appear to be stored in an add-in and not in this file, so, no the UDFs did not upload with your file. I replaced all of the UDFs with a SUM() function, and (after resetting the errors) that allowed the spreadsheet to calculate and converge on something (obviously not the correct answer, but maybe it correctly represents the dependencies well enough??).

This appears to have something to do with HVAC engineering, and probably will require some expertise to understand exactly what you are trying to calculate here and exactly how the math/engineering calculation needs to be done. Many engineering calculations require iterative methods like this, but it often requires that engineering expertise to know when the circular references are required and when they are not. It seems likely that we will need a quick engineering lesson from you before we will fully understand what is going on here.