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:

• enter =lastAmount+Interest

As this illustration shows this is much more understandable (and easy to debug) than using cell references:

Iteration and random (Monte Carlo) simulations

Random numbers

The Excel function Rand() is useful for generating (pseudo)random numbers that you can use in simulations.

Keep recalculating

Click to see the spreadsheet recalculated over and over again.

Iteration

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:

• first click the Switch iteration on button
• and then download and open the demonstration spreadsheet Iteration.

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).

Running totals

If you want to keep a running total of some quantity in a cell named total (say), use a formula like this:

=if(counter=0,0,total+quantity)

Notice that this formula is in the cell named total, so it refers back to itself, so creating a circular reference.