|
Advanced features 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 table) 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:
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 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. 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. If in addition to a counts cell you
also have a cell named counter, this will be reset to zero
by Running totals 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.
|
to copy this down to the bottom of the table.
to see the spreadsheet recalculated over and over
again.
switches iteration on and 
switches it off.
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').