How To Do Linear Interpolation In Excel

Click here to go to our page on using linear interpolation to solve "1-way" equations

This page has one of our highest folio views rates, because it is useful well beyond the microwave engineering community.  Here's a quote from Josh who sent us an email in Febrary 2015:

"I just wanted to drop a quick note that your communication about using Alphabetize and Lucifer Functions just helped me terminate a project.  I couldn't find this anywhere else and it was fantastic help!"

There are many times in engineering where you lot detect yourself interpolating between data points. In microwave technology, this happens in power demote measurements all the time (you only get a limited set up of calibration data with a power head). Also when y'all take manufacturer'southward Southward-parameters, they are not always at the exact frequency yous want.

Go to our download surface area and become the Linterp_101 spreadsheet that nosotros used to create this page.

Before we even become into the obtuse EXCEL calculation, let'southward bespeak out that in that location is no excuse for Microsoft Excel not to have this role built in. MathCAD has it (and tons more cool functions!), MathCAD calls it the LINTERP part. BTW, what do you lot phone call the function when your kid wants help with their math homework? That'south MathDAD!

Interpolation means fitting Y-value data to to an X-value that is somewhere betwixt two data points, using a straight line. Uncomplicated in concept, a purple pain to do in a spreadsheet.

Note 1: fitting a "spline" to data is oft much more authentic than linear interpolation.  We don't discuss that here, but you can go spline data out of Excel.

Notation 2: when interpolating S-parameter data, is recommended that you interpolate magnitude and bending (yous could call this "radial" interpolation) rather than existent and imaginary data.

Interpolation or extrapolation?

Interpolation is "faking" data points that are contained inside a wide dataset. Extrapolation is faking information points exterior the dataset. Extrapolation is wishful thinking, and can get you in trouble in microwave engineering. Try to see what extrapolating the response of a bandpass filter does outside the passband sometime if you need proof!

What is interpolation?

Any high school graduate should exist able to tell y'all the formula for linear interpolation. Especially after they look it up on Wikipedia! Below is a close-up of an interpolated information signal. X1 and Y1 are "real" data points, so are X2 and Y2. We seek to find the Y value for an arbitrary X value between these two points, such that it is on the direct line that is drawn between them.

This is the linear interpolation part:

Y=Y1+(X-X1)x(Y2-Y1)/(X2-X1)

Uncomplicated, n'est-ce pas?

Interpolation is used every day in engineering, linear is just one method. Other more complicated methods include fitting a cubic spline to a data gear up (which is what Excel does when it draws curved lines in a graph) or logarithmic interpolation, and even log-linear interpolation (linear on one axis, logarithmic on the other). Actually, if you interpolate Southward-parameter magnitudes in dB over frequency, you lot are doing log-lin interpolation.

By the way, our spreadsheet can interpolate on log-log and log-lin axes. All you lot have to do is convert your data to log format first, then interpolate, so "unconvert" from log.

Creating the office in Excel

In Excel, creating the interpolation function uses a mix of other functions INDEX and Match functions (there may exist other ways to exercise it but this is how we did information technology). Yous have to discover the next lower value and the next higher value in the assortment for X and Y. MATCH is used to observe X1 and X2, while INDEX returns the Y1 and Y2 values that MATCH points to.

The formula for interpolating a Y value for a X value contained in cell C10 is given below. In this case the data ready is contained in rows 10 to 17 (column A is X data, column B is Y data), and it is in ascending guild.

=Index($A$10:$B$17, Lucifer(C10,$A$10:$A$17,ane),ii)+
(C10-INDEX($A$10:$B$17, Match(C10,$A$x:$A$17,ane),1))*
(INDEX($A$10:$B$17, MATCH($C10,$A$ten:$A$17,ane)+1,2)-
INDEX($A$x:$B$17, MATCH($C10,$A$x:$A$17,i),2))/
(Index($A$ten:$B$17, MATCH($C10,$A$10:$A$17,i)+1,1)-
INDEX($A$10:$B$17, Lucifer($C10,$A$10:$A$17,1),1))

Yikes!

Match(C10,$A$10:$A$17,i) finds the largest value that is less than or equal to "lookup_value", which in this case is the X data. The is "X1" in the graph.

MATCH($C10,$A$10:$A$17,1)+1,2) finds the adjacent value that is just a picayune bigger than X1, this is X2 in the graph.

Lucifer is used to discover the Y1 and Y2 values.

Than it's just a affair of computation:

Y=Y1+(X-X0)*(Y2-Y1)/(X2-X1)

Here'southward a message board post on another spider web site that also attempts to describe the solution. Nosotros think we offer a better explanation, but feel costless to annotate if yous see any points that need clarification.

Now it's time for two examples.

Noise source instance

A noise source is used in noise figure measurements. It provides 2 noise states, the excess dissonance ratio is the departure between them, usually expressed in dB. There is a limited set up of scale data, and it is usually attached to the noise source past the factory, similar the photo of Agilent 346B dissonance source beneath (really this is an old "HP" unit, newer units are branded as "Agilent").

We entered the data into the interpolation spreadsheet, and interpolated values betwixt the calibrated points, shown below. Notice how interpolation tends to smooth information!

.

Power head example

Hither's an 8485A power head from Agilent, used in power meter measurements. Information technology has calibration data from ii to 26.5 GHz. Really the "reference cal cistron" is another data point at 50 MHz, in this example it is 99%.

We entered the information into the spreadsheet, and interpolated points between calibration information equally shown below.

S-parameter case

Southward-parameters can be interpolated over frequency, only there'south ii problems to consider. Should y'all interpolate the magnitude in linear units, in dB, or should you convert magnitude/phase to real/imaginary and interpolate that? In the case of Agilent'due south Advanced Design Arrangement, yous'd use the "native units", then you lot'd merely interpolate whatever the data came in as.

The other issue y'all take to bargain with is when y'all interpolate phase data between wo adjacent data points where the phase bending wraps from -180 to +180 degrees; you can end upwardly on the opposite side of the Smith chart.

Below is a plot of interpolated South-parameter angle data that we created from an Hittite HMC548LP3 that nosotros downloaded from the Hittite spider web site. The frequencies are incremented every xi.962 MHz, which is a result of measuring the device from 500 to 3000 MHz in 210 steps. If yous await closely in that location is one errant point in the interpolated S21 bending, this happened at 1500 MHz because the stage of the two adjacent information points wrapped past 180 degrees. One of these days we'll put in a set for that!

Source: https://www.microwaves101.com/encyclopedias/linear-interpolation-for-excel

Posted by: danielsgaince.blogspot.com

Share this post