Finding y-axis value

husnafezaahmad

New Member
Joined
Oct 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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.

Screen Shot 2020-10-21 at 03.45.22.png

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

1603224784911.png


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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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?
 
Upvote 0
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).

1603265179536.png



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.
Please help me :(
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
husnafezaahmad
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)
 
Upvote 0
@husnafezaahmad,

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!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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