Excel: Interpolating Using Goal Seeker

The information in this article applies to:

• Microsoft Excel for Windows, versions 3.0, 4.0, 5.0

SUMMARY

In Microsoft Excel, you can use Goal Seeker to interpolate values between two known values. This can be used, for example, for creating an XYZ graph where many X and Y values need to be plotted, and constraints are known for X and Y. Interpolating means to insert a specified number of values between two known values.

MORE INFORMATION

For example, suppose you want to interpolate 48 values between the numbers -3 and 3, for a total of 50 values. The goal is to calculate 48 values, all of which differ by a constant. To use Goal Seeker, you need to have an empty cell to hold this unknown constant. To accomplish this do the following:

1. Enter the value -3 into cell A2. Cell A1 is empty and will be the cell for the unknown constant.

2. In cell A3, enter the formula =A2+$A$1 (noting the absolute and relative referencing).

3. Highlight cells A3:A51. Choose Fill Down from the Edit menu to fill this formula down to cell A51. All cells will have a -3 entered because A1 is blank.

4. From the Tools menu (Formula menu in versions 3.0 and 4.0), choose Goal Seek.

5. In the Set Cell box, enter A51.

6. In the To Value box, enter 3 (the final goal).

7. In the By Changing Cell box, enter A1 (the constant).

8. Choose OK and Goal Seeker will interpolate all values between -3 and +3.

REFERENCES

"Microsoft Excel User's Guide 2," version 4.0, page 78-79

"Microsoft Excel User's Guide," version 3.0, pages 290-291