Getting value on Y axis by putting X axis value

[kashifjillani]

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,

 

[Fysto]

The original post said it wanted to find Y for an input X value, but in the description the opposite was requested.

You should be able to modify my approach to actually input X and get Y.

Jon i right i just changed the B3 Column by adding -1 in the [match_type] and both A2 and B3 show me, #N/A. have been up all night searching for a solution and listening to dead end google suggestion. Please help Jon, you are the only guy hence far who seems to be ontop of this. i have even tried rearranging the columns. Stuck between a rock and a hard place...

 

[Fysto]

The original post said it wanted to find Y for an input X value, but in the description the opposite was requested.

You should be able to modify my approach to actually input X and get Y.

Jon i right i just changed the B3 Column by adding -1 in the [match_type] and both A2 and B3 show me, #N/A. have been up all night searching for a solution and listening to dead end google suggestion. Please help Jon, you are the only guy hence far who seems to be ontop of this. i have even tried rearranging the columns. Stuck between a rock and a hard place...

 

[Fysto]

mac, did you just edit the B3 column, been up all night and maybe the pressure is getting to me but when i just edit the B3 value A2 and B3 show me #N/A.. .

 

[Jon Peltier]

Fysto -

Without knowing what your data looks like, I can't even be sure that changing Match Type to -1 was correct. What's your data like and what are you trying to get?

 

[Fysto]

Fysto -

Without knowing what your data looks like, I can't even be sure that changing Match Type to -1 was correct. What's your data like and what are you trying to get?

Thank you for the concern, but i figured it out. It must have been the pressure. Your method saved me a lot of time and it relieved most of the headache thanks again i am very gratefull.

 

[Gridzone]

HiJon Peltier,

Many thanks for your nice solution here, works a treat

I've been trying to figure out how I can apply this to my spreadsheet and so far I'm failing - any chance of a quick hand?

I have 2 columns, in A I have the value and in B I would like to insert a value which is interpolated from some base data in a seperate table, say columns AA and BB, using your technique?

I hope this makes sense? - in effect I have 2 tables, the first one has a column of readings (A) and a value (B) to be looked up from the second table based on the value of column A.

I'm not sure if this is even possible?

Many thanks in advance.

 

[Gridzone]

Hi Again,

Here is a quick snap of what I was trying to explain in the previous post, hopefully this is clearer?

Thanks again.

0.726622001	Value is interpolated from columns C&D based on the value of A	0.10	58.2
0.13688455	Value is interpolated from columns C&D based on the value of A	0.20	59.7
0.527027184	Value is interpolated from columns C&D based on the value of A	0.30	62.8
0.07190035	Value is interpolated from columns C&D based on the value of A	0.40	65.1
0.498236894	Value is interpolated from columns C&D based on the value of A	0.50	75.8
0.121328227	Value is interpolated from columns C&D based on the value of A	0.60	86.5
0.62808022	Value is interpolated from columns C&D based on the value of A	0.70	101.5
0.859325857	Value is interpolated from columns C&D based on the value of A	0.80	120.8
0.449674319	Value is interpolated from columns C&D based on the value of A	0.90	150.7
0.853022132	Value is interpolated from columns C&D based on the value of A	1.00	187.54
0.775082229	Value is interpolated from columns C&D based on the value of A
0.350342043	Value is interpolated from columns C&D based on the value of A
0.105732847	Value is interpolated from columns C&D based on the value of A
0.773225059	Value is interpolated from columns C&D based on the value of A
0.735979737	Value is interpolated from columns C&D based on the value of A
0.589701218	Value is interpolated from columns C&D based on the value of A
0.149143409	Value is interpolated from columns C&D based on the value of A
0.556660035	Value is interpolated from columns C&D based on the value of A
0.605821513	Value is interpolated from columns C&D based on the value of A

<colgroup><col style="mso-width-source:userset;mso-width-alt:4352;width:85pt" width="113"> <col style="mso-width-source:userset;mso-width-alt:15411;width:301pt" width="401"> <col style="width:44pt" span="2" width="59"> </colgroup><tbody>
</tbody>

 

[Jon Peltier]

You have to think about what you have and what you need:

1. Your last two columns must be the Old X and Old Y values of the table you are using for your interpolation.
2. Your first column are the New X values that you need New Y values for.

Your Old X and Old Y values generate a set of points that produce a sequence of short segments that roughly trace a curve.

In my screenshot below, I show your Old X and Old Y values in columns E and F, and these are plotted in the chart. The New X values are in column A, the New Y values will be computed in column B, and column C contains the result of a MATCH formula which will be used multiple times in the formulas in column B.

The formula in cell C2 is:
=MATCH(A2,$E$2:$E$11)

This tells me which segment of the plotted curve contains our New X value.

The formula in cell B2 is:
=(A2-INDEX($E$2:$E$11,C2))/(INDEX($E$2:$E$11,C2+1)-INDEX($E$2:$E$11,C2))*(INDEX($F$2:$F$11,C2+1)-INDEX($F$2:$F$11,C2))+INDEX($F$2:$F$11,C2)

This is a classic interpolation formula. For more information you could start at my tutorial Excel Interpolation Formulas.

Here I've filled the formulas in B2:C2 into the whole New X and New Y range, and the new data are plotted in the chart.

 

[DrSingh]

Dear Jon
My data set is

X(Plotted on Log Scale)	Y(Normal Scale)
0.002	8
0.075	12
0.425	19
2	23
4.75	31
10	45
20	58
40	96
80	100

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

I want to find X corresponding to Y = 10, 20 and 30.
The scenario is X shows particle sizes of soils (which are always same) and Y shows cumulative percetages of particle sizes which change for each sample and we have to calculate percentage finer than 10, 20 and 30% for different kinds of soils.
Can u help me Jon. Thanks in advance.

 

[Jon Peltier]

Dr Singh -

This is the same as the previous, except that you need to calculate X from Y.

Here's your old data (Old X and Old Y), plotted on two charts, one plotting Y vs X and the other Y vs Log X.

The formula in the Match column finds where the given Y value (column B) falls within the array of Y values (column F). Cell C2 contains this formula, which is filled down to C4:

=MATCH(B2,$F$2:$F$10)

The formula in column A computes X for the given Y value in column B. Cell A2 contains this formula, which is filled down to A4:

=(B2-INDEX($F$2:$F$10,C2))/(INDEX($F$2:$F$10,C2+1)-INDEX($F$2:$F$10,C2))*(INDEX($E$2:$E$10,C2+1)-INDEX($E$2:$E$10,C2))+INDEX($E$2:$E$10,C2)

The orange dots are the looked up points. These points fall exactly on the lines in the Y vs X chart, shown more clearly on the lower tow charts. If you need them to fall exactly on the lines in the Y vs Log X chart, you need to adjust your formula in cell A2:

=10^((B2-INDEX($F$2:$F$10,C2))/(INDEX($F$2:$F$10,C2+1)-INDEX($F$2:$F$10,C2))*(LOG(INDEX($E$2:$E$10,C2+1))-LOG(INDEX($E$2:$E$10,C2)))+LOG(INDEX($E$2:$E$10,C2)))