-1

How can i get a value from an Excel graph?

Meaning the Y-val of and X which isn't in the input data.

For example in the graph - get the value for x=2.5?

Example

D. Marks
  • 1
  • 1
  • as far as I know you can't get it directly from the graph. You can however look it up in the data tables used to create the graph. depending on the level of accuracy, or the nature of the function (line) connecting your points, you can look at excel built in functions like LINEST, TREND, FORECAST – Forward Ed Mar 28 '16 at 15:42
  • SLOPE and INTERCEPT might be useful to you as well. – Forward Ed Mar 28 '16 at 15:56
  • All of these use the same regression rule, in order to get an accurate value i need to use only the 2 points of the specific section of the value X - which means i can calculate the Y-val without these functions. – D. Marks Apr 04 '16 at 07:51
  • With your example data, you would need to make an IF statement and come up with three equations for the line. One for each straight portion. You can do that using the formulas mentioned above. You then use nested IF statements. IF (X<=Value1, Line Formula 1, if (X<=Value2, Line Formula 2, Line Formula 3)). – Forward Ed Apr 04 '16 at 16:55
  • Another approach, would be to look up the X value you are wanting in your data table. Return the value below it and the value above it. Then with those two known points interpolate in between and grab your value. Again the interpolation can be done with the formulas above. And again it only works accurately for straight lines – Forward Ed Apr 04 '16 at 16:57

1 Answers1

0

For your example data, assuming the X values are in L4:L9 and the Y values are in M4:M9, and the X value you are interested in finding the Y value for is in O4, the following really ugly and unreadable formula would work for you:

=IF(O4=MAX(L4:L9),INDEX(M4:M9,MATCH(O4,L4:L9,0)),(INDEX(M4:M9,MATCH(O4,L4:L9,1)+1)-INDEX(M4:M9,MATCH(O4,L4:L9,1)))/(INDEX(L4:L9,MATCH(O4,L4:L9,1)+1)-INDEX(L4:L9,MATCH(O4,L4:L9,1)))*O4+INDEX(M4:M9,MATCH(O4,L4:L9,1))-((INDEX(M4:M9,MATCH(O4,L4:L9,1)+1)-INDEX(M4:M9,MATCH(O4,L4:L9,1)))/(INDEX(L4:L9,MATCH(O4,L4:L9,1)+1)-INDEX(L4:L9,MATCH(O4,L4:L9,1)))*INDEX(L4:L9,MATCH(O4,L4:L9,1))))

This will work provided the value in O4 is not less than the first value of X in your series nor greater than the last value of X in your series.

No I did not develop that in a single cell. I developed it through a series of helper cells and then back substituted them in to the equations that referenced them.

This is the sheet showing the basic principals and back substituting for the slope of the line.

Example 1

now to find your X1 and your X2 relative to your wanted value of x we performed an INDEX/MATCH combination to find your points with in your data. In order to find X1, the following formula was used in cell Q4:

=INDEX(L4:L9,MATCH(O4,L4:L9,1))

The MATCH returns the position of the number that is less than or equal to the number you are looking for as x (value of O4). INDEX Returns that value in a column of data at a given point. In this case it returns the value in the range L4:L9 that is in the position of the value of the MATCH function. So now we need to find the value for X2. This would be +1 positions down the list. So we reuse the formula but insert the +1 and get the following in cell Q5:

=INDEX(L4:L9,MATCH(O4,L4:L9,1)+1)

Now this works great provided the value in match is not returning the last value in the list. When this happens, the +1 will tell index to look for something outside of the list and it will return an error. That special case is dealt with in the last formula as I did not want to do special case treatment for both the X2 and Y2 lookups separately. Speaking of the Y1 and Y2 values, they are found the same way for the match portion, the look up range for index just needs to be moved to a new range. Instead of using L4:L9 which represents the Xs, M4:M9 is used to look up the Y values. So in cells R4 and R5 we get the following formulas:

=INDEX(M4:M9,MATCH(O4,L4:L9,1))

=INDEX(M4:M9,MATCH(O4,L4:L9,1)+1)

So now if you want to get that all into 1 cell, you wind up copying a few formulas and wind up with the big ugly equation that is at the top. Now the key thing to this whole approach is you are working with straight lines between points.

As always there are other ways of achieving this, this is just one approach.

OOPS Error catching

So I talked about it but forgot to show it. The special case of what to do when we are dealing with the last entry of the list assuming we are not using data validations to prevent such entries. I used the following chunk of code before my final big built up formula.

IF(O4=MAX(L4:L9),INDEX(M4:M9,MATCH(O4,L4:L9,0)),BIG GORMULA)

Basically I am saying that IF we are using the last value in the list, just return the last value in the Y list. Otherwise, use the big formula. Now I could have done this at the X2 look up, and I could have done the same thing for the Y2 lookup. Which meant it would have been substituted back into the bigger equation multiple times making it look longer and more ugly than it already is.

Now you could add another statement to trap unacceptable 04 entries such as:

IF(OR(O4<MIN(L4:L9),O4>MAX(L4:L9)),"Unacceptable entry", DO THE FORMULA)

Another way would be to use DATA VALIDATION on cell O4.

Community
  • 1
  • 1
Forward Ed
  • 9,484
  • 3
  • 22
  • 52
  • I've implemented the interpolation for this example with similar concept. – D. Marks Apr 07 '16 at 11:09
  • I was looking for some Excel Tool or special formula for 2 point interpolation from multi-point graph, something that would be better and cleaner. Thanks. – D. Marks Apr 07 '16 at 11:16
  • not that I am aware of. I have a design spreadsheet for bridges that actually uses the approach above to pull the Y value from graph based on the temperature which is on the x axis. If you are allowed to use helper cells, you can make this look slightly better by finding your two coordinates n the list then using trend or forcast on those two points instead of developing the slope for the line. – Forward Ed Apr 07 '16 at 16:37