How do I define a 'seed' value for a cell in a circular reference?

Copper Contributor

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.

1 Reply



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.