Full Version: Loop to Accomplish a Series
awolterm
Hi all,

I am trying to work a loop out such that number becomes 0 by multiplying a factor by certain items.

It basically needs to work like Goal Seek in excel, in that it will identify if a number is negative or positive. If it is negative then the factor needs to decrease, if it is positive, the factor needs to increase.

It would then go through a cycle like this starting at 1:

1 Number is -
.9 Number is -
.8 Number is +
.81 +
.82 +
.83 -
.829 -
.828 -
.827 +
.8271 +
.8272 +
.8273 -
.82729 -

And so on and so forth.

Excel's goal seek comes up with .827202915173841.

I am trying to construct a loop that can iterate through all of these decimals to find the correct answer.

Can anyone help me with this?

Thanks.
xteam
Hmm, take a look at this function - is an example of calculating square root with precision of 0.0000000001

CODE

Public Function GoalSeek(dblVar As Double)

'approximate square root for dblVar with 1 ^ -10 precission)
Dim dblCalc                 As Double
Dim dblStep                 As Double
Dim strSign                 As String

dblCalc = 1
dblStep = 0.1

'Initialize strSign
Select Case dblCalc * dblCalc - dblVar
Case Is = 0
GoalSeek = dblCalc
Exit Function
Case Is < 0
strSign = "-"
Case Is > 0
strSign = "+"
End Select

Do While Abs(dblCalc * dblCalc - dblVar) >= 10 ^ (-10)
Select Case dblCalc * dblCalc - dblVar
Case -10 ^ (-10) To 10 ^ (-10)
Exit Do
Case Is > 10 ^ (-10)
If strSign = "+" Then
dblCalc = dblCalc - dblStep
Else
dblStep = dblStep / 10
dblCalc = dblCalc - dblStep
End If
strSign = "+"
Case Is < -10 ^ (-10)
If strSign = "-" Then
dblCalc = dblCalc + dblStep
Else
dblStep = dblStep / 10
dblCalc = dblCalc + dblStep
End If
strSign = "-"
End Select
Debug.Print dblCalc
Loop

Debug.Print "==================="
GoalSeek = dblCalc

End Function

IF you run ?Goalseek(2) in debug window you will get:

1.1
1.2
1.3
1.4
1.5
1.49
1.48
1.47
1.46
1.45
1.44
1.43
1.42
1.41
1.411
1.412
1.413
1.414
1.415
1.4149
1.4148
1.4147
1.4146
1.4145
1.4144
1.4143
1.4142
1.41421
1.41422
1.414219
1.414218
1.414217
1.414216
1.414215
1.414214
1.414213
1.4142131
1.4142132
1.4142133
1.4142134
1.4142135
1.4142136
1.41421359
1.41421358
1.41421357
1.41421356
1.414213561
1.414213562
1.414213563
1.4142135629
1.4142135628
1.4142135627
1.4142135626
1.4142135625
1.4142135624
===================
1.4142135624