LOESS Utility – Awesome Update

Comment from Bob
Time: Thursday, October 8, 2009, 12:20 pm

Way to go Jon.

Can’t wait to play with this.

Cheers,

Bob

Comment from Jerry Betz
Time: Thursday, October 8, 2009, 1:52 pm

Jon,

Ayuh, wicked gorgeous. Many thanks.

Jerry

Comment from Kurt
Time: Thursday, October 8, 2009, 3:00 pm

Hi Jon,

tried your LOESS-Tool, it installs a Menu
“PTS Charts” in my Excel 2003. clicking this
menu I have *two* indentical menu-entries
“LOESS”

Do you have any hint, what mistake I made?

thanks

Kurt

Comment from Bob
Time: Thursday, October 8, 2009, 5:24 pm

Hi Jon,

The same thing happened to me in Office 2007 SP2 and all the patches.

Works fine. I also have the error bar utility you cooked up for Excel 2007. Happy camper.

Thanks for your hard work.

Cheers,

Bob

Comment from Kurt
Time: Friday, October 9, 2009, 12:27 pm

Jon,

the sugested procedure did not work for me.anyway it is just a “cosmetic” thing.
thanks for making this tool available for free

Kurt

Comment from Stephen Hughes
Time: Monday, October 12, 2009, 8:40 am

Is there a minimum version of Excel needed to run this? I’ve got 97 but get a ‘Compile Error in Hidden Module: M_LOESS’ error when I try to run it.

Any suggestions would be appreciated as I need a program to smooth out experimental data.

Comment from Stephen Hughes
Time: Monday, October 12, 2009, 10:33 am

Hi Jon,

Anything you can manage would be appreciated! Meanwhile I’ll try (again) to persuade my boss of the benefits of having a modern computer and up-to-date software!

Comment from Omair Noor
Time: Thursday, December 17, 2009, 8:14 pm

Hi Jon,

I have approximately 2400 data points per column. I tried to use your LOESS utility function, but i get the following error:

Run-time error ’6′:

Overflow.

Any idea how should i be going about resolving this problem.

Thanking in anticipation.

Comment from tom
Time: Friday, January 29, 2010, 6:24 pm

I also experienced the runtime error.
Does it have to do with very small alpha values? I chose 20 points out of 2000.

bug #2:
The utility crashes from the menu if Excel is opened without any workbook.

bug #3:
Sometimes the dialog disappears when I am entering ranges in the text boxes.

idea:
Could you add something like a auto-fit for columns in the tool. I rather would like
to add full columns. The utility could start the range below the last text row and
would end the range before the first empty cell.

Comment from Nick
Time: Sunday, January 31, 2010, 8:22 pm

Hi Jon!
Nice little utility you have here! I’m utterly hopeless at math and I have a report involving normalizing microarray data, which requires me to apply LOESS to some data. I have trouble following your example of plotting LOESS for anomaly data for NASA. There are seven choices for entering the data and I don’t know which to choose! If you give a step by step instruction of how you used the add in, it would be great.
Please help the mathematically crippled biologist!

Comment from K Hayhoe
Time: Thursday, April 15, 2010, 12:50 pm

any chance of a Mac versions for this and all your other great add-ins, pleeease?

Comment from david
Time: Wednesday, June 9, 2010, 11:37 am

Hi John,

I’m also attempting to run your loess app on some microarray data. I’m not certain if it’s working properly or not. My data consists of ~385k values (I can break into subsets of 2 30k and 2 150k).

I’m using the “3 adjacent column” button. When I hit it, the task manager says Excel is not responding. I’ve experienced this before with large data sets (this one is a smaller one for me), analysis and Excel. So I walk away — after a 1/2 hour it’s still not responding — with either the 30k, 150k or the 380k.

Should I wait longer??

Thanks

Excel 2007
Windows XP, x64 SP2
Dell Precision T5500, 2.27ghz, 24g RAM

Comment from DD
Time: Wednesday, June 23, 2010, 1:27 pm

Can’t get this to work at all in Excel 2007.

Steps to reproduce:
1. Go to Add-Ins and select “PTS Charts => LOESS”
2. Choose the button in the lower right (four columns)
3. Select 270 values for X
4. Select 270 next to it for Y
5. Do the same for output (other columns)
6. Type “0.25″ in the alpha box (Number of points updates to 67)
7. Press OK

