Advanced modelling features
Equally spaced values in a column
You will often want to fill a column with equally
spaced values - an easy way of this is to define parameters named
max and min (for example), then:
TIP Whenever you create a table the
special name Steps1 is always equal to the number of rows
in the table less one (or Steps2 in the case of a second
Step by step calculations
You may often find yourself wanting to write
models where the formulae in each row refer to values in the row
above. It would be great to be able write a formula to do this without
having to use cell references. ImagineIT allows you to do this.
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
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
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'
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.
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
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.