LOESS Smoothing in Excel | PTS Blog

Description

In 1979 William Cleveland published the LOESS (or LOWESS) technique for smoothing data, and in 1988 he and Susan J. Devlin published a refined version of the technique (references are given at the end of this article). For each X value where a Y value is to be calculated, the LOESS technique performs a regression on points in a moving range around the X value, where the values in the moving range are weighted according to their distance from this X value.

The NIST Engineering Statistics Handbook has a good description of the LOESS technique, including a worked example. A commenter named Nick used the NIST chapter as a starting point for his implementation of a LOESS function for Excel, and he posted in in a comment on JunkCharts . Nick’s approach was to create a UDF in VBA. The UDF accepts as inputs the X and Y data ranges, the number of points to use in the moving regression, and the X value for which to calculate Y. Nick’s UDF used Dectionary objects to hold intermediate values, and it outputs the Y value for the input X value.

I’ve expanded on Nick’s starting point, and produced the function presented later in this article. I’ve discarded the Dictionary objects in favor of VB arrays. It accepts the input X and Y data and the output X values either as ranges or as vertical arrays, and it outputs the calculated LOESS Y values as a vertical array. This means it can be called from within other procedures using arrays, or as a UDF from a worksheet, as an array formula. The original data must be sorted by X in either ascending or descending order (Nick’s Dictionaries do not require sorted input data, and I have an idea to remove the requirement ...

Original URL

http://peltiertech.com/WordPress/loess-smoothing-in-excel/

http://peltiertech.com/WordPress/loess-smoothing-in-excel/