Excel chart smoothing algorithm

artz

Well-known Member
Joined
Aug 11, 2002
Messages
830
Office Version
  1. 2016
Platform
  1. Windows
I recently showed an Excel chart during a presentation on which I had used the built in Excel option of "smoothed line" from the Format Data Series menu. I was challenged as to the validity of the smoothing and was asked to identify the algorithm used by Excel. Do we have an expert in the group who could show how Excel smoothes lines in the graphs? A sample worksheet would help.

Thanks,

Art
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Excel uses Bezier curves, which is nearly equivalent to cubic splines.

Why would it matter? :)
 
Upvote 0
Thanks. Looked at the cubic bezier worksheet though I am still not sure how to use this to smooth a data set. Can I get Excel to give me the data points it uses in the smoothed line plot? The question came up whether the smoothed data point values were representing the original data faithfully. -Art
 
Upvote 0
Smoothing doesn't affect your data points, it's just an alternative method of connecting the dots. Hence, the issue of whether they're representing the "original data" doesn't really apply. Smoothing only affects how the interpolation makes the discrete data points into a continuous trend. For example, if your data has points for x = 1 and x = 2, smoothing would give a "not necessarily" guess as to what the value at x=1.5 would be, as would linear approximating.

It sounds like the issue your skeptic has is whether you can accurately infer what that value is, and the answer is "no", but you can take an educated guess. You can forecast, spline, trend, regress, etc. But, at the end of the day, the Intermediate Value Theorem doesn't apply to discrete functions.

I'd explain it that way, or switch your graph to an X-Y scatter plot with just discrete points if your skeptic has about as much of a chance of understanding what cubic beziers are as my boss would.
 
Upvote 0
Excellent response Oaktree. Sometimes those above us should realize that there is a reason they couldn't do our jobs in the first place.

Sounds like your challenger would like to see a chart with the possible error in it. Not seeing your chart, I can only guess.

One option would be to show the straight line approximation along with (i.e. another series) the smoothed line. You could also add another couple of series with including the error (max and min) and plot those. You would then have a mathematical window where your would expect the line to fall.

As a caveat, if you're looking at some type of analysis that follows a 6th order polynomial and only have a few data points, Excel is not going to do the trick.

K

Edit: I assume the OP was using XY in the first place. If not, all bets are off!
 
Upvote 0
kkknie said:
Excellent response Oaktree.

(y) Thanks, KKK. (There's something I never thought I'd write :whistle: )

kkknie said:
As a caveat, if you're looking at some type of analysis that follows a 6th order polynomial and only have a few data points, Excel is not going to do the trick.

You could, however, add a 6th degree polynomial trendline to your graph and have Excel display the equation on the chart.
 
Upvote 0
Oaktree,

Having those initials has been a bit of a burden for quite some time. Sad that my dad is a very dimutive (assumingly as non-racist as a 68 year old American can be) ex-high school english teacher that thought it would be cute...

My point on the polynomial issue is that if the underlying mathematics were not known (I'm a Chemial Engineer and there are plenty of things I work with like this), a chart just with a few points could be graphed plenty of ways. In fact, I barely got through a junior-level project for just this reason.

K (notice I use only one)
 
Upvote 0
Agreed. Didn't mean to suggest that Excel is a good tool for advanced numerical methods techniques, just that it has one option for fitting a 6th degree to your data.
 
Upvote 0

Forum statistics

Threads
1,215,615
Messages
6,125,857
Members
449,266
Latest member
davinroach

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top