How to get the corresponding X value for a given Y value?

[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 o
http://hkoyda.blu.livefilestore.com...uCh8zH_ZKlZwXt4POp6d2ay6fhDehApx_wA/graph.JPG

Any help in this matter would be greatly appreciated.
Yahya

 

[Ed Ferrero]

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...uCh8zH_ZKlZwXt4POp6d2ay6fhDehApx_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/tabid/102/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

 

[Jon Peltier]

Hi 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/tabid/102/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

 

[Yahya]

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 point
http://hkoyda.blu.livefilestore.com...x2wgAK-pxgI06jaLLI9u1naBfXsjvAfVDOA/graph.JPG

 

[Mike Middleton]

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

 

[Bernard Liengme]

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

 

[Lori Miller]

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

http://groups.google.com/group/micr...read/thread/2406846f5b6c9d29/09417169ec10d29b

 

[kim.lindsey]

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!

 

[Lori Miller]

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.


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!