Aug 17 2023 02:23 PM
My spreadsheet containing a circular reference usually works, but sometimes it puts #NUM! in both cells and stays stuck like this. To correct this I enter an appropriate value in the critical cell, to break the circular reference, and a solution is provided. I then Ctrl-Z to get the formula back in the cell and get the actual answer.
--> How can I 'seed' the cell with an approximate value so there is no error?
Example: In one cell, 'performance' is the inverse of 'temperature'. If 'temperature' becomes zero, 'performance' becomes infinite and the circular reference breaks. If I set 'temperature' to 50, 'performance' becomes 0.02, the circular reference would work, and when I Ctrl-Z to change 'temperature' back to the inverse of 'performance' the circular reference works.
Aug 19 2023 09:18 AM
It would help if you would be willing to post a copy of your workbook so we'd be working with more than a description, which, no matter how extensive, will always leave some details out.
That said, in the absence of a worksheet to try this out, one thought that occurred to me is that you use data validation to "require" that temperature, for example, be a number greater than zero. Or, if it is determined by a formula, build in a conditional clause that precludes the result zero.
If you are willing and able to post a copy of the workbook, but unable to do it directly here in the forum, post it on OneDrive or GoogleDrive with a link here that grants access.