I was trying to have VBA to recurse on an assumed result, to
calculated one (I need to assume the answer to do some intermediat
calculations which ultimately end up with a final answer), in this cas
a temperature.

when I coded

recurse:
terror = tassume -tcalc
if terror <0 the terror = terror * -1
if terror <0.3 then goto done
tassume = tassume +0.5 * (tcalc - tassume)
go to recurse

the code did not recurse to within 0.5

but when I coded

recurse:
terror = (tassume *100 - tcalc*100)
if terror <0 the terror = terror * -1
if terror < 30 then goto done
tassume = (tassume*100 +0.5 * (tcalc - tassume)*100)/100
go to recurse

it worked fine

any thoughts??

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=56167

Did you declare your variables as double?

duane wrote:
I was trying to have VBA to recurse on an assumed result, to a
calculated one (I need to assume the answer to do some intermediate
calculations which ultimately end up with a final answer), in this case
a temperature.

when I coded

recurse:
terror = tassume -tcalc
if terror <0 the terror = terror * -1
if terror <0.3 then goto done
tassume = tassume +0.5 * (tcalc - tassume)
go to recurse

the code did not recurse to within 0.5

but when I coded

recurse:
terror = (tassume *100 - tcalc*100)
if terror <0 the terror = terror * -1
if terror < 30 then goto done
tassume = (tassume*100 +0.5 * (tcalc - tassume)*100)/100
go to recurse

it worked fine

any thoughts???

yes i did, sorry should have added that to first post


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=561671

What kind of initial values do you have?

Sub Demo()
Dim TAssume
Dim TCalc
Dim TError

TAssume = 45
TCalc = 2

TError = Abs(TAssume - TCalc)
Do While TError = 0.3
TAssume = TAssume + 0.5 * (TCalc - TAssume)
TError = Abs(TAssume - TCalc)
Loop
Debug.Print TAssume
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"duane" wrote in
message ...

yes i did, sorry should have added that to first post


--
duane


------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=561671

If you would like, perhaps something along this line...

Do While TError = 0.3
TAssume = (TAssume + TCalc) / 2
TError = Abs(TAssume - TCalc)
Debug.Print TError
Loop

We see that you variable TAssume is moved closer and closer to TCalc
until your error is <0.3.
We might be able to solve for this recurrence relationship without
looping. I was thinking something like this:
We first calculate how many loops it would take to get the error below 0.3.
Then we solve for the actual value.

Sub Demo2()
Dim TAssume
Dim TCalc
Dim n

TAssume = 13
TCalc = 2

If Abs(TAssume - TCalc) = 0.3 Then
n = Log(20 * Abs(TAssume - TCalc) / 3) / Log(2)
n = WorksheetFunction.Ceiling(n, 1)
TAssume = ((2 ^ n - 2) * TCalc + 2 * TAssume) / 2 ^ n
End If
Debug.Print TAssume
End Sub

I hope I got that correct! :0

Dana DeLouis


Dana DeLouis wrote:
What kind of initial values do you have?

Sub Demo()
Dim TAssume
Dim TCalc
Dim TError

TAssume = 45
TCalc = 2

TError = Abs(TAssume - TCalc)
Do While TError = 0.3
TAssume = TAssume + 0.5 * (TCalc - TAssume)
TError = Abs(TAssume - TCalc)
Loop
Debug.Print TAssume
End Sub

Thanks for our help. Right now I as initializing all of the assume
values at 4. The actuals work out to be anywhere from +15 to -40. I
theory, the Tassume can influence the Tactual, so Tactual has to be
variable as Tassume changes. I am working with an older verion o
excel (2000 I think), and when I wrote the code, I trie
application.worksheetfunction.abs, but this did not exist, so
substituted if terror <0, then terror - terror * -1. Can I just cod
in the abs(arguement) in vba as you have

--
duan

-----------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...fo&userid=1162
View this thread: http://www.excelforum.com/showthread.php?threadid=56167