Result is X output empty and Y output containing the same value repeated 270 times.
I can pick any data, and this is always the result.

Comment from david
Time: Tuesday, June 29, 2010, 4:46 pm

Hi John,

Thanks for your excellent reply, and please accept my apology for my delayed response.

Yes, it’s many data points, but it’s actually a small data set for us (thus the use of Excel). I’ve some data sets that contain ~3 million data points, and yes a standard first pass is to do LOESS.

It would be nice to have a LOESS function in Excel — it’s more familiar and comfortable to some folks around here — rather than R. That’s why we were excited to come upon your solution. We do have some other ~20-30K datasets (and the above that I mentioned), but trusting Excel and XP to 30 minutes (or longer) of stable computing does seem to be living to close to edge for me!

Thanks for your excellent and informative blog.

David

Comment from DanF
Time: Saturday, July 10, 2010, 5:42 pm

I particularly like the remember last use feature.

However, after a half day of LOESSing all over the place, I can’t get the utility to work any longer. When I click the x input range, I get the normal selection box. When I try to put a y range in or click any of the input buttons or text boxes, it goes poof and the LOESS window closes. When I close Excel (thinking that a good old fashioned close it-open it sequence is the cure-all for just about anything), I get the password requests. I read your REF CTL commentary, but I didn’t see what I might have done to cause LOESS to go on strike.

Comment from Thomas Rand-Nash
Time: Thursday, July 22, 2010, 11:40 am

First and foremost, THANK YOU!! This is coming in incredibly handy for smoothing out an unruly data horde! My question has to do with an “PTS_LOESS Password” box which opened after I quit excel. Is there a password I should know?
Thanks

Comment from Nate
Time: Tuesday, August 3, 2010, 12:52 pm

Jon, I’m looking forward to trying this tool, but after installing it, I get the following error message when opening Excel 2007: “object library invalid or contains references to object definitions that could not be found”

Any ideas on what might cause that?

Comment from Nate
Time: Wednesday, August 4, 2010, 7:48 am

Jon, regarding my comment on the missing object library: I determined that this was being caused by a corrupted installation and not your add-in. I reinstalled E2007 and have it working now.

Comment from Ann D
Time: Wednesday, September 8, 2010, 1:53 pm

I’m trying to load the Loess add-in, but when I click on “Click this PTS LOESS Smoothing Utility link to download the utility” I just get a bunch of garbely gook. Is the link still active?

Thanks.
Ann

Comment from Ann D
Time: Friday, September 10, 2010, 12:40 pm

Thank you, John. Yes, that worked. Very cool utility and SO much friendlier than R.
Thank you for sharing it.

Ann

Comment from Lewis Mc
Time: Friday, November 5, 2010, 12:32 pm

I’ve been smoothing data by other tedious means for almost a year. Thank you for your help :)

Comment from Elizabeth Arias
Time: Monday, November 8, 2010, 1:43 pm

Just wanted to say thanks. I usually work in Stata, but am stuck working at home and needed to do some data smoothing. All I had access to was Excel…and there you were after a little searching. I downloaded the version for Excel 2007 and works “wicked awesome”!!!

Comment from DaleW
Time: Friday, November 19, 2010, 10:08 pm

Jon, great utility.

I would note that it still behaves oddly for me for really small N. For example, with that NIST data, try pushing it to N=3 resolution, and give it lots of resolution for output. (I know, it’s just *wrong* to use LOESS for linear interpolation.) But wait, why is it linearly interpolating, if jaggedly, when N=3? Linear interpolation implies N=2, right?

Comment from DaleW
Time: Saturday, November 20, 2010, 4:30 pm

Jon, I noticed your algorithm sometimes tweaks N. However, for my example it kept reporting back N=3 while clearly doing a sort of linear interpolation for N=2 points locally instead of LSQ with N=3 locally.

BTW, I like how Minitab handles excessively low alpha in its LOWESS implementation. It falls back to pure linear interpolation, meaning it uses the nearest point in each direction if at all possible (instead of true nearest two points as LOESS perhaps should, and your code actually does when reported N=3).

Comment from Rogan
Time: Sunday, January 16, 2011, 6:18 am

