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:

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

### Like this:

Like Loading...

*Related*