How to use "goal seek" many times?

[insaneoctane]

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]

 

[Oaktree]

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).

(See download Colo's HTML Maker... at the bottom of this page for a utility to post a sample of your worksheet)

 

[insaneoctane]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	DMIRROR	N2	N1	KSAMC	K1	K2	D1	L1	L2	L3	C	c_as_%L2	ZERO
2	5.90E-04	1	9	2608	23472	2608	3.17E-04	1.25	2.3	0.65	1.454008	63.2%	0.000406
3	5.90E-04	2	9	2608	23472	5216	3.15E-04	1.25	2.3	0.65	1.431163	62.2%	0.000163
4	5.90E-04	3	9	2608	23472	7824	3.12E-04	1.25	2.3	0.65	1.406136	61.1%	4.79E-05
5	5.90E-04	4	9	2608	23472	10432	3.09E-04	1.25	2.3	0.65	1.37843	59.9%	7.86E-06
6	5.90E-04	5	9	2608	23472	13040	3.06E-04	1.25	2.3	0.65	1.347315	58.6%	-0.00028
7	5.90E-04	6	9	2608	23472	15648	3.02E-04	1.25	2.3	0.65	1.311772	57.0%	-2.4E-05
8	5.90E-04	7	9	2608	23472	18256	2.97E-04	1.25	2.3	0.65	1.270002	55.2%	0.000181
9	5.90E-04	8	9	2608	23472	20864	2.91E-04	1.25	2.3	0.65	1.218666	53.0%	-5.8E-06
10	5.90E-04	9	9	2608	23472	23472	2.83E-04	1.25	2.3	0.65	1.14988	50.0%	-0.0008
11	5.90E-04	10	9	2608	23472	26080	2.66E-04	1.25	2.3	0.65	1.028468	44.7%	-0.00099
Sheet1

 

[tusharm]

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.

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]

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

   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]

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!!

Thanks in advance..


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]

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]

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]

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.

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

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!!

Thanks in advance..


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]

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.