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


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
Vishesh's picture

There should be a formula in

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

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

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

=(C3 - D3)*100000

haven't thought of that...

haven't thought of that... thanks!