Plotted graph from a table. Read value from the curve?

[wrighty99]

Hi I wonder if you can help.

I have a table which has been used to create graph curve in excel.

Is there a way of reading a value from the curve?

Many thanks in advance

 

[palaeontology]

why not plot your data as a scatterplot, add a trendline, and choose to display the equation of the trendline on the graph,

you could then use the equation of the trendline to determine the required value.

 

[wrighty99]

Thank you for your reply.

The spreadsheet is for my engineers to take out with them on a tablet.

They would enter the tested results and the spreadsheet then has to do some calcs, then refer to a referencing table, which will report where on the curve (X axis distance) the correct reading can be referenced from the Y axis.

So I was hoping for this to all happen automatically and the sheet would return the value required.

 

[thisoldman]

You can probably adapt something from these.

From chandoo, "Highlighting Data Points in Scatter and Line Charts":
https://chandoo.org/wp/2010/11/11/highlight-data-points-scatter-line-charts/

From EngineerExcel
http://www.engineerexcel.com/2-ways...point-on-the-x-and-y-axes-of-a-scatter-chart/

Both methods will only show points from the plotted data. They won't show interpolated data.

If you run into difficulties, post some dummy data with the solution you want to achieve. People are always willing to help, if they can.

 

[wrighty99]

Thank you for your response.

I have found that you can use =SLOPE(W9:W10,V9:V10)*X9+INTERCEPT(W9:W10,V9:V10).

V W X Y
9 75 2.26 73 =formula
10 70 2.03

Result Y9 = 2.168

Therefore I don't need to graph a graph

However in the formula the cell references cant change.

How can I can those references based on a value.

example:

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

If the value I want to find is 45 how can I get excel to search the X column find that its in between X6 & X7 and use this inside the formula above?

Many thanks in advance

 

[thisoldman]

You're using a calculated, local slope and intercept. I have one solution requiring helper cells and array formulas. I do hope you are familiar with multi-cell array formulas. I hope this works for you.

To enter the formula in D5:E5, select both cells, then type in the formula into D5. Do not type the curly braces. Do not press Enter. Instead, press Ctrl+Shift+Enter. Excel will place the formula into both cells and add the curly braces. Same procedure goes for the formula in D6:E6.

Don't omit the last comma in the INDEX+MATCH formulas. The empty argument after the comma makes Excel return the entire row.

Book1
	A	B	C	D	E
1	X	Y		User's X	35
2	0	0		Calc. Y	-0.015
3	10	0.01
4	20	-0.01		Helper_X	Helper_Y
5	30	-0.01		30	-0.01
6	40	-0.02		40	-0.02
7	50	-0.06
Sheet1

Cell Formulas
Range	Formula
E2	=SLOPE($E$5:$E$6, $D$5:$D$6) * $E$1 + INTERCEPT($E$5:$E$6, $D$5:$D$6)
D5:E5	{=INDEX($A$2:$B$7, MATCH($E$1, $A$2:$A$7, 1),)}
D6:E6	{=INDEX($A$2:$B$7, MATCH($E$1, $A$2:$A$7, 1) + 1,)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jon Peltier]

If you search the forum you'll find dozens of threads about interpolation.

In Getting value on Y axis by putting X axis value I used some lookup formulas to find which pair of points to look for an interpolated value.

 

[thisoldman]

Thank you, Jon. For some reason my searches didn't come up with your thread. I was hoping someone with better math skills than mine would volunteer a solution, and you came through for me.

Now I have to bookmark your solution.

 

[Jon Peltier]

Yeah, the search was not as effective as I'd hoped. I couldn't find any interpolation threads with my user name. In the Mr Excel folder on my hard drive, I have at least three other examples of interpolation, but that one is the only one I had the presence of mind to save the link. More recently I've gotten better at pasting the link into a cell on the worksheet, so I can find it again later.

 

[wrighty99]

Thank you thisoldman

That is perfect.

Will try immediately and report back.

Your help is very much appreciated.