Maximum value for interpolation

[ile]

Hi
I have a curious problem with interpolated data. My data is particle size
distribution (0-100%), where the last proportion is added to next. The last
values are about 97% and after that are 2-3 blank cells before the maximum
value 100%.

I have plotted the data into XY-scatter chart and interpolated the empty
cells with smoothed lines. The interpolation gives curves that go above 100%
after the said 97% value and return to the maximum 100% in the end. The
interpolation is ok with straight lines, but this way the presentation is not
quite accurate.

My question is that is it possible to set maximum value for interpolated
values i.e. 100%? Please advise.

 

[Bernard Liengme]

It is not clear what you mean by "and interpolated the empty cells with
smoothed lines".
May I suggest you fill the empty cells with =NA(). This will display as #N/A
Excel will ignore these when making the chart.
Please return with further details
best wishes

 

[Jerry W. Lewis]

I agree with Bernad that your question is less than clear.

You can do nonlinear interpolation among more than 2 points. The chart
smoother appears to fit Bezier curves

http://www.xlrotor.com/Smooth_curve_bezier_example_file.zip

which in most instances is not greatly different than cubic splines

http://groups.google.com/group/microsoft.public.excel.programming/msg/6a2966520eccdb1f

However, neither does a great job with monotonic curves that plateau
asymptotically (like a cdf or the tail of a pdf). For a monotonic curve that
plateaus, you may prefer to fit
y=(a+b*x)/(1+c*x)
which can be linearized to y = a+b*x-c*x*y in order to solve for the unknown
coefficients (a,b,c). When c=0, this reduces to linear interpolation.

Jerry

 

[ile]

Hi
Thanks Jerry and Bernard for replies. I tried Bernards NA -trick, but it
didn't do anything for the chart. Jerrys bezier example is somewhat close to
the answer, but I can't figure out how the function works. The programming
example is way too difficult for my skills with excel.

I try to be more clearer with my problem. I attach part of my data in
question. Maybe this will clarify. The top row is sieving slot size (in mm)
and the other three results as percentage.

20 30 40 45 60 78 100
60,8 85,3 98,3 100,0
98,0 100,0
90,5 97,5 100,0

As you see the data is not comparable unless I put it in same chart. The
interpolation I did, makes the curve rise above 100%. So I want the
interpolated curve gradually rise to end at 100% and not going above 100%.

Hope you can help. Many thanks.

 

[Jon Peltier]

Here's my 2¢:

The only reason Excel put the Smoothed Lines feature into the charts is so
management slides look nice. Using smoothed lines is potentially misleading,
particularly if the data points are not indicated with markers. Use straight
lines connecting the points and avoid misrepresentation of the gaps between
the actual data.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______