Comment from derek
Time: Wednesday, June 24, 2009, 8:34 am

Jon, it looks very nice, but I have a couple of problems installing it. First it objects it “can’t find project or library”, then demands a password. Fortunately cancelling the password prompt results in an installation.

Then I had a problem understanding the instructions, and seeing where the LOESS series was going to be inserted. I think I got it right when I selected the third radio button, Separate XY input and output. Finally, I seem to have lost the ability to Cancel, OK. or Close the application; it responds to mouse clicks by depressing the buttons, but not by closing anything. So I can’t tell what happens in the final stage.

Okay, I killed Excel and restarted. Same password problem on install, but this time the radio button responded with a new range dialog box for the output range. That makes more sense, because I wasn’t sure what was going to happen when I filled in the range input box.

But when I filled the input box and went to fill the output box, something went wrong and I’m back to a stuck Excel. (the output range dialog box has disappeared, which probably happened the first time, but I didn’t notice it)

I’m using Excel 2000 SP3 in XP 5.1 SP3.

Comment from David
Time: Thursday, June 25, 2009, 8:19 am

Jon,
That’s a nice little add-in. I installed it and had it working in Excel 2007 in less than 5 minutes. I tried a basic curve using your default settings and it looks great. I tried tweaking the smoothing parameter up and down, but settled on the default values as the cleanest representation.
Thanks for the tool and the great tips in the blog.

David

Pingback from Mile Wide… Inch Deep… » LOESS in Excel: Big deal?
Time: Tuesday, June 30, 2009, 9:00 pm

[...] its simplicity and power. Take the LOESS function, for example. It’s built in to R. Excel needs a plug-in. Wow. Gotta love R. Used to love Excel, then I grew up (just a [...]

Comment from Brigt E
Time: Tuesday, July 7, 2009, 4:54 pm

Thanks for this one. Used this function today for a wage analysis at my company. By the way I’ve been rather fan of this blog, so keep up the good work. Hopefully my skills will evolve so that I one day can contribute to this community.

Best regards from Norway

Comment from Richard Berthelsdorf
Time: Wednesday, July 29, 2009, 7:43 pm

LOESS is a very neat and useful utility!
I discovered that it refuses to work if the spreadsheet is set to use the R1C1 reference style (I’m using Excel 2003).

Comment from jeff weir
Time: Monday, September 21, 2009, 4:53 am

Hi Jon. I’ve been playing with this utility today, and found that while it does a great job of smoothing fairly small datasets, it doesn’t do so well with the large datasets with fairly regular cyclical swings that I’ve been feeding it today.

I’m guessing this is an artifact of who the LOESS technique works, rather than due to your fine utility.

In your original LOESS post, you say “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.” I’m guessing that the larger your dataset, the larger the moving range around the X value.

I was using it on some half-hourly electricity metering data. When I used the LOESS utility to smooth a week’s worth of data (336 points), I get a great looking curve compared to the rather ‘jagged’ appearance of the raw data. But when I use it to smooth the whole year (17520 points), I get a flat line. I was expecting to get a similar effect for the whole year’s worth of data as I did with a week’s worth…that is I thought I’d still see some swings etc.

Is this how you’d expect LOESS to work with such a large dataset?

Regards

Jeff

Comment from Jeff Weir
Time: Monday, September 21, 2009, 7:37 pm

Ahhh…I didn’t realise you could overwrite the number of points. This utility rocks. I like how it retains the last range you used it on…handy if you want to rerun the function on the same range but just change a setting.

Some thoughts on improvement:

1. One thing that would be better is if when selecting a range you could use Shift + Cntl + Down to select really long ranges rather than having to drag down with the mouse. Is this possible? I see that Name Manager doesn’t let you do this either – so maybe it’s an artifact of constructing such addins?

2. In excel 2007, the PTS menu disappears from the addins ‘menu commands’ if you select the chart. For a moment, I thought it was suddenly uninstalled from my machine.

3. The function can also give a misleading match at each end of the series, because it has less data points with which to perform a regression on points in a moving range around the X value than it does everywhere else (and it might be substituting zero values for any missing data here, but I’m not sure). For an example of how a sine wave is affected, see my uploaded speadsheet at http://cid-f380a394764ef31f.skydrive.live.com/self.aspx/.Public/LOESS%20on%20SineWave.xls

To counter this, you could amend the function so that either:
- if it goes to perform a regression with say a moving range of 6 points then it doesn’t return data for the first 3 or the last 3 values; or
- in the above case, it substitutes any missing data from data it does have. For instance, in the above case, for the first point it would use the first 3 numbers twice instead of a moving range that has missing data points.

Alternately, the user could simply amend their range so that they use a subset of the LOESS function output that doesn’t include end values. But they might not be aware of this.

Regardless, this is staying on my ribbon! THanks Jon

Comment from DaleW
Time: Monday, October 5, 2009, 11:23 pm

Nice utility, Jon. Am I right that it needs at least 15 input points to avoid generating an overflow error, or am I doing something it wasn’t expecting?

Comment from jeff weir
Time: Monday, October 5, 2009, 11:29 pm

I got an overflow error one time when I didn’t select one of the output ranges properly. Maybe this is what’s going on for you, Dale?

Comment from DaleW
Time: Tuesday, October 6, 2009, 12:26 am

Well, Jeff, I got the overflow error roughly 20 for 20 times when I selected 14 pairs or less, and 0 for 8 times when I selected 15 pairs or more. Looks systematic to me. I even duplicated it from scratch with a new workbook.

Each time I was using the contiguous range option. It can accept fewer input points for separate I/O ranges without the overflow error, but with small N the output still isn’t right.

Since you aren’t having this problem, I’ll try again tomorrow with Excel 2003 on a different PC.

Comment from Jeff Weir
Time: Tuesday, October 6, 2009, 1:50 am

Interesting…if you do a pass with say 12 points using the default N (i.e. Number of points for the moving regression) of 5, then it bombs out. If you try again with N of 6, then it works. And then if you try again with N of 5, it works.