Hi Jon,

Nice program, thanks for making it available. But …

It worked fine for the first few times; then I stopped to do something else, and then when I came back, the box always disappears when I try to do anything on it. Not just about once every 10 or 12 times, but always. Can’t get it to work at all. Even just accepting the data there and clicking OK does nothing. I’ve read your pages about RefEdit and tried everything there, without success. Only thing remaining is to throw the computer out the window.

Note that I also get Loess twice on the menu, and can’t fix this – not that it really matters.

You mentioned that you might replace RefEdit with text boxes or input boxes: have you made any progress here?

Thanks,
Rogan.

Comment from Rogan
Time: Sunday, January 16, 2011, 3:22 pm

Hi Jon,

Thanks for your quick reply.

I had tried your suggestion, and did it again, just in case. Same result. (I should warn you that I am jinxed when it comes to electronics and computers. I don’t really believe in it myself, but, when I get close to any device, if anything can go wrong, it will. If a program can blow up, it will. I once wrote to Bill Gates about this (when I thought his software was at fault) and got a refund of my purchase price. But enough of that – it is my problem).

Sorry, I should have told you I am using Excel 2002. (I find 2007 excruciatingly slow; and 2010 just blows up on my machine, due to influences mentioned above). But I also tried all suggested remedies on Excel 2007, with the same result.

This is all a bit of a drag, because I cannot use your amazing interface; but I have taken your VBA code for the calculations, and got it working. Thanks.

I am now looking at allowing a 2nd order fit (as suggested by TV and others. Your response – some very nice pseudo-code plus the warning “You’ll have to figure out what to do with the weights” – was a good model. Just include the weights in each of the sums. EXCEPT: the algorithm assumes that the sum of the weights is 1; and when this is not so, it may change subtly).

Anyway, I’ll have it sussed within a day or two, and will post here the code to add to yours to allow the 2nd order as an option.

Rogan.

Comment from Rogan
Time: Tuesday, January 18, 2011, 2:04 am

Hi Jon,

I wanted to do the 2nd order by using the worksheet function Minverse to solve the three simultaneous equations. But Minverse requires a range input, and this must be a range on a worksheet, and you cannot change anything on a worksheet from a UDF. (If any of this is wrong, or there are any tricks to work around it, I would love to hear.) Of course, it can be done by a sub, and maybe your interface calls a sub.

But, for the moment, I had to resort to an explicit solution. The code follows:

1. Replace the function declaration with:
‘ New function declaration to allow for 2nd degree
Public Function LOESS(x As Variant, y As Variant, xDomain As Variant, nPts As Long, Degree As Integer) As Double()

2. Add the following variable declarations:
‘ Variables for 2nd degree
Dim SumWtX3 As Double, SumWtX4 As Double, SumWtX2Y As Double
Dim R1 As Double, R2 As Double, R3 As Double, R4 As Double, R5 As Double, R6 As Double, R7 As Double, R8 As Double

3. Put the following code just before the comment “do the sums of squares”:
‘ Test for linear (Degree=1) or 2nd degree
If Degree = 1 Then

4. Add the following code between and the statement yLoess(iPoint, 1) = … and the final Next:
‘ 2nd degree polynomial: Degree is anything but 1
Else

SumWts = 0
SumWtX = 0
SumWtX2 = 0
SumWtX3 = 0
SumWtX4 = 0
SumWtY = 0
SumWtXY = 0
SumWtX2Y = 0

For i = iMin To iMax
SumWts = SumWts + weight(i) ‘S0
SumWtX = SumWtX + x(i, 1) * weight(i) ‘S1
SumWtX2 = SumWtX2 + (x(i, 1) ^ 2) * weight(i) ‘S2
SumWtX3 = SumWtX3 + (x(i, 1) ^ 3) * weight(i) ‘S3
SumWtX4 = SumWtX4 + (x(i, 1) ^ 4) * weight(i) ‘S4
SumWtY = SumWtY + y(i, 1) * weight(i) ‘T0
SumWtXY = SumWtXY + x(i, 1) * y(i, 1) * weight(i) ‘T1
SumWtX2Y = SumWtX2Y + x(i, 1) ^ 2 * y(i, 1) * weight(i) ‘T2
Next

