Iterations

[antonje]

Hey,

Can someone explain an iteration to me in layman's terms?

I built a financial model that is circular (net income --) cash t
service debt --) interest expense --) net income), which I understan
is pretty common, but do not understand how excel solves a circula
formula.

Peac

 

[Boo]

Iteration = a problem-solving or computational method in which a succession
of approximations, each building on the one preceding, is used to achieve a
desired degree of accuracy.

i.e. Excel takes a "best guess" at the correct result and repeats the
computation X number of times to refine the accuracy of the result (X being
the number of iterations)

 

[JLatham]

Try to find a way not to use circular formulas if at all possible. But if
you must, basically Excel performs the circular calculations up to the
maximum number you have set for Iterations or until the change in value is as
small as you've set to detect.

If you want to see how weird things can go with circular formulas, use Tools
Options to permit iterations with the default values and put these values or
formulas onto a sheet
in B1 enter =(A1+C1)*2
In C1 enter =B1/2
Now you've already created a circular reference, but since both B1 and C1
are zero to begin with, things look alright. Now type 6 into A1... WHOA!
1200 and 600. Gets even messier if you move the 6 around (dragging the cell
to other locations) or change the value in A1 (without having moved it).