PDA

View Full Version : iterate till equilibrium reached



white_flag
03-25-2011, 04:18 AM
Hello

I have the following sheet (see attachment) And I like to iterate an cell (D6) till (D21+I10=N15) or result will converge (equilibrium reached).

A.

BrianMH
03-25-2011, 12:14 PM
Are you looking for a formula to figure out this number? Or are you looking for VBA to count D6 up by a certain number until the condition is met.

mdmackillop
03-25-2011, 02:57 PM
Have you tried Goal Seek?

Wireless Guy
03-25-2011, 04:12 PM
I think you just want to use Goal Seek.

Make sure you have the Solver Add In installed. If you want it to be part of a macro, just record it.

white_flag
03-28-2011, 12:16 AM
morning

It is possible to be solved without solver.xla? VBA?

white_flag
03-28-2011, 01:30 AM
I put this solution:


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


What do you think?

white_flag
03-28-2011, 03:34 AM
I like to add extra conditions:


if (range("D4").value + 2* range("D5").value)/1000 <= 0.075 then
Range("G10").Formula = "=I4*(I8^4-I9^4)*I7*I5*D6"
else ..etc


my problem is how to add condition in if statement?
"if (range("D4").value + 2* range("D5").value)/1000 <= 0.075 then"

Kenneth Hobs
03-28-2011, 07:28 AM
Looks ok to me.