I talked about LOESS smoothing in LOESS Smoothing in Excel, where I showed my improved VBA function for calculating smoothed data. I introduced an improved LOESS Smoothing utility in LOESS Utility for Excel. Since I use the utility frequently, I encountered many things about it that I wanted to change. Since I am the developer of the utility, I have actually been able to make these changes. And after a few months of use and a few hour-long sessions aimed at making it do what I want it to do, I’ve developed this utility into something that is, in the local vernacular, “wicked awesome”. If you’ve never seen “Good Will Hunting”, that means “way cool”.

What makes this utility so great, you ask? The calculations were already perfectly adequate, and I didn’t change them at all. But I’ll show the new dialog, so you can see what has changed.

First, the previous interface for selecting input and output ranges was frustratingly restrictive. So I made the range selection function much more flexible. The colorful buttons on the left indicate the built-in options. In the most elementary case, all four entries (X input, Y input, X output, and Y output) are separate ranges, which can even be located on separate worksheets.

The options make range selection easier if some columns happen to be adjacent to each other, or if the same X values are to be used for both input and output. The range selection (Ref Edit) boxes and related labels update according to which option has been selected.

When the X input range has been selected, the program determines Npts, the number of points that should be used for the selected value of alpha. When the user changes alpha or Npts, the other parameter updates.

In the following case, one column serves as X input and X output (A7:A135), the adjacent column holds the Y input data (B7:B135), and the program will dump the Y output into a separate column (D7:D135).

Even this wasn’t smooth enough for me, because I got tired of scrolling up and down to select long ranges. So I made another improvement. I made the program smart enough to know that if only one row had been selected, it should use the range of data below the selected row, stopping at the first empty cell.

Couldn’t be easier.

The last change I made was to have the utility store the last LOESS parameters in each worksheet. This way, if I run a particular analysis on each sheet in a workbook, I only have to activate that sheet, run the program, and the dialog is pre-populated with the settings I need. What a time-saver!

PTS LOESS Utility Demo

Here is a demo of the new utility. This analysis only took a few minutes. I started with some Global Temperature Anomaly data from NASA.

I ran the utility to generate a smoothed temperature anomaly curve.

Then ran the utility a few more times to see the effect of alpha (number of points in the moving linear regression) on the shape of the smoothed curve. The values of 42, 25, and 12 points correspond to alpha values of 0.33, 0.2, and 0.1.

The fewer points (i.e., smaller alpha) in an analysis, the more that the smoothed curve follows local variations in the data.

Download and Install the Utility

Click this PTS LOESS Smoothing Utility link to download the utility. The download is a simple Excel add-in. Save it to any convenient directory, then install it following the instructions in Installing an Excel Add-In or Installing an Add-In in Excel 2007.

Related Posts:

• LOESS Utility for Excel
• LOESS Utility – What the Buttons Mean
• LOESS Smoothing in Excel
• Deming Regression Utility
• Deming Regression
• Plotting Measured Data
• Announcing the Box and Whisker Chart Utility
• Label Last Point – Updated Add-In
• How to Edit Series Formulas
• Build an Excel Add-In 1 – Basic Routine