# How to get a particular y value on a graph from Excel

Posted on

QUESTION :

Here below is my Graph: Let’s say I have a reading of 1.02 ml (the x-value from the graph) from my buret, how do I find the corresponding y-value?

There isn’t an actual Y value for that X value, so you have to interpolate based on the actual points on either side of the value you’re looking for. teylyn’s example will work for the first leg of your graph, but let me give you a more general solution that will work for any point.

It looks like the data is roughly:

``````    A     B
===   ===
1   X     Y
2   0   0
3  10   0.01
4  20  -0.01
5  30  -0.01
6  40  -0.02
7  50  -0.06
``````

Since these are straight lines, you can calculate the value from the equation for a line: Y=mX+b, where m is the slope and b is the intercept (the Y value when X is zero). The simple ratio in teylyn’s example works because in that leg, the intercept is zero. To calculate any point, you can use Excel’s built-in functions for slope and intercept.

Say you have your X value of 1.02 in cell D2. The formula in E2 for the corresponding Y value would be:

``````=SLOPE(B2:B3,A2:A3)*D2+INTERCEPT(B2:B3,A2:A3)
``````

The SLOPE and INTERCEPT functions specify the range of Y values, then the range of X values. Since the value you want to interpolate lies between the first and second data points, those are the values used on the formula. For any other X value, use the data points it falls between.

When you hover over a data point in an Excel chart, a tool tip comes up with the data point’s values. When you hover on the line between two data points, you’ll get the value of the closest data point, not an interpolation of the value for the current position. Excel is not built to deliver that.

The sample chart you show does not seem to be a function, but arbitrary data. You’d need to use a worksheet formula to calculate the desired result, for example I broke down the graph into smaller graphs, only graphing 0-10 ml and then displaying the equation on chart, then graphing only 10-20ml, and 20-30ml is just a straight line so any buret reading between the range of 20-30ml will always have a correction value of -0.01ml.

Then using the equations provided by Excel, I obtained the y-value for a certain range of readings by plugging in the x-value.

Thank you for the help (@fixer1234) and answers!