‘ Combine S & T variables
R1 = SumWtX2Y * SumWtX2 – SumWtY * SumWtX4 ‘T2S2-T0S4
R2 = SumWtX2 * SumWtX4 – SumWtX3 ^ 2 ‘S2S4-S3^2
R3 = SumWtX * SumWtX4 – SumWtX2 * SumWtX3 ‘S1S4-S2S3
R4 = SumWtX2Y * SumWtX3 – SumWtXY * SumWtX4 ‘T2S3-T1S4
R5 = SumWts * SumWtX4 – SumWtX2 ^ 2 ‘S0S4-S2^2
R6 = SumWtX2Y * SumWtX – SumWtY * SumWtX3 ‘T2S1-T0S3
R7 = SumWtX * SumWtX2 – SumWts * SumWtX3 ‘S1S2-S0S3
R8 = SumWts * SumWtX2Y – SumWtY * SumWtX2 ‘S0T2-T0S2

‘ Can now calculate directly
yLoess(iPoint, 1) = ((R3 * R6 + R4 * R7 – R2 * R8) * xNow ^ 2 + (R4 * R5 – R1 * R3) * xNow + R1 * R2 – R3 * R4) / (R3 ^ 2 – R2 * R5)

End If

The second degree fit is better at the ends (where the 1st degree tends to be linear) and is more faithful to the peaks and troughs, as you would expect. (By the same token, it will also be more sensitive to outliers).

Now I’m going to try and write a dll for the 3rd and higher degrees (despite the comment I saw somewhere that this is against the original spirit of Loess. It will be interesting to see the results). But that will be in my favourite language, Fortran.

Hope this is useful,

Rogan.

Comment from Blake
Time: Wednesday, February 16, 2011, 11:16 am

Jon,

I was curious if there is a way I can call this in VBA? I want to run a batch process on about 50 sets of data, and want to do the basic 3 Column: Shared X input and X output, Y input, Y output. I tried using your VBA code you posted, but it runs real, real slow for some reason, and isn’t giving the same results. Thanks!

Blake

Comment from andres
Time: Monday, March 21, 2011, 2:06 pm

hi Jon,

thanks so much for your work!

unfortunately, when I accessed the url where the download link is referred to (http://peltiertech.com/images/2009-10/PTS_LOESS.xla), but i get into a page with weird codes and symbols, and there is no obvious direction where to go to download the add-in

any suggestions where else can i access to the add-in from?

many thanks!

andres

Comment from andres
Time: Monday, March 21, 2011, 2:58 pm

thanks Jon!
andres

Comment from Dave Rents
Time: Monday, May 23, 2011, 4:48 pm

Jon, Thanks for this fantastic tool. Very easy to use. I was wondering if you have/or have plans to build in an interpolation function? I was hoping to use something of this nature for not only smoothing but future forecasting.

Thanks again
Dave

Comment from J Hey
Time: Friday, July 15, 2011, 10:53 am

Very useful utility.

However I am having a problem that seems similar to what a couple others found.

If I use the addin extensively in a particular sheet, and I save the file and then come back later, when I open the addin again it will have values in the form. If I click in any of the form windows, or click on a different model, the applet closes down. The only form window I can alter is for ‘shared Xinput and X output’. In effect there is no way to use the utility in that worksheet again. uninstalling the addin and reinstalling does not help. Only if I copy all the data to a new sheet, then can I use the applet on it.

thanks

J. Hey

Comment from Claire
Time: Thursday, September 22, 2011, 3:17 am

Hi Jon,
Does this work with Office 2010?
Thanks,
Claire

Comment from Claire
Time: Sunday, September 25, 2011, 2:20 am

Thanks Jon.
Looks like a great utility. I have it in my addin panel in the options window of excel but still nothing shows up in the add-ins pane of the ribbon (unlike other add-ins I have installed). I wondered if this was a compatibility issue but from your reply I guess not. I’ll keep trying.
Thanks,
Claire

Comment from Claire
Time: Sunday, September 25, 2011, 11:28 am

Thanks. Works fine on home computer – maybe a anti-virus problem or something at work.

Comment from K. A.
Time: Saturday, October 8, 2011, 3:39 am

Thank you Jon for this wonderful utility.
I am a new bee here and sorry for the dumb question.
My X and Y sets of data are as follows. For X =55 the corresponding value Y is unknown. Could someone kindly explain to me ,how I could predict the Y value for X= 55 and all corresponding Youtput values for each Xinput values using this utility? Which buttons to use is not clear to me due to my limited knowledge. Can I predict the Youtput value for X=55 this way? Thank you so much answering.
KA.

X Y
0 466
1 441
2 440
3 449
4 417
5 430
6 447
7 451
8 487
9 417
10 392
11 399
12 408
13 388
14 396
15 389
16 403
17 414
18 412
19 414
20 419
21 454
22 448
23 444
24 444
25 460
26 478
27 476
28 461
29 491
30 479
31 478
32 473
33 475
34 460
35 463
36 463
37 422
38 441
39 378
40 361
41 362
42 385
43 388
44 381
45 367
46 383
47 389
48 386
49 400
50 411
51 422
52 433
53 421
54 429
55 ?

Comment from K.A.
Time: Wednesday, October 12, 2011, 4:21 am

Thank you so much Jon. I will try to get my head around it :).

