Getting value on Y axis by putting X axis value

K

kashifjillani

New Member
Joined
Jul 26, 2011
Messages
47
Hello to all the experts of Excel,

I still can not figure out the way to get the values in a chart/graph by putting an specific X axis value.

e.g.

There is relation between Gauge height and Flow

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=128><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 width=64>Gauge</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=64>Flow </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.305</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.32</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.10022</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.341388</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.27539</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.381347</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>0.85528</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.401708</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1.32224</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.413752</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>1.69248</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.433354</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>2.46828</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.452996</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>3.43106</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.482379</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>5.3022</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.511557</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>7.77429</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.522962</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>8.9451</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.561209</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>13.53592</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 align=right>0.589</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" align=right>17.68</TD></TR></TBODY></TABLE>


If I draw the curve with Flow on X-axis and Gage on Y-axis and then want to know the flow at Gage 0.53m.

OR

By putting flow value to know Gage height.

Regards,:rolleyes:
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
I arranged your data like this:

interpolationdata.png


Note the horizontal axis variable is in the first column.

Cell B2 is the value we want to match in the second column.
Cell B3 contains this formula:
=MATCH(B2,B6:B18)
Cell A2 contains this interpolation formula:
=(B2-INDEX(B6:B18,B3))/(INDEX(B6:B18,B3+1)-INDEX(B6:B18,B3))*(INDEX(A6:A18,B3+1)-INDEX(A6:A18,B3))+INDEX(A6:A18,B3)

The following chart shows Flow vs. Gauge as blue diamonds, and the interpolated point as a red square:

interpolationchart.png


When I enter 0.35 into cell B2, A2 calculates 0.400368, and the chart looks like this:

interpolationchart2.png
 
Upvote 0
x1.11.21.31.41.51.61.7
0.481.0021.003751.0071.011.0151.02051.027
0.461.002251.00451.00751.0121.01751.0241.032
0.441.00251.0051.0091.0151.021.0271.036
0.421.0031.0061.01051.0161.0231.03151.0415
0.41.00351.00751.01251.0191.0261.0361.048
0.381.0041.00851.01451.02151.03051.04151.054
0.361.00451.011.01551.0251.03551.04751.062
0.341.0051.01161.01951.0291.0411.0551.071
0.321.0061.01351.0231.0341.0471.0631.081
0.31.0071.0151.0261.041.0541.0721.093
0.281.0091.0191.0321.0461.0631.0841.106
0.261.011.0221.0371.0541.0741.0961.121
0.241.0121.0261.0441.0631.0851.1111.14
0.221.0151.0321.0521.0741.11.1271.16
0.21.0171.0371.061.0851.1141.1461.182
0.181.021.0441.071.11.1321.1681.206
0.161.0241.0531.0821.1161.1541.1951.239
0.141.0291.0611.0921.1361.181.2241.272
0.121.0341.0731.1131.161.2061.251.31
0.11.041.0861.1351.1851.2411.2991.357
0.0951.0441.091.1411.1941.251.3091.371
0.091.051.0991.151.2051.2641.3231.387
0.0851.0551.1081.1611.2181.2771.341.407
0.081.0661.121.1751.2351.291.3631.43
0.0751.0761.1341.1931.2551.3181.3881.458
0.071.0911.1511.2131.2771.3451.4151.489
0.0651.1071.1691.2351.3021.3731.4451.521
0.061.1261.1921.261.3331.4051.4831.56
0.0551.1481.2161.2881.3641.441.521.602
0.051.171.2431.3191.41.481.5641.65
0.0451.1951.2751.3551.4381.5241.611.7
0.041.2241.3061.3911.481.5691.661.752
0.0351.2551.3411.4321.5251.621.7151.815
0.031.2881.381.4781.5751.6751.7751.88
0.0251.3241.4241.5251.6281.7341.8381.95
0.021.361.4691.5751.6841.7981.9072.02

<colgroup><col span="8"></colgroup><tbody>
</tbody>

Dear Jon
i have above data in which X value Ranges from 0.48 to 0.02 & Y value Ranges fronm 1.1 to 1.7
actually this is a graph with seven lines.
for example in this if we select value of X=0.48 & Y=1.1 Then results is 1.002

But i want to put values e.g. X=0.196 & Y=1.474
is it possible to do the same.
Thanks
 
Upvote 0
This is much more complicated than a 1D lookup. We need to find not two values our final value is located between, but four values, corners of our lookup grid. We have to interpolate two columns between the rows, then interpolate these two results between columns.

First, sort your X values in ascending order. Here is the top part of my worksheet, with some of the data and with my intermediate calculations. I've shaded the X and Y edges to show where the lookup values lie, and also the 2x2 grid within which our interpolated value will be found.

Interp2Dworksheet.png


The X and Y values being looked up are in cells L5 and M5.

Formulas are as follows:

Matches in X and Y arrays:

L7:
=MATCH(L5,$B$3:$B$38)

M7:
=MATCH(M5,$C$2:$I$2)

The 2x2 grid within which our answer lies (aka the corners):

L9:
=INDEX($C$3:$I$38,L7,M7)
L10:
=INDEX($C$3:$I$38,L7+1,M7)
M9:
=INDEX($C$3:$I$38,L7,M7+1)
M10:
=INDEX($C$3:$I$38,L7+1,M7+1)

The fraction of the way our lookup values are from the lower match to upper match (corners):

L12:
=(L5-INDEX($B$3:$B$38,L7))/(INDEX($B$3:$B$38,L7+1)-INDEX($B$3:$B$38,L7))
M12:
=(M5-INDEX($C$2:$I$2,M7))/(INDEX($C$2:$I$2,M7+1)-INDEX($C$2:$I$2,M7))

The interpolated rows:

L14:
=L9+(L10-L9)*L12
M14:
=M9+(M10-M9)*L12

And finally, our answer:
M16:
=L14+(M14-L14)*M12

I've used a lot of cells for intermediate calculations. I recommend you do as well, because it's easier to trace the calculation this way and fix any errors. But if you insist, here is the formula in cell M14 that uses only the lookup grid and the lookup values:

=INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2))+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38)+1,MATCH(M5,$C$2:$I$2))-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)))*(L5-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))/(INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)+1)-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)+1)+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38)+1,MATCH(M5,$C$2:$I$2)+1)-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)+1))*(L5-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))/(INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)+1)-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2))+(INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38)+1,MATCH(M5,$C$2:$I$2))-INDEX($C$3:$I$38,MATCH(L5,$B$3:$B$38),MATCH(M5,$C$2:$I$2)))*(L5-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)))/(INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38)+1)-INDEX($B$3:$B$38,MATCH(L5,$B$3:$B$38))))*(M5-INDEX($C$2:$I$2,MATCH(M5,$C$2:$I$2)))/(INDEX($C$2:$I$2,MATCH(M5,$C$2:$I$2)+1)-INDEX($C$2:$I$2,MATCH(M5,$C$2:$I$2)))
 
Upvote 0
Dear Sir

I don't have words, to say thank you.

You solve my problem by taking personnel interest

Many Many thanks from my Heart

:)

Kulbir Singh
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
816
gvsumich
G
A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
D
  • Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
139
dilshod_k
D
E
  • Question
VBA for Web Scraping help
Replies
0
Views
579
epactheactor
E

Forum statistics

Threads
1,214,577
Messages
6,120,359
Members
448,956
Latest member
Adamsxl

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top