Data Techniques

Deming Regression

Monday, October 5, 2009 by Jon Peltier 30 Comments

Monday, October 5, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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 the measurement of the Y variable, the dependent variable.

This assumption is reasonable enough. But there are cases where errors in the values of X cannot be discounted. For example, X and Y may be comparisons of the output of two different machines for given inputs. When the outputs are compared, both machines are subject to errors in measurement, so the usual assumptions are invalid.

Deming Regression Example

Suppose Machines A and B had the following output. At each nominal output value, the results of two runs of each machine are tabulated. There is some variability in measurements of each machine’s output. The averages of each machine at each nominal output level are shown next to the individual measurements.

We may be tempted to plot each machine’s outputs as Y values against the nominal X values.

We may even compute the lines of best fit for the two machines. We can see that they are close, but not exactly the same. The problem with this analysis is that we are not directly regressing one machine against the other.

In the Deming Regression, outputs of the two machines are plotted against each other. Pairs of actual outputs are measured for each nominal output, and the differences between the two measurements from each machine will be used to estimate the variation of each machine’s outputs. The averages of each pair form the basis of the regression, corrected for the machine variation.

The table below shows three sets of calculations. The first row shows a standard linear regression of the measured data. The second row shows a standard linear regression performed on the averages of each pair of values. The third shows the Deming regression analysis. Details of the Deming regression calculations are not given here, but a motivated reader can check the links in the references at the end of this article.

The data are not too different, and in fact the correlation in the LR of the average values and of the Deming regression are identical. In the plot below, lines have been added to show the three sets of results in the table above, as well as the 45 degree line, where Machine B = Machine A. The lines are practically on top of each other.

In the example above, there is little difference in the output of the various LR models. However, if Machines A and B exhibit greater variation in their output, there can be noticeable differences.

Deming Regression Utility

I’ve created a small utility that facilitates Deming Regression analysis in Excel. Read about it and follow the download link in Deming Regression Utility.

Regression and Trendline Articles in this Blog

References

Incorrect Least-Squares Regression Coefficients in Method-Comparison Analysis by P. Joanne Cornbleet and Nathan Gochman, Clin. Chem. 25/3, 432-438 (1979).

Posted: Monday, October 5th, 2009 under Data Techniques.
Tags: Regression, Statistics.
Comments: 30

Microsoft MVP Logo

Mind the Gap – Charting Empty Cells

Thursday, July 30, 2009 by Jon Peltier 101 Comments

Thursday, July 30, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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.

Note

A new feature in Office 365 (and Excel 2019), Show #N/A As An Empty Cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.

Read about it in Plot Blank Cells and #N/A in Excel Charts.

This new behavior in Excel makes this article obsolete.

Plotting Without Empty Cells

Here are some typical Excel charts, to remind us what they look like with fully populated data ranges. I’ve placed data labels on the charts to help show their behavior when empty cells (and other non-numeric fillers) are included in the source data.

Plotting With Empty Cells

The default behavior for most charts is to treat an empty cell by leaving a gap on the chart. Here are XY and Line charts that show how this looks. Note the lack of data labels where the gaps occur.

XY and Line Charts, With Empty Cells Treated as Gaps

You can instead have Excel treat blanks as zeros (which in general is deceiving, since empty cells mean the absence of a value, not a value of zero). Note the data labels indicating a value of zero.

XY and Line Charts, With Empty Cells Treated as Zeros

You can also tell Excel to interpolate over the missing data point. No data point means no data label.

Purists may say that the interpolation option may cause readers to think there is data along the line that spans the gap. Maybe so, but if the line or XY chart plots actual data points with markers, this is less of a risk.

XY and Line Charts, With a Line Interpolated Across Empty Cells

Column charts offer only the gap and zero options for empty cells. Interpolating makes no sense in a column chart, which has discrete bars for each actual data value. The chart with a gap has no data label at the gap, the chart with zeros has a zero data label.