Comment from Mehdi
Time: Sunday, January 1, 2012, 4:28 pm

Hi Jon

Fantastic Job;

I was looking for something like this for ages and i got so excited when i saw your examples undertaken by LOESS, however, it didnt work for my data. Maybe i’m doing something wrong during introducing X,Y. I’ll be grateful if yo can look at my data.

How can i send you my file?

Comment from Bram
Time: Friday, January 20, 2012, 4:32 pm

Hi Jon,

Thank you for this very useful tool. I have used it succesfully to study time trends of various pollutants. However, for one pollutant I always get the error Runtime error 13: Type mismatch. I could not identify any difference with the other datasets that ran fine. Any thoughts?

Here’s the data:
2/13/2001 140
2/26/2001 129
3/5/2001 80
11/13/2001 23
3/8/2002 3
11/9/2002 138
12/18/2002 24
2/12/2003 110
2/3/2004 169
2/19/2004 12.2
2/27/2004 232
10/19/2004 28.1
10/28/2004 39
12/6/2004 4.89
1/10/2005 83.8
10/19/2005 4.14
11/10/2005 13.8
2/21/2006 23.8
3/1/2006 26.2
12/11/2006 7.4
2/23/2007 5.8
4/21/2007 2.9
9/24/2007 5
12/19/2007 5.7
1/24/2008 14.6
11/26/2008 26.3
12/16/2008 11.3
2/7/2009 2.6
3/5/2009 2.8
10/14/2009 21
12/8/2009 20
2/6/2010 24
10/7/2010 20
10/31/2010 47
2/17/2011 7.7

Comment from DaleW
Time: Friday, January 20, 2012, 9:36 pm

Bram – Your data ran fine for me from the Text Import Wizard. The early data has so much scatter that you might consider alpha=0.5, if you can get past the runtime error.

Comment from Bram
Time: Monday, January 23, 2012, 11:52 am

Dale,
Thanks for your response. It seems like its working now.
Bram

Comment from Jason
Time: Wednesday, March 7, 2012, 6:06 pm

Hi Jon,

Let me just say thanks for putting together a great tool. It’s perfect for analyzing my data and bringing datasets to a common scale for comparison. However, after some use of the add-in it’s started to exhibit some instability. It launches fine and I can click in the “X Input” box, but if I click anywhere else on the interface it closes immediately. I’m currently on Excel 2010, but had a similar issue on 2003.

Have you ever seen that before or have any recommendations for a fix?

Thanks,
Jason

Comment from Jason
Time: Wednesday, March 7, 2012, 6:31 pm

Sorry for the redundant post… I just read through the comments and it seems that I’m not the only one experiencing the self closing fluke. I’ll use J Hey’s work around and copy and paste data from one sheet to the other for the time being.

Thanks again for putting the time into this utility!

Comment from Shirley
Time: Tuesday, March 27, 2012, 4:46 am

Thanks for your instructions on dowloading the software, and again for you work on this dissertation saver.

I tried to use the loess utility and received a message similar to one of your other users: runtime error ’6′ overflow. The solution to this problem wasn’t on this listserv, can you please help me to figure out reasons for getting this error message.

Thanking you in advance,

Shirley