Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
I have a chart in Excel 2007 and I want to provide a Y value (that is not one
of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...x_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
Hi Yahya,
I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...x_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya You can't do that without knowing the formula for your line of best fit. There is a sample here http://www.edferrero.com/ExcelCharts...2/Default.aspx called 'Reading Interpolated Values' that works for a straight line. The sample uses click-and-drag on the chart which will not work in Excel 2007. However, you can manually change the x-Value at cell E4 to calculate the intercept at a particular x-Value. Looking at your picture, I would try calculating the log of the y-Values and charting that. If this is close enough to a straight line for your purposes, then just use my sample to calculate the intercept. =LN() to find the log, =EXP() to change back. Ed Ferrero www.edferrero.com |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
"Ed Ferrero" wrote in message ... Hi Yahya, I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...x_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya You can't do that without knowing the formula for your line of best fit. There is a sample here http://www.edferrero.com/ExcelCharts...2/Default.aspx called 'Reading Interpolated Values' that works for a straight line. The sample uses click-and-drag on the chart which will not work in Excel 2007. However, you can manually change the x-Value at cell E4 to calculate the intercept at a particular x-Value. Looking at your picture, I would try calculating the log of the y-Values and charting that. If this is close enough to a straight line for your purposes, then just use my sample to calculate the intercept. =LN() to find the log, =EXP() to change back. If the coordinates of the points are known, i.e., the values are in the worksheet, then a stepwise interpolation approach can be implemented. In general, if these are measured points, unless you know the form of the relationship, you should refrain from using smoothed lines to connect the points. Excel uses an arbitrary algorithm for smoothing the lines, which has no physical bearing on the chart, and which may cause the smoothed line to deviate substantially from a well-behaved relationship. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
"Jon Peltier" wrote: "Ed Ferrero" wrote in message ... Hi Yahya, I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...x_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya You can't do that without knowing the formula for your line of best fit. There is a sample here http://www.edferrero.com/ExcelCharts...2/Default.aspx called 'Reading Interpolated Values' that works for a straight line. The sample uses click-and-drag on the chart which will not work in Excel 2007. However, you can manually change the x-Value at cell E4 to calculate the intercept at a particular x-Value. Looking at your picture, I would try calculating the log of the y-Values and charting that. If this is close enough to a straight line for your purposes, then just use my sample to calculate the intercept. =LN() to find the log, =EXP() to change back. If the coordinates of the points are known, i.e., the values are in the worksheet, then a stepwise interpolation approach can be implemented. In general, if these are measured points, unless you know the form of the relationship, you should refrain from using smoothed lines to connect the points. Excel uses an arbitrary algorithm for smoothing the lines, which has no physical bearing on the chart, and which may cause the smoothed line to deviate substantially from a well-behaved relationship. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ Yeah I have the points coordinates, but I don't know how to do an interpolation Here is a snapshot of my data points http://hkoyda.blu.livefilestore.com/...VDOA/graph.JPG |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
Yahya -
You might get a good fit using a logistic function. Use Google to search for "excel logistic curve" (without the quote marks). But, when possible, curve fitting should rely on knowledge about the physical phenomenon that is being modeled. Please share what you know about the source of the data. Such knowledge is usually important for selecting an appropriate functional form. - Mike http://www.MikeMiddleton.com "Yahya" wrote in message ... "Jon Peltier" wrote: "Ed Ferrero" wrote in message ... Hi Yahya, I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...x_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya You can't do that without knowing the formula for your line of best fit. There is a sample here http://www.edferrero.com/ExcelCharts...2/Default.aspx called 'Reading Interpolated Values' that works for a straight line. The sample uses click-and-drag on the chart which will not work in Excel 2007. However, you can manually change the x-Value at cell E4 to calculate the intercept at a particular x-Value. Looking at your picture, I would try calculating the log of the y-Values and charting that. If this is close enough to a straight line for your purposes, then just use my sample to calculate the intercept. =LN() to find the log, =EXP() to change back. If the coordinates of the points are known, i.e., the values are in the worksheet, then a stepwise interpolation approach can be implemented. In general, if these are measured points, unless you know the form of the relationship, you should refrain from using smoothed lines to connect the points. Excel uses an arbitrary algorithm for smoothing the lines, which has no physical bearing on the chart, and which may cause the smoothed line to deviate substantially from a well-behaved relationship. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services, Inc. http://PeltierTech.com/WordPress/ _______ Yeah I have the points coordinates, but I don't know how to do an interpolation Here is a snapshot of my data points http://hkoyda.blu.livefilestore.com/...VDOA/graph.JPG |
#6
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
Email me privately (remove TRUENORTH.) , I have a sample file to do a four
parameter fir to logistic data Then you use Solver to back solve from x to y best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Yahya" wrote in message ... I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...x_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya |
#7
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
if you're using the "smoothed line" charting option, try this formula
with data in the range A4:B14 and an x-value in D4: =SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2; 1,-3,3,-1},OFFSET(A4,MATCH(D4,A4:A14,-1)-2,,4)-D4 )))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1; -1,3,-3,1},OFFSET(B4,MATCH(D4,A4:A14,-1)-2,,4)))/2 this returns the corresponding y-value within an interior interval. These curves are widely used in computer graphics - for more info check out "Catmull-Rom splines". (Ed Catmull recently won an award at the Oscars!) The curve that Excel plots makes a couple of tweaks to the textbook example: - End intervals are calculated by extending the range at both ends i.e. using the points (0.765,99.7) and (0.04,-3.683) in rows 3 and 15 respectively and adjusting the ranges in the formula accordingly. - Small intervals have a higher "tension" value which has the effect of reducing the overshoot. This value depends on the chart scale used, a VBA function for this is he http://groups.google.com/group/micro...417169ec10d29b "Yahya" wrote: I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...x_wA/graph.JPG Any help in this matter would be greatly appreciated. Yahya |
#8
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
Hi Lori, I saw several posts from you explaining how to get a
corresponding y for any x value using the Catmull-Rom spline, but I still can't quite understand how the data should be arranged and where the formula should go to make it work. Can you say a little more about that? Thank you! On Mar 3, 9:16*pm, Lori Miller wrote: if you're using the "smoothed line" charting option, try this formula with data in the range A4:B14 and an x-value in D4: =SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2; 1,-3,3,-1},OFFSET(A4,MATCH(D4,A4:A14,-1)-2,,4)-D4 )))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1; -1,3,-3,1},OFFSET(B4,MATCH(D4,A4:A14,-1)-2,,4)))/2 this returns the corresponding y-value within an interior interval. These curves are widely used in computer graphics - for more info check out "Catmull-Rom splines". (Ed Catmull recently won an award at the Oscars!) The curve that Excel plots makes a couple of tweaks to the textbook example: - End intervals are calculated by extending the range at both ends i.e. using the points (0.765,99.7) and (0.04,-3.683) in rows 3 and 15 respectively and adjusting the ranges in the formula accordingly. - Small intervals have a higher "tension" value which has the effect of reducing the overshoot. This value depends on the chart scale used, a VBA function for this is he http://groups.google.com/group/micro...harting/browse... "Yahya" wrote: I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...oxLQUtlTXmrhiI... Any help in this matter would be greatly appreciated. Yahya |
#9
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
OK, let's take the original data set, with data entered into the
range A4:B14 and draw an XY chart as shown in the OP. X Y 0.71 99.145 0.655 98.59 0.5125 97.99 0.3375 97.61 0.215 94.51 0.1525 84.21 0.1155 50.26 0.098 33.939 0.0825 27.062 0.064 9.797 0.052 3.057 DATA INTERPRETATION The y data range lies between [0,100] and is increasing with x, and i would guess that this is a distribution function of a statistical sample of some kind. If so, using splines for estimation is valid and common in the technical literature and in this case the Excel curve looks like a reasonable approximation. (If these were measurements subject to a degree of error however, other methods may be more appropriate, such as regression, as mentioned by other posters.) ESTIMATION i. To estimate a y-value, enter the x-value in D4 and copy the formula shown in the last post into E4. eg x = 0.13 - y = 66.316 If you fix the ranges by using A$4,B$4 and A$4:A$14 in the formula you can pull the fill handle down to create a range of x and y values. Charting these values should match the curve that Excel plots. ii. Actually the original post called for estimating an x-value given a y-value which can be done with the same formula but just switching X with Y ie by interchanging A's and B's in the formula. eg y = 0.5 - x = 0.115 Also the values are arranged in descending order, often data would be arranged ascending so that 1 instead of -1 is used in the MATCH function. ADDITIONAL NOTES i. Note that these types of curve (cardinal splines) do not assign values to end intervals. The method Excel uses to plot these intervals is equivalent to adding an extra data point at each end. You can do this by selecting the range A4:B5 and dragging the fill handle up to row 3 and then selecting A13:B14 and dragging down to row 15, this should give the same values as before, and the formula result should now match the curve eg x = 0.06 - y = 7.047 ii. The tension adjustment that Excel uses is only noticable when points are sufficiently irregularly spaced which is not the case here. Specifically, if the distance between neighbouring points is less than a third the distance between correspopnding alternate points (as measured on the chart) a proportional tension adjustment is made. This is accounted for in the Chartcurve UDF. wrote in message ... Hi Lori, I saw several posts from you explaining how to get a corresponding y for any x value using the Catmull-Rom spline, but I still can't quite understand how the data should be arranged and where the formula should go to make it work. Can you say a little more about that? Thank you! On Mar 3, 9:16 pm, Lori Miller wrote: if you're using the "smoothed line" charting option, try this formula with data in the range A4:B14 and an x-value in D4: =SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2; 1,-3,3,-1},OFFSET(A4,MATCH(D4,A4:A14,-1)-2,,4)-D4 )))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1; -1,3,-3,1},OFFSET(B4,MATCH(D4,A4:A14,-1)-2,,4)))/2 this returns the corresponding y-value within an interior interval. These curves are widely used in computer graphics - for more info check out "Catmull-Rom splines". (Ed Catmull recently won an award at the Oscars!) The curve that Excel plots makes a couple of tweaks to the textbook example: - End intervals are calculated by extending the range at both ends i.e. using the points (0.765,99.7) and (0.04,-3.683) in rows 3 and 15 respectively and adjusting the ranges in the formula accordingly. - Small intervals have a higher "tension" value which has the effect of reducing the overshoot. This value depends on the chart scale used, a VBA function for this is he http://groups.google.com/group/micro...harting/browse... "Yahya" wrote: I have a chart in Excel 2007 and I want to provide a Y value (that is not one of the data points) and have Excel to tell me the corresponding X value from the chart. How can I do that? The chart is not a straight line, so trendline won't work Here is a picture of the chart I'm working on http://hkoyda.blu.livefilestore.com/...oxLQUtlTXmrhiI... Any help in this matter would be greatly appreciated. Yahya |
#10
Posted to microsoft.public.excel.charting
|
|||
|
|||
How to get the corresponding X value for a given Y value?
wrote in message
http://help.lockergnome.com/office/d...ct1005590.html Hey this really works! I sincerely appreciate the hand-holding youve offered me here. I have been trying to figure out a good way to do this for some time and I think your method will work very nicely for me. Thank you! Glad it worked for you - don't know why these replies are dropping out? I spent a long while figuring this out too. I'd initially been using goal seek with a parameter value but then realised you could make use of the IRR function to solve the cubic eqn. It seemed to work in my test scenarios. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|