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