iterate till will converge

[white_flag]

Hello

I like to iterate an cell (D6) till (D21+I10=N15) will converge (equilibrium reached). D21=D6*formula, I10=D6*formula, N15=D6*formula.

 

[white_flag]

ok, thx.

But I quess via VBA can be possible.

 

[shg]

See http://www.box.net/shared/4gpe9qelvy

If you have a compile error, open the VBE, do Tools > References, and untick Solver if it shows as missing. Then, still in References, browse to C:\Program Files\Microsoft Office\OFFICExx\Library\SOLVER, change the file type dropdown to *.xls, *.xla, and pick SOLVER.XLA

If you still have a problem, run Solver from the user interface once (the model is in the workbook; Solver sometimes needs a kick start).

After that, it will solve automatically when you change any of the input cells.

Good luck, nice workbook, well organized. Pardon my editing -- it's a compulsion.

EDIT: BTW, someone here may be able to do this via iteration. If so, it would be a better approach for this workbook, I think.

 

[white_flag]

nice done ..thank you very much for your time and sugestions

 

[white_flag]

I put this solution via VBA:

Option Explicit
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("N14").Formula = "=2*PI()*(N13-N12)/(LN(((N4+2*N5)/2000)/(N4/2000))/N9+LN(((N4+2*N6)/2000)/(N4/2000))/N10+LN(((N4+2*N6+2*N7)/2000)/((N4+2*N6)/2000))/N11)"
Range("D22").Formula = "=PI()*D21*((D4+2*D5)/1000)*D6*(D7-D8)"
Range("I10").Formula = "=I4*(I8^4-I9^4)*I7*I5"
Range("N14").Name = "conductivity"
Range("D22").Name = "convection"
Range("I10").Name = "radiation"
Range("G19").Formula = "=conductivity-(convection+radiation)"
On Error GoTo TheEnd
Range("G19").GoalSeek Goal:=0, ChangingCell:=Range("D7")

TheEnd:
Application.EnableEvents = True
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Worksheet_Calculate
End Sub