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

Asked By Yahy on 01-Mar-09 06:54 PM
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/y1p8aB149EOMrBPPjH7oxLQUtlTXmrhiIspRq3r8fuZBNtdjVL_9ckuCh8zH_ZKlZwXt4POp6d2ay6fhDehApx_wA/graph.JPG

Any help in this matter would be greatly appreciated.
Yahya




Ed Ferrero replied on 01-Mar-09 08:12 PM
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.

Ed Ferrero
www.edferrero.com
Jon Peltier replied on 01-Mar-09 11:40 PM
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/
_______
Yahy replied on 02-Mar-09 01:25 AM
Here is a snapshot of my data points
http://hkoyda.blu.livefilestore.com/y1pgZrqLtEzUywZot1wKtjvEbwQZtl0v7j1OArIQLJovsDzqfsixklx2wgAK-pxgI06jaLLI9u1naBfXsjvAfVDOA/graph.JPG
Mike Middleton replied on 02-Mar-09 02:40 AM
Yahya  -

You might get a good fit using a logistic function. Use Google to search for

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 replied on 02-Mar-09 08:19 AM
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
LoriMille replied on 03-Mar-09 08:16 PM
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

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/microsoft.public.excel.charting/browse_thread/thread/2406846f5b6c9d29/09417169ec10d29b
kim.lindse replied on 28-Mar-09 11:59 AM
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=A0pm, Lori Miller <LoriMil...@>
ut
le:
sing
ot one
from
Lori Miller replied on 26-Mar-09 09:26 PM
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!



On Mar 3, 9:16 pm, Lori Miller <LoriMil...@>
Lori Miller replied on 06-Apr-09 11:21 PM
http://help.lockergnome.com/office/default--ftopict1005590.html

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.