Using Goal Seek on a multi-column range

Following a comment requesting help using my Goal Seek spreadsheet I have modified the macro to allow it to operate on a multi-column range.  The spreadsheet can be downloaded from, including the solution to the problem described below.

The problem is to find the values of H such that Z = 1/(sqrt(H^2 + (A-x)^2 + (A-y)^2) for values of x and y between -10 and 10.

The stages to solve the problem are:

Set up a 21 x 21 table with copies of the formula reading x values from the row above the table, y values from the column to the left, and H values from a 21 x 21 range below:

Click for full-size view

Click for full-size view

The formula is entered in cell B13 as =SQRT(B36^2+($B$7-B$12)^2+($B$7-$A13)^2)
note the use of $ signs to specify x values in row 12 (B$12) and y values in column A ($A13).  The formula is then copied to the range B13:V33.  The Z values will be written to the range B36:V56, and this range must be filled with a starting value for Goal Seek.

The ranges for the target cells and “by changing” cells, and the target value are then entered in the grey shaded cells.  Note that I have entered the target as 1/Z, and modified the formula accordingly.  This gives better precision in the results, because the target is a larger number (and also makes the formulas a little simpler).


After entering the data and ranges press Alt-F8 and run the GSeekA macro.  The range B13:V33 should show the target value (100.0) and the range B36:V66 will contain the Z values found by Goal Seek:

Click for full size view

Click for full size view

This entry was posted in Excel, VBA and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s