Linear interpolation is an alternative to using the trend line tool or the regression tool in Excel, and it works well in certain scenarios. Sometimes is difficult to obtain a mathematical equation for information represented on a scatter plot. Here I've got an example of crude oil prices, dollars per barrel on the y-axis as a function of date from roughly early 2017 to mid to late 2018. And this really doesn't follow a nice nice mathematical model. In other words if I try to add a trend line to this data, by the way, this is in a file called crude oil. If I right click on the data and I try to add trend line, I can experiment around with a couple of these, I can even create very high order polynomials. And even with very high order polynomials, I'm really not creating a really good fit for the data as a whole. So instead what we can use is this technique known as linear interpolation. With linear interpolation we are assuming that between each subsequent set of data, each pairs of consecutive data, we have lines. So we have lines going between each of these data points. And if we do that in certain situations like this, our approach is going to be a lot more accurate than trying to fit the data to a mathematical equation as a whole. As an example we're going to do this, we're going to use linear interpolation to estimate what was the price of crude oil on September 15, 2017. If we're trying to estimate the crude oil price on September 15, 2007, we're going to zoom into this region because that's where September 15 occurs. And we're going to use the data over here on the left that we're going to have in Excel, and we're going to assume that it's a line. So we've got a line connecting those data points and that's just y = mx + b. We're going to create a line, and then we can easily predict by plugging in our September 15 for the variable x. We can predict the y value which is dollars per barrel. And that's how we can use linear interpolation. So we're going to zoom in on those two points. What we're going to have to do then is we're going to have to calculate a lower point and an upper point that is on left and right of our date of interest. Again what we're trying to do is calculate September 15, the crude oil price. And so we're going to use those two data points, we're going to create the line y = mx + b. And then we can use our x value of September 15 to estimate the dollars per barrel on that date. In order to do this, we're going to start with two points. The upper one we're going to name that high dat, high date is the x value corresponding to the upper point. The y value of the upper point is called high price. We're going to do the same thing for the low point. We have the low date and the low price. And if we have any two points, we can create a line, and we're going to do that in Excel. After we have that equation for the line, then we can substitute in September 15, so somewhere in here. And we can estimate using that the cost of crude oil on that date. I've got the crude oil cost as a function of date, and we're going to create this worksheet such that the user can just put in a date anytime between January 3, 2017 and June 1, 2018. And what we're going to try to do is we're going to make this worksheet such that it spits out the estimate for the dollars per barrel. Again we need two points. We need the low point and an upper point. We have to find somehow the lower point and the upper point from the date, and that's a little bit challenging in itself. But once we find the lower point, in this case if I look over here September 15 lies between those two. So this here, that's our low date and our low price. The next line is going to be our high date and our high price. So we somehow need to extract using Excel formulas, the low date and the high date. We're not just going to manually punch those numbers in, because what if somebody wanted a different date? We would have a different low point and high point. In order to do this lookup, we're going to use the VLOOKUP function. I'm going to first name this array here. I'm just going to name that data. I'm also going to name this cell E7, data and date, and then we can go ahead and find the low date. Now I can use the VLOOKUP function to do this. The lookup value is going to be the date, the table array that I'm looking through is data. Now I'm looking through the data, it's actually rare in the VLOOKUP function to have 1 as the output, the column index that you output. But in this case what I want to do is I want to search for September 15, assuming this is in ascending order here. If I put TRUE, it's going to find an approximate match, is going to find the match that's 1 above where it would find September 15. So if I press enter it's going to search for September 15 in the leftmost column of our data, and then is going to back up to the next one. And it spits out a serial number. I can go up here and turn this into a short date. Now to find the high date, this is going to be a little bit different. So what I'm going to do now in order to calculate the high date, I'm just going to use the match function. I'm going to match September 1, our low date. I'm going to look that up in the date array. So I'm going to select that and I'm going to use F4. I want an exact match, so I'm going to do 0. So what this does is going to tell me which row does it find the low date in. And then If I add 1 to that, I would have the tenth row. I'm going to use the index function. I'm going to use the index function with that date column. I'm going to press F4 which really isn't necessary here, but I'm going to look through that date array for then tenth row, because we found September 1 in the ninth row. And so I'm going to add 1 to here, and when I press enter it's calculating the serial number for that. I'm going to go up here to General and change this to a short date. So what that does is we can calculate the low date using VLOOKUP. We're going to find using the match function. We're going to find September 1 here which in this case is the ninth row. We're going to add 1 to that, because we want to output the tenth row. And then we can use the index function to output the tenth item of that date array. So this is a little It tricky but it is a nice way to find those lower and upper dates. Now what we need to do is we need to find the price that corresponds to our low date. I'm going to use the VLOOKUP function, I'm going to look up. The value that I'm going to look up is not our date, is going to be the low date. I'm going to be looking through our data array. In this case I want to output the dollars per barrel, so I'm going to put 2. And then I want an exact match here, so I'm going to do FALSE. And when we do that, we see that on September 1 the price of crude oil was about 47.29. Because I wrote this formula here in terms of relative references, I can drag this down. And we are using October 2, which is the upper date to do this. On October 2, the price per barrel was $50.58. And now what I've done in essence is I have calculated the high date, the high price, and the low date, and the low price. What I'm going to do next is I'm going to use the forecast function. The forecast function will estimate the y value given at least two points for a line. Here in cell E8 I'm going to type in FORECAST.LINEAR. The x value I want to use is our date, September 15 in this case. The known ys would be the prices. We've got our prices because those are on the y axis. And then our known xs are the the dates. And then I can close the parentheses and I can press enter. I'm going to decrease the decimal little bit here, and it's estimating that on September 15 the price per barrel was 48.78. So it's using linear interpolation. And we can kind of just go in there and check to make sure that that makes sense. The nice thing about how I set this up is the user can put in any date. So maybe the user puts in March 14, 2018. What it does is it calculates the low date, the high date, so it's finding these two data points. It's interpolating using the forecast function to estimate that on March 14, the cost of crude oil was about $61.81. So again a lot of times it's difficult to create a mathematical equation for a bunch of data using trend lines. And if that's the case, then you can use linear interpolation to estimate mathematical equations between points just as simple lines. Hope you enjoyed this screencast, and thanks for watching.
