LOESS Utility – What the Buttons Mean

Last fall in LOESS Utility – Awesome Update I introduced the latest version of my LOESS Utility for Excel. Compared to the earlier version, this one provides the user with much more flexibility in data range selection. As with all of my utilities, I made these adjustments after using the utility for my own real analysis. I don’t know of too many companies where the programmers are also users of the same software.

Lately I’ve received a few comments and emails asking how to use the utility’s dialog. I thought it was self-explanatory, but then, as the designer I already knew how it worked. This article is intended to answer these questions.

The LOESS Utility Dialog

The dialog has seven icon-buttons, and depending on which of these has been selected, one to four range selection boxes appear in the large empty space to the right of the buttons. The icons represent the data arrangement: Blue = X Input, Red = Y Input, Green = X Output, Orange = Y Output. The labels above the icons and the labels adjacent to the range selection boxes are intended to guide the user.

The X Input and Y Input ranges must have the same number of rows, and the X Output and Y Output ranges also must have the same number of rows, but not necessarily the same number as the input ranges.Mismatched numbers of rows will result in an error, as will blank cells and non-numeric cells within the selected ranges (except that the contents of the Y Output range are ignored and overwritten).

If any two of the ranges selected in the range boxes are not contiguous, they need not begin on the same row.

One handy feature I built into this version of the utility is autoselection of the number of rows in a range. If you select the first data cell of a column, the utility determines how many rows are filled with data, and uses that range in its calculation. If you select more than one row, the program does no autoselection, but instead assumes you selected the rows you needed, and it calculates based on your selection. The autoselection means the user doesn’t have to count the number of rows of the ranges.

LOESS Utility Button 1

The first button needs only one range selection box, because the data all fits into a contiguous range. The first column of the range contains the X values (used for both X Input and X Output), the second column contains the Y Input data, and the Y Output data will be placed into the third column, overwriting any existing data in the column.

LOESS Utility Button 2

The second button needs two range selection boxes. The first column of the first range contains the X values (used for both X Input and X Output), the second column contains the Y Input data. The Y Output data will be placed into the only column of the second range, overwriting any existing data in the column. The Y Output range does not have to start on the same row as the first range. All ranges must have the same number of rows.

LOESS Utility Button 3

The third button needs three range selection boxes, each indicating a single column. The first range contains the X values (used for both X Input and X Output), the second rangecontains the Y Input data, and the Y Output data will be placed into the third range, overwriting any existing data in the column. The three ranges do not have to start on the same row. All ranges must have the same number of rows.

LOESS Utility Button 4

The fourth button needs two range selection boxes. The first range contains the X Input and Y Input data in the first and second columns of the range. The X output data is in the first column of the second range, and the Y Output data will be placed into the second column, overwriting any existing data in the column. The two ranges do not need to start on the same row or contain the same number of rows.

LOESS Utility Button 5

The fifth button needs three range selection boxes. The first range contains the X Input and Y Input data in the first and second columns of the range. The X output data is in the second range, and the Y Output data will be placed into the third range, overwriting any existing data in the column. The three ranges do not need to start on the same row. The second and third ranges must have the same number of rows, but not necessarily the same number of rows as the first range.

LOESS Utility Button 6

The sixth button needs three range selection boxes. The first range contains the X values, the second range contains the Y Input data, the first column of the third range contains the X Output values, and the Y Output data will be placed into the second column of the third range, overwriting any existing data in the column. The three ranges do not have to start on the same row. The first and second ranges must have the same number of rows, but not necessarily the same number of rows as the third range.

LOESS Utility Button 7

The seventh button needs all four range selection boxes. The first range contains the X Input values, the second range contains the Y Input data, the third range contains the X output values, and the Y Output data will be placed into the fourth range, overwriting any existing data in the column. None of the ranges have to start on the same row. The first and second ranges must have the same number of rows, and the third and fourth ranges must have the same number of rows, but not necessarily the same number of rows as the first and second ranges.

Smoothing Parameter and Number of Points

The user can adjust the smoothness of the calculated Y Output by changing either the smoothing parameter alpha (the fraction of points used in the moving regression) or the number of points used in the moving regression. When one of these is changed, the other is updated, based on the number of points (rows) in the X Input range.

I think that values of 0.25 to 0.50 are typical for alpha, but it seems to me that if you have some logical number of points built into the data you should use that number. For example, if you have a few years of daily data, you might choose 90 points (3 months or a calendar quarter) for your analysis.

Rerunning an Analysis

When you perform a LOESS calculation with this utility, the values in the dialog are saved, and re-entered into the dialog the next time it is called on the same worksheet. Saved settings from one worksheet do not affect settings from another worksheet. If you use the output in a monthly report, this month’s settings are saved. Next month when you run the utility on the updated worksheet, your previous settings will be used as defaults, saving you a lot of time.

If you select only the first data cell of a range, the autoselection feature will select the right number of rows this month, and again next month even if data has been added or removed.

Related Posts:

• LOESS Utility – Awesome Update
• LOESS Utility for Excel
• LOESS Smoothing in Excel
• Deming Regression Utility
• Chart Source Data Highlighting
• Adding Excel Chart Data