Linear Regression People are familiar with a standard linear regression (LR) method that makes assumptions about the X and Y variables in the analysis. Excel’s built-in regression methods make these same assumptions. The X variable, also called the independent variable, is assumed to be known precisely, and all error in the regression is assumed to [...]

Excel offers a few ways to deal with empty cells in a chart’s source data range. This is the cause of much confusion, especially over the definition of “empty cells”. Let’s take a look at this problem. Plotting Without Empty Cells Here are some typical Excel charts, to remind us what they look like with [...]

Apply an AutoFilter to an Excel Pivot Table Excel Pivot Tables are a very powerful feature, and the AutoFilter is also very useful. The row fields of the Pivot Table can be manipulated like an AutoFilter, but the data fields cannot be sorted or filtered. And if you try to apply an AutoFilter to a [...]

In Calculating Wind Direction, Dick Kusleika helped solve the problem of calculating a wind direction using a best fit trendline, given wind directions that may vary on either side of 360°. Dick’s original data has wind directions in degrees at various altitudes in feet. The requirement is to estimate the wind direction at the intermediate [...]

I was reading Chandoo‘s post Us vs. Them – Compare Sales Performance using Charts & Form Controls, and the first step in his protocol was “Prepare your data”. I’m always telling people to prepare their data first. I tell people to spend five minutes on their data, and save themselves five hours of frustration later [...]

In 1979 William Cleveland published the LOESS (or LOWESS) technique for smoothing data, and in 1988 he and Susan J. Devlin published a refined version of the technique (references are given at the end of this article). For each X value where a Y value is to be calculated, the LOESS technique performs a regression [...]