## 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 GSeek.zip, 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:

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:

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.