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

 

[wrighty99]

Jon

What an elegant solution.

Thank you very much.

I appreciate your time in answering my query.

Again many thanks

 

[wrighty99]

Thisoldman

Ive tried your suggestion but the D5:E5 comes up with error #N/A

The data I am using has decimals.

Below is the data of which my E1 value would be 45.05.

Any suggestions, im sure its to do with excel rounding up numbers used in these formulas?

[TABLE="width: 125"]
<colgroup><col width="64" style="width: 48pt;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]80[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]2.62[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]75[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]2.26[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]70[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]2.03[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]65[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.826[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]60[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.7[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]55[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.578[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]50[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.464[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]45[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.348[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]40[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.254[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]35[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.166[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]30[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.09[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]25[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]0.986[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]20[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]0.922[/TD]
[/TR]
</tbody>[/TABLE]

 

[wrighty99]

Jon Peltier

I have tried your solution but it doesn't seem to work it throws up an error #N/A in both formulas.

I used the following formulas

=MATCH(H20,E15:E27) which is in H22
=(H20-INDEX(E15:E27,H22))/(INDEX(E15:E27,H22+1)-INDEX(E15:E27,H22))*(INDEX(F15:F27,H22+1)-INDEX(F15:F27,H22))+INDEX(F15:F27,H22) which is in H25

The value in H20 = 45.05

The table is below of which the 80 in the table is in E15 and the 2.62 is in F15

Could you please help to resolve.

[TABLE="width: 125"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]80
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]2.62
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]75
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]2.26
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]70
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]2.03
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]65
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.826
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]60
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.7
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]55
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.578
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]50
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.464
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]45
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.348
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]40
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.254
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]35
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.166
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]30
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]1.09
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]25
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]0.986
[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent"]20
[/TD]
[TD="class: xl65, width: 103, bgcolor: transparent"]0.922
[/TD]
[/TR]
</tbody>[/TABLE]

 

[thisoldman]

MATCH is sensitive to the sort order of the lookup array.

The MATCH function I used was set up to look through an ascending (increasing) column of values as you showed in post 5. Your data now shows a descending (decreasing) sort in the columns.

Untested. For the descending order, change the formulas to

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,)

They are still two-cell array formulas and must be entered with Ctrl+Shift+Enter.

 

[wrighty99]

Thisoldman

Thankyou for your help again.

I will give that a try

 

[wrighty99]

Hi thisoldman

I have tried your new suggestion and it doesnt throw up any errors but it uses the wrong values.

In H20 i have 71 which was your E1

In H22 i have =SLOPE(I24:I25,H24:H25)*H20+INTERCEPT(I24:I25,H24:H25) giving the answer 1.972 but this should be just over 2.03

In H24&I24 i have {=INDEX(E15:F27,MATCH(H20,E15:E27,-1),)} giving the answer 75 & 2.26 but this should be 70 & 2.03

In H25&I25 i have {=INDEX(E15:F27, MATCH(H20,E15:E27,-1)-1,)} giving the answer 80 & 2.62 but this should be 75 & 2.26

Attached is the spreadsheet screen capture.

Would be very grateful if you could help with the solution please.

 

[wrighty99]

Hi thisoldman

Problem solved the formula required is below.

H24 =INDEX(E15:F27,MATCH(H20,E15:E27,-1)+1,)
H25 =INDEX(E15:F27, MATCH(H20,E15:E27,-1),)

Thank you for your assistance

 

[thisoldman]

I'm glad to hear that you worked it out.