Archive for 'Data Techniques'
Deming Regression
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Monday, October 5th, 2009 under Data Techniques.
Comments: 8
Mind the Gap – Charting Empty Cells
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Thursday, July 30th, 2009 under Data Techniques.
Comments: 1
AutoFilter Tricks
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Friday, May 22nd, 2009 under Data Techniques.
Comments: 14
Calculating Wind Direction per DDoE
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Wednesday, April 29th, 2009 under Data Techniques.
Comments: 2
Prepare Your Data
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Wednesday, March 18th, 2009 under Data Techniques.
Comments: 6
LOESS Smoothing in Excel
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Monday, March 9th, 2009 under Data Techniques.
Comments: 24
How to Edit Series Formulas
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Wednesday, December 17th, 2008 under Data Techniques.
Comments: 36
SOLVER – Optimization Approach to a Simple Physics Problem
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Thursday, September 11th, 2008 under Data Techniques.
Comments: none
Goal Seek – Optimization Approach to a Simple Physics Problem
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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 [...]
Posted: Thursday, September 11th, 2008 under Data Techniques.
Comments: 8
Regression Approach to a Simple Physics Problem
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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, [...]
Posted: Wednesday, September 10th, 2008 under Data Techniques.
Comments: none














