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.

[TABLE="class: grid, width: 1000, align: left"]
<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>[TR]
[TD="width: 113"]0.726622001[/TD]
[TD="width: 401"]Value is interpolated from columns C&D based on the value of A[/TD]
[TD="width: 59"]0.10[/TD]
[TD="width: 59"]58.2[/TD]
[/TR]
[TR]
[TD]0.13688455[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.20[/TD]
[TD]59.7[/TD]
[/TR]
[TR]
[TD]0.527027184[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.30[/TD]
[TD]62.8[/TD]
[/TR]
[TR]
[TD]0.07190035[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.40[/TD]
[TD]65.1[/TD]
[/TR]
[TR]
[TD]0.498236894[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.50[/TD]
[TD]75.8[/TD]
[/TR]
[TR]
[TD]0.121328227[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.60[/TD]
[TD]86.5[/TD]
[/TR]
[TR]
[TD]0.62808022[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.70[/TD]
[TD]101.5[/TD]
[/TR]
[TR]
[TD]0.859325857[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.80[/TD]
[TD]120.8[/TD]
[/TR]
[TR]
[TD]0.449674319[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]0.90[/TD]
[TD]150.7[/TD]
[/TR]
[TR]
[TD]0.853022132[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD]1.00[/TD]
[TD]187.54[/TD]
[/TR]
[TR]
[TD]0.775082229[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.350342043[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.105732847[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.773225059[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.735979737[/TD]
[TD]Value is interpolated from columns C&D based on the value of A
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.589701218[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.149143409[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.556660035[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0.605821513[/TD]
[TD]Value is interpolated from columns C&D based on the value of A[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[TABLE="width: 298"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]X(Plotted on Log Scale)[/TD]
[TD="colspan: 2"]Y(Normal Scale)[/TD]
[/TR]
[TR]
[TD="align: right"]0.002[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.075[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]0.425[/TD]
[TD="align: right"]19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]23[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4.75[/TD]
[TD="align: right"]31[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]45[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]58[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]40[/TD]
[TD="align: right"]96[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]80[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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)))