Excel Charts and Missing Data

This tutorial shows you how to handle missing data in Excel Charts. It is based on examples found in Chart_Missing_Data.xls.

1. Working with Missing Data

Many times we need to work with data sets that have some missing data. Lets look at a typical situation where a plant tracks flow and quality data. Our data looks like the table on the right.

Notice the missing quality data on weekends. In this example, quality data is collected 5 days a week, while flow data is collected 7 days per week. This means that every week, we have two empty quality data cells.

How do we handle charting the empty cells caused by missing data?

2. Excel's Missing Data Chart Options

User's can tell Excel how to plot empty cells in charts with the Tools > Options > Charts menu. Excel offers three choices:

 O Empty Cells Not Plotted (leave gaps)

 O Empty Cells Plotted as Zero

 O Empty Cells Interpolated

The three charts below show how the plot empty cell choice affects a chart with  one months laboratory data.

• Empty cells as Zeros is unattractive and misleading. Empty cells are not necessarily zero.
• Empty cells  Interpolated looks better than the zero choice, however, a trend line without the markers could be misleading because it would be difficult to know which values were interpolated without direct examination of the data.
• Empty cells Not Plotted is the most informative because it shows exactly when observations were made without the zero effect or interpolation effect

Working with missing data doesn't look to bad so far, Excel lets us make a reasonable choice on how we want to handle  plotting empty cells.

3. Calculated Values and Empty Cells

 What about working with cells that have calculated values based on empty cells?

Let's take another look at our example. Column E shows the calculated value of  flow * concentration *  factor to give us pounds/day.  Notice what happens on the weekend days, cell E2  is 0, our formula gives a misleading/invalid  result on days when with missing data, days when no quality test was taken.

Zero's are not acceptable for calculated values when we have missing data. A zero is not the same as an empty cell. Think what the 0's would do to average, minimum calculations. We need a workaround.

4. If Formula with " " Workaround

Users often use an If formula to work around missing data;  the formula is only calculated when there is data in the two or more cells involved in the calculation. An If formula like the one below can help 

                             F2  = If(D2<>"",C2*D2*8.34,"")

If the D column cell is blank (=""), then the value in the F2 cell will look blank.

The If Formula with " " workaround has solved part of the problem, it looks empty and the totals and averages are now correct. However, Excel still has a charting problem. Since cell F2 has a formula, it is not empty,  so it must be plotted. Since cell F2 has no value,  Excel plots F2 and the other Column F weekend cells as 0. We need another workaround to solve the calculated empty cell chart problem.

 5. If Formula with #N/A Workaround

Users who do not want their charts to drop to zeros when plotting results of empty cell calculations, often use the #N/A adjustment to the If statement.

                       G2  = If(D2 <>"",C2*D2*8.34,NA())

Good news - this technique solves the chart problem. Excel recognizes #N/A's and ignores them when charting.

There is some bad news with the #N/A workaround, it disrupts  Excel's regular  sum, average, max, and min functions.

This is solvable with several formula techniques or a little VBA. See my future post on the #N/A workarounds.