Interpolation and Extrapolation in Excel

Interpolation and Extrapolation in Excel

This trick will save you time to do interpolation and extrapolation out from a table in Excel. It is powered by VBA or Macro using a user-defined function.

Description of Example

We have a table of thermal expansion of carbon steel at different change in temperature. The objective is to find the value of expansion at 35 degrees Celsius in reference to the given table.
Stacks Image 259

The screen recording below shows how to use the user-defined function 'InterpolateTable()'.
Stacks Image 140

Creating the User-Defined Function

Step 1. Go to 'Developer' menu, then choose 'Visual Basic'
See below figure.
Stacks Image 263

Step 2. Right-click from the left side panel of the new window, then choose 'Insert' > 'Module'.
See below figure.
Stacks Image 267

Step 3. Copy and paste the following VBA code to the new module that has been created. Your screen must be similar to this screenshot.
Stacks Image 176

Step 4. Close the VBA window and use the newly created function by typing =InterpolateTable(). See below figure and screen recording.
Stacks Image 235
Stacks Image 241
Download the example Excel file using the button below.

Did you find this content useful? If yes, please consider helping us by making a Donation. You can get free pdf when you donate now, see details here.