Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bezier Smoothing Algoritm Used in Excel Charts
I understand I'm a bit late to the party on this, but I believe this is still the best attempt to crack the smoothing function, at least that I could find!
Given that, I just want to ask for an explanation of why the bezier calculations have to be done in screen coordinates for this tool (as opposed to chartool). I modified the example file to do it in user coordinates, and got the same correct values in the example file and the same error with the supplied data suggested in this thread. (I simply removed Modules 2 and 3, and edited Module 1 to remove the user<--screen transformation. Thanks so much for this, though, it's amazing! Warmest regards, Yaki On Tuesday, March 25, 2003 1:44:05 AM UTC-4, Brian Murphy wrote: Hello Jerry, That's a good observation. Excel does use a 4 point Bezier, I'm quite confident of that. The poor match with the points you provided is, I'm pretty sure, due to a shortcoming in excel. The Bezier calculations have to be done in screen coordinates (points or pixels, take your pick), and then converted to the axis scales of the chart. These conversions can't always be done accurately because excel rounds off the PlotArea.left/width/top/height properties. My chartool utility uses a slightly more sophisticated algorithm in converting user coordinates to screen coordinates, and back again. When using chartool to show a crosshair cursor on the excel smooth curve of your data set, it tracks the curve quite well where the simpler function in the Bezier Example file does not do too well. If you're curious, look through the code in chartool to see what it's doing that's different. There is a function named myPlotAreaInsideLTWH(). Brian "Jerry W. Lewis" wrote in message ... Bezier curves are a much better approximation to the Excel chart smoother than cubic splines, but the correspondence does not seem to be exact. Consider the following data x y 1 3 2 2.95 3 1 4 0.91 5 0.905 6 0.9025 7 0.90125 =FEvaluate_Bezier($A$1:$A$7,$B$1:$B$7,1,1,2,3,$A9, "Chart 1") is noticeably different than the chart smoother for 1<x<2. While less noticeable, if you set the y-axis scale fine enough =FEvaluate_Bezier($A$1:$A$7,$B$1:$B$7,2,3,4,5,$A35 , "Chart 1") does not match the chart smoother for 3<x<4. Jerry Brian Murphy wrote: I'm posting this at the suggestion of MVP Jon Peltier. A file that demonstrates how to draw a smooth curve that matches excel's smooth curve can be found at: www.xlrotor.com/excel_stuff.htm The file contains a user defined function that computes the xy coordinates of a curve that is shown to match the one drawn by Excel. Anyone wanting to integrate to get the area under excel's smooth curve may find it handy. If you find it useful, please let me know about it. This is one those things I spent way too much time on, but hopefully it will save someone else a lot time someday. Regards, Brian Murphy Austin, Texas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Smoothing Data in Columsns in Excel 2012 | Excel Worksheet Functions | |||
excel's curve-smoothing algorithm | Charts and Charting in Excel | |||
Smoothing Line Graphs - Excel 2000 | Excel Discussion (Misc queries) | |||
double exponential smoothing in MS Excel? | Excel Discussion (Misc queries) | |||
interpolation XY data with bezier curver | Charts and Charting in Excel |