Home » Forums » Open Source Learning Center » Question and Answer

(SOLVED) How to use a cell value as the "To value" value on Goal Seeking

Submitted by guiismiti on 13 April, 2013 - 17:57

Hello,

I have been looking for the solution for this problem for hours now, and I can't seem to find it, since the only results that show up when you include Excel goal seeking in your search are basic guides on how to use the Goal Seek feature.

A detailed description of my problem:
I am trying to implement a button in my worksheet, and the macro connected to it is supposed to Goal Seek with values informed by the user. I need the button implemented since most of the people who are going to use the worksheet are not much familiar with Excel.

So, the question is, how do I use a cell value for the "To value" field of the Goal Seek feature?

The picture attached illustrates my problem - I want to reach a minimun of Y on Z by variating X.

Thanks in advance

Goal seek - reach Y on Z by variating X
‹ arrays and finding unique names in a list Mirroring data ›
»
Vishesh's picture

There should be a formula in

Submitted by Vishesh on 15 April, 2013 - 13:55.

There should be a formula in Cell D3.
Enter the 'To value' in cell F3.
Create a button and assign a macro to it with the following code...

Range("D3").GoalSeek Goal:=Range("F3"), ChangingCell:=Range("C3")

»

I actually solved it by

Submitted by guiismiti on 15 April, 2013 - 14:01.

I actually solved it by simply creating a hidden cell with the formula "=C3 - D3". So, the Goal seek setting is, Set hidden cell to reach the value of 0 by variating B3. That way, the "To value" field became a constant (0).

Not my idea though.

Thanks anyway!

»
Nick's picture

useful tip: multiply the

Submitted by Nick on 15 April, 2013 - 14:59.

useful tip:
multiply the difference by 100000 (or whatever), and you'll get a more accurate result

i.e:
=(C3 - D3)*100000

»

haven't thought of that...

Submitted by guiismiti on 15 April, 2013 - 16:26.

haven't thought of that... thanks!

»