Column Charts, With Empty Cells Treated as Gaps and as Zeros

If we change the vertical axis so that the horizontal axis crosses below zero, we see that the first chart has no data point (bar) corresponding to the gap, but the second has a point (bar) with a value of zero.

Area charts seem to offer both the zero and interpolation options, but in both cases, the chart plunges to zero without a corresponding data label. If we mess with the vertical axis, the chart still plunges exactly to zero in both cases.

Area Charts, With Empty Cells Treated as Zeros, or Supposedly Interpolated

A more suitable appearance for an area chart would be one that leaves a real gap, with vertical edges, as below. To get this I had to make a two-axis chart, with a hidden series on the primary axis to provide the A-B-C category axis labels, and an area chart on the secondary axis, with two points at the second category (Y=4 and zero) and two at the fourth category (Y=zero and 8). A date scale on the secondary category axis aligns these points above each other to produce the vertical-sided gap. This only took me three minutes to construct, but an average user might never quite get it.

Area Charts, With Empty Cells Treated as Gaps, if We Had Our Way

The protocol to produce an area chart with a gap that has vertical sides is given in Area Chart With Gap » Peltier Tech Blog

Pie charts don’t seem to offer any options for dealing with blanks, but the grayed-out default is the gap option. In the chart at the right, there is no point (wedge) and no corresponding data label where the empty cell would be plotted.

Pie Charts, Without and With Empty Cells

Apparently all charts except the Area chart provide a non-plotted point for an empty cell in the data range, though we can spoil that by selecting the option to treat a gap as a zero value.

Simulating Empty Cells

The main confusion comes along when someone uses formulas to fill the source data range.

The problem arises because a formula that links to an empty cell

=A1

doesn’t display a blank, it displays the value zero.

Unfortunately Excel has no formulaic way to simulate a blank in a cell. There is no formulaic way, then, to get an actual gap in a chart. A function like BLANK() or NULL() would be nice, and we’ll keep asking. But we won’t hold our breath. And if we really need it, we can hack something in VBA.

Andy Pope shares a workaround in Broken Lines for formula linked data, but it requires one or more dummy series with a line color matching the chart background, which obscure the existing line where the gaps should go.

What people try to do to fake a blank is return “” in the formula.

=IF(A1=””,””,A1)

The result looks to you and me like a blank, but to Excel, “” is a text string, albeit a short one, and it is therefore assigned a value of zero. These charts use “” in a logical yet misguided attempt to simulate an empty cell. All charts plunge to zero, and all display a data label showing the value zero.

XY and Line Charts, Empty Cells Simulated by Zero-Length Text

Column and Area Charts, Empty Cells Simulated by Zero-Length Text

People have learned that the #N/A error in a worksheet cell is often not plotted in a chart. In a formula, the function NA() returns this error.

=IF(A1=””,NA(),A1)

What #N/A does is prevent the rendering of a marker in XY and line charts: notice the lack of markers and data labels. It’s not a gap, but it’s pretty much the next best thing.

XY and Line Charts, Empty Cells Simulated by #N/A Error

This doesn’t work as well in other chart types. The column and area charts place a data label showing #N/A at zero. For these chart types it would be better to use “” or even zero, and apply a custom number format that suppresses the display of zeros (a format like “0;-0;;”).

Column and Area Charts, Empty Cells Simulated by #N/A Error

A pie chart will behave like a column or area chart: there is no visible point (wedge) but there is a data label showing zero or #N/A where the wedge would be located.

Pie Charts, Empty Cells Simulated by Zero-Length Text and by #N/A Error

Summary

The least deceptive way to display an empty cell in a chart, is to treat it as a blank. In line or XY charts, it is generally acceptable to interpolate across the blank with a line, if there are markers plotted for every actual data value. In the vast majority of cases, it is not appropriate to treat empty cells as zeros.

