# How to use "goal seek" many times?

#### insaneoctane

##### Board Regular
I've got several parameters in one row and I am using goal seek to manipulate one of the cells until another cell becomes zero, which is my solution...
I'd like to drag copy this row down many many times and vary one of the cells during the drag, but the row that contains the goal seek solution clearly isn't right... It would need to manually be run on each row which would take forever! Any ideas? If it's not clear, I'll upload a picture later.

[/img]

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### Oaktree

##### MrExcel MVP
Welcome to the board.

Not sure, I follow you. After you use goal seek, doesn't the input cell just have a value in it? So, if you copy the row, aren't you just copying a hardcoded value?

Perhaps you could post a sample of your data (formulas particularly).

#### insaneoctane

##### Board Regular
Here's the example:
I use goal seek to solve for what value of C (col. K) gives col M a value of 0. That gives me answer for that row of data (col. K is solved).

What I want to do next is vary N2 (col. B) from 1 to 9 and see what values of C result, but I have to manually run goal seek on each row to get each col M to zero which in turn gives me my solved col K for that row.

Is that any clearer?
Book3
ABCDEFGHIJKLM
1DMIRRORN2N1KSAMCK1K2D1L1L2L3Cc_as_%L2ZERO
25.90E-041926082347226083.17E-041.252.30.651.45400863.2%0.000406
35.90E-042926082347252163.15E-041.252.30.651.43116362.2%0.000163
45.90E-043926082347278243.12E-041.252.30.651.40613661.1%4.79E-05
55.90E-0449260823472104323.09E-041.252.30.651.3784359.9%7.86E-06
65.90E-0459260823472130403.06E-041.252.30.651.34731558.6%-0.00028
75.90E-0469260823472156483.02E-041.252.30.651.31177257.0%-2.4E-05
85.90E-0479260823472182562.97E-041.252.30.651.27000255.2%0.000181
95.90E-0489260823472208642.91E-041.252.30.651.21866653.0%-5.8E-06
105.90E-0499260823472234722.83E-041.252.30.651.1498850.0%-0.0008
115.90E-04109260823472260802.66E-041.252.30.651.02846844.7%-0.00099
Sheet1

#### tusharm

##### MrExcel MVP
Record a macro (Tools | Macro > Record new macro...) that does the first goalseek. Share it here (remember to format the code using the Code button) and someone should be able to generalize it for you.

insaneoctane said:
I've got several parameters in one row and I am using goal seek to manipulate one of the cells until another cell becomes zero, which is my solution...
I'd like to drag copy this row down many many times and vary one of the cells during the drag, but the row that contains the goal seek solution clearly isn't right... It would need to manually be run on each row which would take forever! Any ideas? If it's not clear, I'll upload a picture later.

[/img]

#### cfree36

##### Board Regular
Here is a code I've used to cycle through a column using goal seek:

Code:
``````   Dim i As Integer
i = 28
Do Until IsEmpty(Cells(i, 8))
Cells(i, 65).GoalSeek Goal:=Cells(i, 67), ChangingCell:=Cells(i, 32)
i = i + 1
Loop``````

This code starts at row 28 (i=28)
It looks at column H (Do Until IsEmpty(Cells(i, 8)) and runs until H is empty
Column BM is where it starts the goal seek (i,65)
Column BO is what it is goal seeking too (i,67)
And Column AF is what it is changing (i,32)

Change to fit your needs... but this will goal seek each row until complete.

#### insaneoctane

##### Board Regular
It a very simple macro...
But I guess the real question (or at least one better than what I already asked) is this the best way to solve it? Is there a way to solve it without manual writing macros? I will need to do this for each variable you see (ten of them) and I'd like to think I don't have to write code for each!!

Sub man_goal()
'
' man_goal Macro
' Macro recorded 12/2/2005 by me
'
' Keyboard Shortcut: Ctrl+g
'
ActiveCell.GoalSeek goal:=0, ChangingCell:=ActiveCell.Offset(0, -2).Range( _
"A1")
End Sub

#### RhettRobb

##### New Member
If I'm reading this right, the formula that determines M is a quadratic. Ax^2 + Bx + C right?

This means there's gonna be two possible solutions (two possible values of Col K that make Col M zero, though no guarantee that both are real).

So I wouldn't use goal seek in any case (since I don't know how it deals with multiple possible solutions). I'd set up some columns to solve for both solutions to the quadratic equation and copy that down. No Goal-Seeking necessary. Then you can vary your parameters to your hearts content and still know what value of C give you zero.

Like this
Col N = '=2*E2*G2' (the A part of AX^2)
Col O = '=-(E2*G2*I2+E2*G2*J2+F2*G2*J2)' (The B part of Bx)
Col P = '=(F2*G2*I2*J2)' (The C part)

then Col Q Would be Solution 1 with the formula '=(-O2+(O2^2-4*N2*P2)^(1/2))/(2*N2)'

Col R would be solution 2 with formula = '=(-O2-(O2^2-4*N2*P2)^(1/2))/(2*N2)'

Copy those down, then whatever you do to the variable Q and R will always tell you what values solve to zero.

Sorry this is more algebra than excel but it's what I'd do for whatever that's worth.

#### insaneoctane

##### Board Regular
Be careful trying to solve it as a quadratic, the term D1 (col G) contains a reference to C (col K) and you will see D1 (col G) terms in M! So if you tried to solve this closed form as you are suggestion, I think you'll get a circular reference...

#### tusharm

##### MrExcel MVP
Edit: Haven't checked RhettRobb's solution but if algebraic manipulation leads to a closed form solution it is probably best to go that route. If you must use numerical computing...

The code below compiles ok but has not been otherwise tested.

Code:
``````Sub man_goal()
Dim aCell As Range
Set aCell = ActiveWorkbook.Range("M2")
Do While Not aCell.Formula <> ""
aCell.GoalSeek goal:=0, ChangingCell:=aCell.Offset(0, -2)
Set aCell = aCell.Offset(1, 0)
Loop
End Sub``````
insaneoctane said:
It a very simple macro...
But I guess the real question (or at least one better than what I already asked) is this the best way to solve it? Is there a way to solve it without manual writing macros? I will need to do this for each variable you see (ten of them) and I'd like to think I don't have to write code for each!!

Sub man_goal()
'
' man_goal Macro
' Macro recorded 12/2/2005 by me
'
' Keyboard Shortcut: Ctrl+g
'
ActiveCell.GoalSeek goal:=0, ChangingCell:=ActiveCell.Offset(0, -2).Range( _
"A1")
End Sub

#### RhettRobb

##### New Member
insaneoctane said:
Be careful trying to solve it as a quadratic, the term D1 (col G) contains a reference to C (col K) and you will see D1 (col G) terms in M! So if you tried to solve this closed form as you are suggestion, I think you'll get a circular reference...

I hadn't caught that, but it wasn't giving me a circular error when I tried it. Upon closer inspection it turns out that it doesn't really matter. D1 (colG2) appears in each term and so can be cancelled out. This actually makes life simpler as your formula should be (using your variables, not the column names)

2*K1*C^2 -(K1*L2+K1*L3+K2*L3)*C+K2*L2*L3

which is amenable to algebraic solution.

Replies
8
Views
378
Replies
14
Views
424
Replies
36
Views
681
Replies
17
Views
728
Replies
13
Views
330

1,195,596
Messages
6,010,636
Members
441,558
Latest member
lambierules

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back