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 be in [...]

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 fully populated [...]

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 pivot [...]

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 [...]

Ever have a situation where you have spent time getting your chart or charts to look nice, then try to use different data ranges in these charts? You’ve discovered that there’s no easy way to approach this change. At least there’s no easy way built into Excel, but there is a utility that can make [...]

In Graphical Approach to a Simple Physics Problem and Regression Approach to a Simple Physics Problem I have discussed approaches to solving my daughter’s physics homework. Basically, the problem started with this data, showing the time to drain water through a hole in a bucket, based on the diameter of the hole and the initial [...]

In Graphical Approach to a Simple Physics Problem and Regression Approach to a Simple Physics Problem I have discussed approaches to solving my daughter’s physics homework. Basically, the problem started with the following data, showing the time to drain water through a hole in a bucket, based on the diameter of the hole and the [...]

In Graphical Approach to a Simple Physics Problem I discussed my daughter’s physics homework. The problem was to determine the relationships between how long it takes water to drain from a hole in the bottom of a bucket, the diameter of this hole, and the initial height of water in the bucket. Given this relationship, [...]