To simulate an empty cell in a line or XY chart, use NA() in your formula to produce the #N/A error in the cell. This produces an ugly #N/A error in the cell, but this error suppresses plotting of a point, and the line spans the position corresponding to the error’s slot in the data range. It’s not as good as a gap, but it’s perhaps the next best thing.

In an area or pie chart, the best approach is to use the null string “”, in conjunction with a number format that suppresses the display of zero values.

For an area chart you have to stand on your head to create a complicated chart that simulates a vertical edged gap and provides appropriate category axis labels.

Alternatively you could rely on VBA to clear cells which need to be cleared, but this routine would have to run every time the data range is changed, and it would have to reintroduce formulas into previously cleared cells as well as clear cells where blanks are needed. Or it could do all of the calculations in VBA, eliminating the worksheet formulas.

Update

Microsoft recently launched the Excel UserVoice channel, and I proposed Give us a proper NULL() worksheet function. The suggestion has received 164 votes (as of 9 January 2016), I’ve had discussions with Microsoft Excel Product Group people so they understand how I would use this function, ind it is now officially listed as “Planned”. So Microsoft is listening, and hopefully soon we’ll get a function that will help with this in an upcoming update of Excel 2016.

Update 2

A new feature in Office 365 (and Excel 2019), Show #N/A As An Empty Cell, solves the pain and frustration experienced by generations of Excel users trying to avoid plotting what look like apparently blank cells.

Read about it in Plot Blank Cells and #N/A in Excel Charts.

Posted: Thursday, July 30th, 2009 under Data Techniques.
Tags: Blank Cells.
Comments: 101

AutoFilter Tricks

Friday, May 22, 2009 by Jon Peltier 19 Comments

Friday, May 22, 2009 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.

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 table, you find the menu command disabled.

But I learned a trick this week at the Excel Dashboard and Visualization Bootcamp. If you select a cell which borders on the pivot table, but is not part of the pivot table,…

…the AutoFilter menu items are now enabled.

Select AutoFilter, and the Pivot Table has been AutoFilter enabled.

These conferences are great.

AutoFilter by Selection

Access has a feature that lets you quickly apply a filter based on the selected item. Wouldn’t it be great to have this in Excel?

Well, this feature exists, and it’s built in. But it’s not present on the default menus or ribbons. You have to dig it out and install it yourself.

Excel 2003 and earlier

On Excel’s Tools menu, select Customize, click on the Commands tab, select the Data item in the Caegories list, click on the AutoFilter item, and drag it to a convenient place on a toolbar or menu.

I’ve put this just under the “regular” AutoFilter item on my Data menu/Filter submenu.

Excel 2007

Click on the down arrow to the right of the Quick Access Toolbar, and select More Commands from the popup menu.

In the oversized dialog that appears, select Commands not in the Ribbon from the right hand dropdown, select AutoFilter, click Add, and press OK.

The AutoFilter icon appears on the QAT.

What the “New” AutoFilter does

This Autofilter command shares a name with the “other” AutoFilter command, but it has a unique icon, and its functionality is enhanced. The command creates an AutoFilter using the range that includes the active cell, then it filters this range based on the active cell.

Posted: Friday, May 22nd, 2009 under Data Techniques.
Tags: AutoFilter.
Comments: 19

Microsoft MVP Logo

Calculating Wind Direction per DDoE

Wednesday, April 29, 2009 by Jon Peltier 2 Comments

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 altitude of 3000 ft. Interpolation between the wind directions at 2000 and 5000 feet may be adequate, but let’s play along and use Dick’s best fit solution.

One of the answers to the generic question “Why do we need charts?” is to gain insight into a problem before we try to solve it.

Dick showed a couple of charts, lame little pie charts actually, which hand-waved a clever little trick he used to account for directions on either side of 360°. I’ll show how using more carefully crafted charts could have helped lead to the same result more quickly, by showing exactly what the relationships are. I will also show how such charts can easily show when a simplified approach is too simple, and leads to a very wrong answer.

