Circular Reference get the Current Iteration value and number of iterations to converge

[Matt_Kelly]

I have a spreadsheet that is using a circular reference to solve a thermodynamics problem under varying conditions.  99.9% of the time, it converges to the solution relatively quickly.  However, for the 0.1% of the time it doesn't converge, it is because there is no solution under those conditions.

What I would like to do is to capture the value of the current iteration, then if it exceeds a certain value, apply a corrective factor to my equations (this works when I manually enter the corrective factor).

I have been able to build an counter that will tell me what iteration it is on then stop when my calculation has converged, however the counter itself is a circular reference so the sheet goes on iterating until my maximum number of iterations.  This will slow down my simulation when I'm getting the results for thousands of different conditions.  It will iterate 1,000 times even though the calculation converged after 10 or 12 iterations.

What gets me though is that when the calculation doesn't converge, while the sheet is iterating, there is an iteration value displayed in the status bar that looks like this "Iter: 245" and I can watch it updating by steps of 10-20 iterations.  If I could just grab that value and use it in my sheet, I think my problem would be solved.  I feel like I should be able to do this using a user defined formula or macro.

Thanks,

MK

[#2]

I have the same urge. A large complex sheet with iterations that I want to run in several scenarios (data tables). But I have no way of telling if any of the scenarios failed to converge.

If I could just get a flag that told me wether the iterations converged (TRUE/FALSE), I would be happy. This should be easy for the Excel engine to provide, but I have yet searched in vain.

[#3]

@Matt_Kelly 

It's not possible to directly access the iteration value displayed in the status bar during circular calculations in Excel. However, there are alternative approaches to achieve your desired functionality:

1. Using Goal Seek:

• Setup:
  • Define a cell (e.g., "C1") to hold the desired outcome of your calculation (e.g., pressure, temperature).
  • Set up your circular reference equations to calculate this value.
• Goal Seek process:
  • Set the "Set cell" to the cell containing the desired outcome (C1).
  • Set the "To value" to the expected result you're looking for under specific conditions.
  • Set the "By changing cell" to a cell within your circular reference loop (choose a cell that significantly affects the outcome).
  • Click "OK".

Excel will automatically adjust the chosen cell within the circular reference loop until the desired outcome in cell C1 is achieved. This effectively mimics the iterative process without displaying the iteration count.

2. VBA Macro:

• Concept:
  • Write a macro that triggers on a specific event (e.g., change in a cell) to:
    • Run your circular calculations using a loop.
    • Within the loop, check for convergence criteria (e.g., change in outcome value is below a threshold).
    • If convergence is not met after a certain number of iterations, apply the corrective factor and continue iterating.
    • Once converged or maximum iterations reached, stop the loop and display appropriate results.

3. Solver Add-in:

• Setup:
  • Define your objective function (e.g., minimize error between calculated and desired outcome).
  • Set up constraints based on your equations and limitations.
  • Choose the "GRG Nonlinear" solving method, which can handle circular references.

Solver will then find the optimal solution that satisfies your objective and constraints, effectively mimicking the iterative process without displaying the iteration count.

Choosing the best approach:

• Goal Seek: Simple and efficient for single-goal scenarios.
• VBA Macro: Offers more flexibility and control over the iterative process and convergence criteria.
• Solver Add-in: Suitable for complex optimization problems with multiple constraints.

Remember that using circular references can impact performance and stability. Consider alternative non-circular approaches if feasible for your specific problem

[#4]

Thanks for your input, @smylbugti222gmailcom.

They are very useful if you have a single (or few) iteration loops in your worksheet.

In my case I have a large number (100+) of non-similar iteration loops in the same worksheet. I am actually amazed about Excel's capability to handle this smoothly. I am seeking a way to check whether Excel's convergence criteria (set in Settings->Formulas) were met for all iteration loops.

Making individual checks for each iteration loop (or even identifying them all) is not an option in my case.

Ideally I would like to see a VBA property called worksheet.converged (boolean) that would tell me whether the worksheet managed to converge since the last change. Or a similar functionality.

[#5]

@Matt_Kelly 

Depending on how complex your calculations are it may be possible with a recursive Lambda (Limit is about 2,000 iterations which decreases with the number of parameters in the function.).

[#6]

@ClausHindsgaul 

Hi,

I think recursive lambda should be enough to handle simple root searching scheme such bi-section root searching.

I use Excel's built-in NORM.DIST function as an example.

e.g Finding x s.t NORM.DIST(x, 0,1, TRUE) = y use bi-section.

[#7]

I was not clear on the complexity. It is a workbook with 40+ sheets containing 100+ circular reference circles, most of these spanning many sheets and intermingled and are not alike. (Yes, Excel handles this surprisingly well).
So my issue is not a question of handling a large number of simple iterations in a handful of circles that are feasible to track individually.

(But your solutions may well be sufficient for Matt and others, so thanks anyway :-))