For example you might want to add interest each month to an amount of money in an account and calculate a new amount. If you had columns named Amount and Interest, naming a third column lastAmount would automatically cause it to fill with the values in the Amount column, but one row up each time. You could then use the following simple formula in the Amount column:
As this illustration shows this is much more understandable (and easy to debug) than using cell references:
Iteration and random (Monte Carlo) simulations
The Excel function Rand() is useful for generating (pseudo)random numbers that you can use in simulations.
Click to see the spreadsheet recalculated over and over again.
Excel also has an Iteration facility (under Tools/Options/Calculation) which enables formulae in cells to refer back to themselves, either directly or indirectly. If you don't have iteration switched on, this creates a 'circular reference' error.
Iteration is useful for keeping running totals of quantities and for solving mathematical equations by iterative methods and ImagineIT makes it easier to use.
switches iteration on and switches it off.
To try out the three 'Iteration' buttons work and see how they work:
Iteration with and counts cell
In addition, if you give one of the parameter cells the name counts, then it will automatically count for you the number of times that the spreadsheet has recalculated.
resets counts to one. The Calculate button (or pressing f9 on the keyboard) causes one recalculation to occur. (If you asked for several iterations at once when you switched iteration on, then read that number for 'one').
If in addition to a counts cell you also have a cell named counter, this will be reset to zero by and then count up from there (often more useful).
If you want to keep a running total of some quantity in a cell named total (say), use a formula like this:
Notice that this formula is in the cell named total, so it refers back to itself, so creating a circular reference.