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
|