When charted, the original data looks like the following.

By inspection, we can see that Directions 4 and 5 are pretty nearly straight lines as drawn, while the other three have points on either side of 360°. To turn these into linear fits, we need to add or subtract 360° so that the lines can cross 360° (or 0°).

Here is the data, corrected by adding 360° as appropriate. The calculated wind directions at 3000 ft are shown in blue.

Which leads to this chart. All five cases now follow nearly straight lines.

Dick’s Approach

The trick is knowing which data needs to be “enhanced”.Obviously you need to correct the ones which have values concentrated at the ends of the 0 to 360° range.

Dick’s approach was to examine the three directions in each case, and if the difference between the min and the max exceeded 180°, he added 180° and moduloed by 360°. This took data which was arranged around 360° and rearranged it around 180°. To these cases he then added 180° to the calculated result, and again moduloed by 360°.

Dick’s magic formula is

=IF(MAX(C4:C6)-MIN(C4:C6)>180,
    MOD(FORECAST($B7,MOD(C4:C6+180,360),$B4:$B6)+180,360),
    FORECAST($B7,C4:C6,$B4:$B6))

which is array-entered (by holding Ctrl+Shift while clicking Enter), so in the formula bar it is surrounded by curly brackets. B4:B6 are the altitudes 2000, 5000, and 10000 in the first column, B7 is the target altitude 3000, C4:C6 are the wind directions under the label Direction1, and this formula is entered in C7. As written, this formula can be copied and pasted into D7:G7 to provide all five calculations.

Instead of a difference of 180°, Dick could have used another cutoff value to decide which data needs correction. Alternatively, a check of the correlation or residuals of the three points could indicate whether the points are nearly aligned or far from linear. These latter approaches are more computationally intensive.

Dick’s calculated results for 3000 ft are shown by the squares in the chart above. They are all pretty close to the line segments connecting the 2000 ft and 5000 ft data.

Alternative Approach, Simple but Wrong

A comment by “T” suggested using some trig to convert all angles to fit into the ragne -180 to 180°, and computing the resulting fit. This seems like an easier approach, and if you only check the answers to the first three cases, they agree.

However, converting all angles to the arbitrary range -180 to 180° doesn’t check to see which angles should be changed. Dick’s fourth case has wind directiosn {190, 170, 200}, which become {-170, 170, -160}. Oops, now the three directions, which differed by at most 30°, now differ by 340°.

The incorrected directions are shown in the table and chart below.

Four of the five cases work out fine, but the fourth is obviously way off. Of course, without a simple chart, it’s not so obvious. The calculated values are shown as squares at 3000 ft. The black squares match perfectly with Dick’s solution, but the red square for the fourth data set is way off. If this solution had incorporated a check to see which sets of dta should be converted and which should be left alone, it may have made correct calculations for all cases.

Posted: Wednesday, April 29th, 2009 under Data Techniques.
Tags: .
Comments: 2

Microsoft MVP Logo

Prepare Your Data

Wednesday, March 18, 2009 by Jon Peltier 6 Comments

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 on. Pay me now or pay me later.

There are a number of aspects involved in preparing your data, and I’ll cover some of them here. I will concentrate on best practices for chart source data, but the principles also apply for other purposes. Factors to keep in mind are the layout of the data, both in terms of blank rows and columns in the data and whether the data is oriented by row or by column. Aspects of chart data include knowing which ranges of data are used for X and Y values and for series names, and how to get Excel to use the right data for the right parts of the chart. Finally, should you format your chart data so it looks nice in that monthly report, or should you splurge and use multiple data ranges? [Read more…] about Prepare Your Data

Posted: Wednesday, March 18th, 2009 under Data Techniques.
Tags: Chart Data, contiguous data, discontiguous data.
Comments: 6

Microsoft MVP Logo