Let's say you have two columns that are related to each other by a simple formula. Perhaps Column A has monthly expenses and Column B has the expenses annualized. Now, for some rows you may want to enter the annual expense and have the monthly expense calculated, and other rows you may want to enter monthly expenses and have the spreadsheet calculate the annual expenses. One solution I have is as follows:
Column A Formula
Column B Formula
Ignore for the moment that I should probably be using
ARRAYFORMULA which I only just discovered in searching for an answer to this question. The above two formulas will form a circular dependency.