# Finding y-axis value

##### New Member
Hi and top of the day,

I'm a newbie here, so please excuse my kinda silly question.

Anyway, i have a problem with excel formula. Attached is the example of the data that i have in excel.

I have the MW and MC values from which i can make a graph that looked like this.

So what i want to do next is, i want to find the value of pi1, pi2, pi3, and pi4 (which is the y-axis value) by refferring to the value of component 1, component 2, component 3, and component 4 respectively. I tried using LOOKUP function, but i'm not happy with the given value as it gives the nearest value instead of the exact one.

Can anyone please help me? I really need some help on this. Thanks in advance

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### KRice

##### Well-known Member
Welcome to the board! Could you offer some additional details, please? What do MW and MC represent? How would one know which data points correspond to component 1, component 2, etc.? Are you looking for a fairly substantial change in the slope of the MC vs. MW plot to indicate when the transition occurs from one component to the next? What are the expected answers for example 11 in your plot? Are they about 19, 22, 90, and 152 for components 1, 2, 3, and 4, respectively?

##### New Member
Thank you so much KRice for responding.

Sorry for the lack of info that i gave before this. Let me put it this way.

I have 3 years of data. Where each year i have 2 sets of data, the X and Y.
So, every year i have to find the X values (X11, X21, X31,........, X43) from the given Y values (Y11, Y21,.........Y43).

Previously what i did is,

I want to find the X11 (x-axis value) when Y11 = 2534.
I used, LOOKUP(A21,A4:A19,B4:B19) to which it gives me Y11 = 160.4.

The given answer wasn't a precise one as u can see. So, what can i do to get a precise Y value?

I also read this thread (Getting value on Y axis by putting X axis value), but i couldn't relate since i only have this much data.

#### KRice

##### Well-known Member
This is not clear to me. You have a data set consisting of paired x,y data (which are referred to as MW and MC, respectively). In your first post, you mention a desire to find the y value, not the x value. In your last post, you describe a desire to find X11 given Y11, yet the colored cells in your last post suggest that you do indeed want to specify an x value and determine the corresponding y value. Could you clarify this inconsistency, please, and correct me if MW is not x, and MC is not y.

Is there some relationship expected between MW and MC, or are you simply trying to perform a linear interpolation between the two data points that bound the given input value? Or alternatively, if the input value does not have an exact match, are you trying to find either the smallest value that is greater than the input value, or the largest value that is less than the input value? For example, given the data in your last post, what is the expected answer for X=2432? Since we have known data of (2364.414, 160.4) and (2898.424, 160.92) that bound x=2432, are you expecting an answer of 160.4, 160.92, or perhaps something like 160.466 from linear interpolation, as shown below?

(2432-2364.414)/(2898.424-2364.414) = (y_unknown - 160.4)/(160.92-160.4), and solving for y_unknown, we get y_unknown=160.466

##### New Member

OMG i'm so sorry to make u confused.

"So, every year i have to find the X values (X11, X21, X31,........, X43) from the given Y values (Y11, Y21,.........Y43)" which it is supposed to be "So, every year i have to find the Y values (Y11, Y21, Y31,........, Y43) from the given X values (X11, X21,........X43)."

So, basically i want to find the Y value from the given X value and yes, you are right. The MW represents X and MC represents Y.

There's no mathematical relationship between MW and MC. I just want to find the interpolation between the two data points. What i'm expecting is, the Y value for X=2432 will gives the exact Y value, not larger nor smaller. Did you get what i mean?

I'm so sorry KRice if i didn't write well to make you understand. But i really hope that u can somehow help me. Thanks sooo much in advance.

#### KRice

##### Well-known Member
That's okay. If there is no functional relationship, but you want to rely on linear interpolation, I am assuming that for x=2432, you would want the formula to return a y value of 160.466. Is that correct?

##### New Member

Yes, KRice. That is what i wish i could get.

How can i do so?

#### KRice

##### Well-known Member
This approach uses some helper cells to find the largest x data point that is less than or equal to input x value (and its corresponding y value), and the next larger x data point (and its corresponding y value). A linear interpolation is then performed on those two points to find an estimated y value for the given input x value. This approach may fail if there are duplicate data points, and it will not extrapolate if you were to provide an x value that is larger than the last data point. Let me know if you encounter any issues. You can click on the clipboard icon in the upper left of the table (intersection of row and column headers) to copy this to your clipboard. Then paste into a worksheet in cell A1.
Book1
ABCDEF
1xy
2MWMC
300
4719.02
51319.09
61719.13
72319.28
82919.33
937921.56
1053022.04
1196490.06
12181490.32
132364160.4
142898160.92
15
16InterpolationLowerUpper
17xyxyxy
182432160.46622364160.42898160.92
192263147.5308181490.322364160.4
201991112.873181490.322364160.4
21172090.2912596490.06181490.32
Cell Formulas
RangeFormula
B18:B21B18=(A18-C18)/(E18-C18)*(F18-D18)+D18
C18:C21C18=INDEX(\$A\$3:\$B\$14,MATCH(\$A18,\$A\$3:\$A\$14,1),1)
D18:D21D18=INDEX(\$A\$3:\$B\$14,MATCH(\$A18,\$A\$3:\$A\$14,1),2)
E18:E21E18=INDEX(\$A\$3:\$B\$14,MATCH(\$A18,\$A\$3:\$A\$14,1)+1,1)
F18:F21F18=INDEX(\$A\$3:\$B\$14,MATCH(\$A18,\$A\$3:\$A\$14,1)+1,2)

#### RoryA

##### MrExcel MVP, Moderator

Welcome to the forum.
Please take a minute to read the forum rules, and note the one on cross-posting etiquette, and comply with it. Thank you!

##### New Member
Thanks so much, KRice.
Gonna try it and will get back to you!

Thanks soooo much.

Replies
1
Views
255
Replies
0
Views
197
Replies
10
Views
488
Replies
15
Views
203
Replies
5
Views
70

1,127,273
Messages
5,623,757
Members
415,987
Latest member
alysse

### 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?

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