Inputs for Goal Seek Command

[MUEEN SEHDI]

This query is with reference to Goal Seek function in Excel 2007.

While entering the inputs for its dialogue, the second parameter ""To Value"
has to be entered manually as a value. Instead of entering value here, we can
not reference this field to any cell in worksheet (As the application does
not allow you for this).

Is there any alternative solution to declare some deafult cell locations as
inputs to all 3 fields of the Goal Seek dialogue thus automating the inputs
and getting the result staright away.

 

[Bernard Liengme]

Without seeing the actual problem, my guess would be that you would be
better off using Solver.
Solver is far more powerful that Goal Seek
Do you wish to share with us a sample problem?
best wishes

 

[MUEEN SEHDI]

Thanks Bernard. Let me try to put forward the actual scenario here:
For a telecom project to dimension for certain number of subscribers, I am
prepearing a worksheet based tool. This sheet will be used a number of times
by a number of team members working on such projects. The target value of
subscribers (T) is known and fixed for a particular assignment which depends
on lot of factors. But one of the factors (Let's say X) is mainly driving all
other factors involved in reaching the target value. This X has to be
proposed and through iteration process, I have to reach the actual target
(T). In order to achieve this, I am trying to use Goal Seek function. I don't
want each user to do the Goal seek function manually. Instead I want to load
this action in a macro and add command button in worksheet to trigger this
automatically and execute based on assigned values in reference cells. In
Goal seek I have to enter values for three fields. The problem here is that I
can not assign reference cell to one of the three fields in this Goal Seek
Dialogue (Which is my target value). If I could do that, i can simply refer
this to my target cells, load this action in Macro and create a button in my
worksheet to trigger and execute this as a single click. Thus users need not
to enter values each time they do this exercise (thus eliminating any
probability of error in data entry).

I have tried to explore "Solver" but the limitation is the same that I have
to enter the target values in Solver Dialoue and I can not refer that field
to a particluar cell".

I hope above is a clear picture of the problem I am facing. I am very much
hopeful that you may help me sort this out.

Regards,
Mueen Sehdi

 

[Bernard Liengme]

Without trying to understand your complex problem I will focus on your
statement

I have tried to explore "Solver" but the limitation is the same that I
have
to enter the target values in Solver Dialogue and I can not refer that
field
to a particular cell".

Here is a simple scenario to show you how to do what you want: refer to a
cell in a Value problem
In A1 enter some number like 3
In A2 enter the formula =A1^2-10 (with A1=3, the result is -1)
Suppose I want Solver to alter A1 such that A2 has a result of 100
In A3 enter 100 (my target value)
Open Solver dialog; clear the box labeled Set Target Cell (select what is in
it and hit Delete key)
In the By Changing box enter A1
The add this constraint A2=A3
Hit the Solve button
Solver finds that with A1=10.488....., A1^2-10 has value of 100

This is the correct way to use Solver. The Set Target Cell should be used
only for Min/Max problems and never for Value problems

best wishes

 

[Dana DeLouis]

I can not refer that field to a particular cell".

Just an idea. You can't reference a cell by itself, but in VBA, you can
set it to the "Value" of that cell. Perhaps...

Sub Demo()
[A1] = 3
[A2].Formula = "=A1^2-10"
[A3] = 100

[A2].GoalSeek [A3].Value, [A1]
End Sub

= = = = = = = =
Dana DeLouis