## Recipe 6.4. Smoothing Data Using Weighted Averages## ProblemYou'd like to smooth data in a time series. ## SolutionUse Excel's exponential smoothing feature in the Analysis ToolPak or construct a smoothed data series yourself using spreadsheet functions and VBA. ## DiscussionSmoothing data is often desirable to remove unwanted noise in a data series. Computing moving averages as discussed in Recipe 6.3 is actually a smoothing process. Besides the moving averages methods discussed earlier, there are other ways to smooth data. Excel offers an exponential smoothing feature as part of the Analysis ToolPak. Further, you can construct any smoothing operations you desire using standard spreadsheet functions (and/or VBA). You already saw this approach in the previous recipe; however, this time I'll show you how to use a weighted moving average that makes use of a cubic spline interpolation kernel. ## Exponential smoothingExponential smoothing is also a weighted-averaging technique. The idea behind weighted averaging is to give data values closest to the value being forecast or estimated greater importance, or influence, as opposed to values further away. Exponential smoothing uses the following formula: F To use exponential smoothing, select Tools images/U2192.jpg border=0> Data Analysis... from the main menu bar to open the Data Analysis dialog box (shown earlier in Figure 6-7). Select Exponential Smoothing from the list of available analyses. Doing so opens the Exponential Smoothing dialog box shown in Figure 6-11. ## Figure 6-11. Exponential Smoothing dialog boxIn the Input Range field, type (or select on the spreadsheet) the cell range containing the input data you wish to smooth. Enter a damping factor in the "Damping factor" field or leave it blank to use the default value of 0.3. Enter a reference to the topmost cell in the desired output cell range in the Output Range field. Press OK when you're done and you should see the results on your spreadsheet. Figure 6-12 shows an example for the same annual temperature data used in the previous recipe. The resulting data series after smoothing is contained in column R. ## Figure 6-12. Exponential smoothing resultsJust like the Moving Average tool discussed earlier, the Exponential Smoothing tool places a cell formula rather than a value in each cell of the resulting data series. This way, if your original data changes, the smoothed data will update automatically. Cell R10 is selected in Figure 6-12 so you can see what the formulas look like. The formula is shown in the formula bar and is of the form Figure 6-13 shows a plot of the smoothed data series superimposed over the original series so you can see the difference between the two. ## Figure 6-13. Exponentially smoothed temperature dataOther weighted averaging techniques are commonly used in time series analysis. In the next section I'll show you one based on a cubic spline interpolation kernel. ## Kernel smoothingIn this example, I'll show you a weighted averaging technique that uses a cubic spline interpolation kernel designed to approximate a Gaussian kernel. You can use all sorts of different kernels for smoothing with only slight modifications to the technique I'll present. The kernel I'll use is: r represents the distance between data points and h represents the effective smoothing radius divided by 2. I'll use this smoothing function to compute weights when computing weighted averages of the time series. To make this task easier, I added a VBA function called ## Example 6-1. Cubic spline kernel
This is a fairly straightforward implementation of the equation I showed earlier, so I won't go over each line of code. Refer to Chapter 2 if you're not already up to speed on using VBA and adding custom functions and subroutines. I'll use this function to generate weights in order to smooth the same temperature data discussed earlier. Figure 6-14 shows a weight table I computed using this function. ## Figure 6-14. Weight tableThe first column in the table, labeled Now, to compute the smoothed data series, I set up another column in my spreadsheet, as shown in Figure 6-15. ## Figure 6-15. Kernel-smoothing resultsThe smoothed data is contained in column S under the heading The second cell range contains the weights shown in Figure 6-14. Further, dividing by the sum of weights, contained in cell V14, normalizes the result. Once the first formula was set up, I simply copied and pasted it into the remaining cells in the data series. Notice that we do lose some data. Specifically, we lose the first and last three data items in the range because of the range of influence of the weighting function I used. You can increase the range of influence by increasing the value for the Figure 6-16 shows a plot of the smoothed data series compared to the original data series; the effect of smoothing is clearly evident. ## Figure 6-16. Kernel-smoothed temperature data## See AlsoSee Recipe 6.3 to learn how to compute non-weighted moving averages in Excel. Smoothing data using the techniques in this and the previous recipe is actually a form of filtering. You can also filter data in the frequency domain in Excel using Fourier transforms; read Recipe 6.10 to learn more. |

# Recipe6.4.Smoothing Data Using Weighted Averages

Similar products