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

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