Tricks With Iteration

[leinad512]

I notice that there are sometimes questions on here regarding
timestamping changes to cells and things like that. On a number of my
worksheets I solve these with iteration without using macros, yet I
rarely see this kind of answer given to people's questions. Is there
any reason why iterative formulas are bad?

Here are some examples (tested in excel 95 for the PC):
make sure that iteration is on in tools/options/calculation. For the
first two max iterations has to be at least 1, for the last it has to
be at least 2.

Timestamp a Cell:
User enters data in C1
Formula in B1: =IF(AND(C1<>B1,A1=NOW()),C1,B1)
Formula in A1: =IF(B1<>C1,NOW(),A1)
Format A1 as dd/mm/yy hh:mm:ss
A1 now displays the date and time that the data in c1 was last
changed.

Display Last Entry:
User enters data in C1
Formula in B1: =IF(AND(C1<>B1,A1=B1),C1,B1)
Formula in A1: =IF(B1<>C1,B1,A1)
A1 now displays whatever value was in C1 before it was last changed.

Count Changes:
User enters data in C1
Formula in B1: =IF(AND(C8<>B8,A8<>ROUND(A8,0)),C8,B8)
Formula in A1: =IF(B8<>C8,A8+0.6,ROUND(A8,0))
A1 starts at 0 and increments by 1 each time the value in c1 changes.

 

[Peo Sjoblom]

I would say that a VBA solution is more adaptable but it's not like it's not
been posted before

http://tinyurl.com/36cys

or website

http://www.mcgimpsey.com/excel/timestamp.html

both methods

 

[JE McGimpsey]

Not bad, per se, but they have some limitations. A couple off the top of
my head: The most important is that the setting is global, so it affect
all workbooks in a session - if you open your workbook with iterations
set *after* opening a workbook with iterations not set, you'll get CR
errors in your second workbook.


Others: they can be a pain to reset, they're somewhat difficult to
understand for many users, they take two cells instead of one, the
initial conditions are sensitive to the order in which the formulas are
entered, and, as you pointed out, the number of iterations can affect
the performance - for a designer, that's a no-no, since the user could
change the number of iterations at any time.

Nonetheless, they can be useful, which is why I have an example posted
to my timestamp and accumulator pages.

 

[Chris R. Lee]

If your application is mission-critical, you may find
validation/qualification (& convincing people who have to be convinced)
easier if you stick to formulae. I agree though that in other cases a few
lines of VBA can be neater and easier to understand.

Regards

 

[Charles Williams]

IMHO the major problem with using Iteration is that it is very difficult to
distinguish between the circular calculations that are model bugs and the
circular calculations that are meaningfull/OK.

So I suppose I reduce it to:
- very small and easy to understand workbooks: iterations OK cos you can
easily check.
- else: iterations not OK without proper error checking each time any change
is made.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[leinad512]

Not bad, per se, but they have some limitations. A couple off the top of
my head: The most important is that the setting is global, so it affect
all workbooks in a session - if you open your workbook with iterations
set *after* opening a workbook with iterations not set, you'll get CR
errors in your second workbook.

I hadn't realised that - a very good point. I had assumed iterations
were unique to the active worksheet. I suppose the only way round that
is to put in a macro that maintains the iteration setting - defeats
the purpose a bit though!

Another bad one is that to avoid the iterative formulas completely
screwing up if the input cell contains an error value they have to
made somewhat more complex.

Others: they can be a pain to reset, they're somewhat difficult to
understand for many users, they take two cells instead of one, the
initial conditions are sensitive to the order in which the formulas are
entered, and, as you pointed out, the number of iterations can affect
the performance - for a designer, that's a no-no, since the user could
change the number of iterations at any time.

Difficult to understand I agree. They are quite fun to think up though
- each one is a bit like a puzzle.

Two cells I partly disagree - I'd like to see a macro to replicate any
of those functions exactly without 2 cells (or some global persistent
data of some sort). Note that the timestamp and the change counter are
not affected if the user types the same thing into the cell as was
there last time, whereas (I think) the change event would still be
called. The last value formula would be impossible in VB without extra
persistent date wouldn't it?

Order of entry doesn't affect the formulas I gave (in excel 95 at
least), but I've certainly seen that problem with more complicated
iteration.

For me one slight advantage is that if someone opens my worksheet and
disables the macros - the functions still work.

 

[JE McGimpsey]

Difficult to understand I agree. They are quite fun to think up though
- each one is a bit like a puzzle.

True enough, and I'm interested in them as well - working out the
puzzles is one reason I spend time here.

Two cells I partly disagree - I'd like to see a macro to replicate any
of those functions exactly without 2 cells (or some global persistent
data of some sort). Note that the timestamp and the change counter are
not affected if the user types the same thing into the cell as was
there last time, whereas (I think) the change event would still be
called. The last value formula would be impossible in VB without extra
persistent date wouldn't it?

My issue wasn't with needing persistent data, which a macro still needs,
of course, but rather of having to have a second cell on the worksheet
where it's visible/accessible (and possibly confusing). True, one could
easily put it in an out of the way place.

For me one slight advantage is that if someone opens my worksheet and
disables the macros - the functions still work.

Yup - that's a definite advantage.