Goal Seek & Newton-Raphson

Hi Guys,

I am newbie to Excell/VBA programing and I am in need of help.

Is there a way to make the Goal Seek function automatic, so a small change in one cell starts the calculation? If this is not possible, can someone help me the code below, an implemention of the Newton Raphson method, because I dont know how to program. I really appreacite your help.

Function PriceChange(MA1, MA2, guess)
Dim epsilon As Double, dVol As Double, P_1 As Double
Dim i As Integer, maxIter As Integer, Value_1 As Double, P_2 As Double
Dim Value_2 As Double, dx As Double

dP = 0.00001
epsilon = 0.00001
maxIter = 100
P_1 = guess
i = 1
Do
Value_1 = MA1
P_2 = P_1 - dP
Value_2 = MA2
dx = (Value_2 - Value_1) / dP
If Abs(dx) < epsilon Or i = maxIter Then Exit Do
P_1 = P_1 - (MA1 - Value_1) / dx
i = i + 1
Loop
PriceChange = P_1
End Function

For automating "Goal Seek" check out the "Worksheet_Change" sub i.e. something like

Please Login or Register  to view this content.

Then there is also the Excel add-in Solver that use the Newton Raphson metode (default setting) to solve linear and and non linera problem.

Alf

Thanks, Alf. I have 400 cells that need to be calculate using Goal Seek, and the valeus for each of them are different. Will the same solutions work?

Please Login or Register  to view this content.

That depends on the layout of your data. The macro above will fire if there is a change in any value in range B2 to B9 but the cell to change and the "Goal" will remain the same.

Perhaps this link would be more usefull?

http://www.vbaexpress.com/kb/getarticle.php?kb_id=433

Alf

Alf, thanks again.

The problem is the following:

A1: 10-X B1: 1 C1: -1. D:1 X. I want to change X until B1 = C1
.
.
.
A400

So, it is a Goal Seek for each line, and has to take into consideration only 4 cells per line.

Perhaps like this then?

Please Login or Register  to view this content.

If I understood you problem description right. If this doesn’t work I suggest you upload a file describing the problem in more detail.

Alf

I am uploading a spreadhseet with an example. I hope it makes the problem more clear. The values continue from row 30 to row 400, and ideally it should be automatic, meaning any change automatically finds the value on column D.