Bezier Smoothing Algoritm Used in Excel Charts

[Brian Murphy]

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

[Jon Peltier]

Thanks, Brian.
_______

[Jerry W. Lewis]

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]

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" <post_a_reply@no_e-mail.com> wrote in message news:<3E771EF4.5080901@no_e-mail.com>...

[yaac...@gmail.com]

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