Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Maximum value for interpolation
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Maximum value for interpolation
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 -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ile" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Maximum value for interpolation
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_...ample_file.zip which in most instances is not greatly different than cubic splines http://groups.google.com/group/micro...2966520eccdb1f 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" wrote: 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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Maximum value for interpolation
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. "Jerry W. Lewis" wrote: 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_...ample_file.zip which in most instances is not greatly different than cubic splines http://groups.google.com/group/micro...2966520eccdb1f 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" wrote: 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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
Maximum value for interpolation
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/ _______ "ile" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding maximum of various ranges of data | Excel Discussion (Misc queries) | |||
find maximum | Excel Discussion (Misc queries) | |||
find which column has the maximum value | Excel Discussion (Misc queries) | |||
Tricky 'Find Maximum' problem seeks formula | Excel Worksheet Functions | |||
Scroll Bar maximum value | Charts and Charting in Excel |