Interpolation And Extrapolation In Excel

Interpolation and Extrapolation in Excel

• • Index and Match
  • Max and If
  • Roots of Quadratic Equation
  • Count Working Days
  • Data Validation
  • Equation Editor
  • Counting Formulas
  • Circle Invalid Data
  • Disabling Save As
  • Linest Function
  • Batch Printing
  • Lookup Pictures
  • Combo Chart
  • Highlight Row
  • 4 Data Entry Tricks
  • Interpolation and Extrapolation
  • Flash Fill
  • Remove Duplicates
  • VBA Ultimate Protection

Interpolation and Extrapolation in Excel

Interpolation and Extrapolation in ExcelThis 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. The screen recording below shows how to use the user-defined function 'InterpolateTable()'.

Creating the User-Defined Function

Step 1. Go to 'Developer' menu, then choose 'Visual Basic'See below figure. Step 2. Right-click from the left side panel of the new window, then choose 'Insert' > 'Module'.See below figure. 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. Option Explicit Function InterPolateTable(rX As Range, rY As Range, x As Double) As Double Dim lR As Long, l1 As Long, l2 As Long Dim nR As Long nR = rX.Rows.Count If nR < 2 Then Exit Function If x < rX(1) Then l1 = 1: l2 = 2: GoTo Interp ElseIf x > rX(nR) Then l1 = nR - 1: l2 = nR: GoTo Interp Else For lR = 1 To nR If rX(lR) = x Then InterPolateTable = rY(lR) Exit Function ElseIf rX(lR) > x Then l1 = lR: l2 = lR - 1: GoTo Interp End If Next End If Interp: InterPolateTable = rY(l1) _ + (rY(l2) - rY(l1)) _ * (x - rX(l1)) _ / (rX(l2) - rX(l1)) End Function Step 4. Close the VBA window and use the newly created function by typing =InterpolateTable(). See below figure and screen recording. Download the example Excel file using the button below. Download Example Excel File More tutorials..> Show Comments

About Us

Excelcrib was founded in November 2017 by a Microsoft® Office Excel® (MS Excel) enthusiast with background in engineering. He's been using MS Excel for more than 15 years in practice with specialty in VBA.

Explore

• Home
• Excel Tricks
• How To
• Downloads
• Add-ins
• SecureVBA

Contact Us

Follow Us

• YouTube
• Facebook
• Twitter

© Copyright 2025 Excelcrib | Privacy Policy

We use cookies to improve your browsing experience.Continuing to use this site means you agree to our use of cookies. Tell me more!

Dismiss