
Excel Charts and Missing DataThis 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.
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.
