How many iterations were needed to solve a problem?

[Gary]

When using iteration (circular references with the iteration option
checked) is there any way of determining how many iterations were
needed to reach a satisfactory answer?

Some spreadsheets that I used (a long time ago) used to have a
variable that stored the number of iterations, and the variable could
then be used in further calculations.

I can't see how to get this information in Excel. Is there a way of
doing it?

Gary

 

[Shane Devenshire]

Hi,

You could set up a macro that counts the number of Calcs. Here is a sample:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
myCount = myCount + 1
End Sub

Add this to the thisWorkbook module in the VB Editor. Then add this in a
regular module:

Public myCount As Long

Sub ShowCount()
MsgBox myCount
myCount = 0
End Sub

After you run your calculation run the ShowCount macro. It will show how
many times the sheet was recalculated.

 

[Gary]

Hi,

You could set up a macro that counts the number of Calcs.  Here is a sample:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    myCount = myCount + 1
End Sub

Add this to the thisWorkbook module in the VB Editor.  Then add this ina
regular module:

Public myCount As Long

Sub ShowCount()
    MsgBox myCount
    myCount = 0
End Sub

After you run your calculation run the ShowCount macro.  It will show how
many times the sheet was recalculated.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire








- Show quoted text -

Thanks - I'll try this out.

Lance

 

[Niek Otten]

I'm afraid it will not, Shane.
At least not when I tried.
I suspect Goalseeks are not considered "normal" recalculations and the event
is not triggered.
Or did I do something wrong?

I tested a solution once where I called a counting function and made it both
precedent and dependent in the recalc tree, so I could be sure it would be
called for each iteration.
I suppose I can find it again, but maybe that isn't necessary if you point
me to what I did wrong in your approach.

To Gary: indeed, in Excel's predecessor, Multiplan, there was an item called
IterCount or IterCnt. I used it several times to control the iteration loop
or research better custom iteration mechanisms. I can't remeber any further
details though and of course (and unfortunately) I lost the code since then.

 

[Charles Williams]

I think this works, but I have not done exhaustive testing. Its a UDF that
will return the number of iterations.
The aCircularCell reference MUST reference one of the cells in the circular
calculation chain.
It relies on the fact that Application.Iteration seems to be False in the
initial part of the calculation when Excel is determining if there are
circular references, and then Application.Iteration becomes true when Excel
has determined that there are circular references and has switched to its
algorithm for resolving circular references.
Not sure what happens if you have more than one circular chain!

Option Explicit
Dim IterCount As Long

Public Function IterCounter(aCircularCell As Range)
Dim var As Variant

var = aCircularCell
If Not Application.Iteration Then
IterCount = 0
Else
IterCount = IterCount + 1
End If
IterCounter = IterCount

End Function

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[Gary]

I think this works, but I have not done exhaustive testing. Its a UDF that
will return the number of iterations.
The aCircularCell reference MUST reference one of the cells in the circular
calculation chain.
It relies on the fact that Application.Iteration seems to be False in the
initial part of the calculation when Excel is determining if there are
circular references, and then Application.Iteration becomes true when Excel
has determined that there are circular references and has switched to its
algorithm for resolving circular references.
Not sure what happens if you have more than one circular chain!

Option Explicit
Dim IterCount As Long

Public Function IterCounter(aCircularCell As Range)
    Dim var As Variant

    var = aCircularCell
    If Not Application.Iteration Then
        IterCount = 0
    Else
        IterCount = IterCount + 1
    End If
    IterCounter = IterCount

End Function

Charles

Interesting. Thanks for this. I'll go and try this too.

Gary

___________________________________