Optimization when Iteration (Circular References) is involved in the formulae

Question

I have a system of equations set up that is based off of two variables and must be solved iteratively because it involves a circular references. When I try to maximize a value in solver, I keep getting an error (Div/0). I'm assuming this is because the Solver isn't iterating through the formula for each value it tries. Is there anyway to tell it to do this, or can I not use Solver for formulas that involve circular references?

Answer 1

Excel is dumb. If you ask it to handle a truly circular reference, it can't.  You have to break the circle.

This article describes how to break the circle:

Circular References: Calculation, Detection, Control and Removal https://fastexcel.wordpress.com/2015/09/14/excel-circular-references-calculation-detection-control-and-removal/

Excel circular references occur either when a formula refers to itself or when a chain of formulas links back to it’s starting point.

Most of the time circular references are created by mistake (unintended circular references) and should be corrected. They can also be used to solve iterative or goal-seeking problems in Finance and Engineering.

If you want to do an iterative calculation, use Solver or a function that limits the looping in it's logic so it is not an unbound circle