interpolating values from graph

**xelhelp** · 02-11-2011, 12:08 PM

Hi,

I am using excel 2007 to plot a graph and I would like to find the values between data points. I have attached a sample spreadsheet with X and Y values plotted on the graph. Column A are X-values and column B are Y-values.

For example, the 3rd X and Y data points are 20 and 0.0711 respectively. The 4th X and Y data points are 27 and 0.065341 respectively. I would like to find the Y value associated with an X value between 20 and 27, say for example 24. How do I go about doing this using interpolation from the graph. I could just eyeball it but I need a more exact figure. Plus I need to do this multiple times throughout the graph. So, what I would like is a formula that will let me enter the X value, 24 in my example, and give me the Y value based on interpolation from the graph. In cell G21 I would like to be able to enter an X value, 24 in my example, and get the interpolated Y value from the graph in cell G22.

p.s. I have attached a sample sheet.

Thanks,
A

**Andy Pope** · 02-11-2011, 12:29 PM

You can use the VLOOKUP,MATCH,INDEX and TREND functions to determine the interpolated point.

You also need to change the chart from Line to XY-scatter in order to highlight the point.

**MarvinP** · 02-11-2011, 12:37 PM

Hi xelhelp,

See the Attached. I've used a few helping cells and formulas to do this problem. It comes down to the standard triangle calculation. (x1,y1) start of triangel (x2,y2) end of triange... etc.