How can i interpolate a data (y) on a graph to obtain the value of (x)?

Here is a shot at it

Scatter chart of columns B through E. Columns B and C are your data. Cell E2 is where you put the Y value of interest.

Formula in D2 =IF(AND(E$2>=C2,E$2<=C3),B2+(E$2-C2)*(B3-B2)/(C3-C2),"")

Fill down to complete the column

Formula in E3 =IF(D3="","",E$2)

Fill down to complete the column

You could use the more simple formula =E$2 . If you do, all rows will have your Y data point but only the rows with X data will plot on the chart.

Given two points you can calculate the slope (m), then you can use the first point to create a linear equation for the line between the two points. Then you can plug any X that is between the Xs of the two points.

Given:

(X1, Y1)

(X2, Y2)

m = (Y2 - Y1) / (X2 - X1)

equation for new line is:

y = mx + b

substitute a coordinate pair into above equation then solve for b:

Y1 = mX1 + b

no rearrange for b:

b = Y1 - mX1

m and b should be actual numbers

now use the formula:

y = mx + b where m and b are actual number and compute a new Y for a given X:

Formulas above are:

D2=(B3-B2)/(A3-A2)

E2=B2-D2*A2

C4=D2*A4+E2

The chart is set up to graph X, Y and Y Interp by sharing x-values

Hi IsaZhL,

Your graph shows a straight line between the two black points. If the graph (response of Y to X) really is a straight line between those points, then the slope of that line and its intercept will enable you to interpolate from a Y value to an X value.

I assume that X (Time?) is the uncontrolled (and uncontrollable!) variable. Y is a measured value (such as height?)

Please post a screen shot of a small part of your data table. We need to see what you are trying to do!

Regards,

Ian.

The chart in your second post is completely different from the one you gave in your first post. Your first post showed a chart where straight lines were drawn from point to point and where there might be more than one X for a given Y. Your second chart is a linear-fit trendline of a set of data. I wasted my time answering the question you asked.

You have the slope and the intercept (m and b) in your graph already:

m = 0,016

b=0,0099

You cannot do this on a chart/graph except by eyeballing it or drawing lines. You can do it with formulas in the table that contains the data from which the graph was created.

An alternate formula for the interpolation in my example, one that doesn't require you to know how to do interpolation, is

cell D2 =IF(AND(E$2>=C2,E$2<=C3),FORECAST(E2,B2:B3,C2:C3),"")

Do you still have a question?