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
:)
 

Some videos you may like

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
Joined
Dec 9, 2003
Messages
1,062
Office Version
  1. 2019
Platform
  1. Windows
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?
 

husnafezaahmad

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

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,062
Office Version
  1. 2019
Platform
  1. Windows
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
 

husnafezaahmad

New Member
Joined
Oct 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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
Joined
Dec 9, 2003
Messages
1,062
Office Version
  1. 2019
Platform
  1. Windows
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?
 

husnafezaahmad

New Member
Joined
Oct 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

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

How can i do so?
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
1,062
Office Version
  1. 2019
Platform
  1. Windows
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)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,453
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
@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!
 

husnafezaahmad

New Member
Joined
Oct 20, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
Thanks so much, KRice.
Gonna try it and will get back to you!

Thanks soooo much.
 

Watch MrExcel Video

Forum statistics

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

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
Top