Inputs for Goal Seek Command

MUEEN SEHDI · External Since: Oct 29, 2009 Posts: 2

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 · External Since: Aug 27, 2003 Posts: 146

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"MUEEN SEHDI" <MUEENSEHDI.DeleteThis@discussions.microsoft.com> wrote in message
news:AB6FF6B3-CD7E-4EB7-83B6-A58FDC84AFE9@microsoft.com...
> 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.

MUEEN SEHDI · External Since: Oct 29, 2009 Posts: 2

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" wrote:

> 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
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
>
> "MUEEN SEHDI" <MUEENSEHDI.TakeThisOut@discussions.microsoft.com> wrote in message
> news:AB6FF6B3-CD7E-4EB7-83B6-A58FDC84AFE9@microsoft.com...
> > 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 · External Since: Aug 27, 2003 Posts: 146

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"MUEEN SEHDI" <MUEENSEHDI RemoveThis @discussions.microsoft.com> wrote in message
news:FF8CE09B-2CA8-4DA0-BFAF-C1C2B54E7C2A@microsoft.com...
> 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" wrote:
>
>> 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
>> --
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>>
>> "MUEEN SEHDI" <MUEENSEHDI RemoveThis @discussions.microsoft.com> wrote in message
>> news:AB6FF6B3-CD7E-4EB7-83B6-A58FDC84AFE9@microsoft.com...
>> > 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.
>>
>>
>> .
>>

Dana DeLouis · External Since: Mar 28, 2007 Posts: 3

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

Bernard Liengme wrote:
> 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