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