How do I create a graph in Excel and get it to ignore blanks or zeros?

Lets assume you want to create a graph that plots a line from a data series, which contains zeros or blanks. These blanks or zeros are as a result of some calculation that has been performed to obtain the data in the first instance.

The charting tool takes into account anything that is either a number (zero) or a nonnumber (i.e. a blank). However it does ignore Excel generated error messages, such as #N/A.

So, lets assume, you are getting your data from cells A1 through A5 in worksheet Sheet2. Your chart data resides in cells A1 through A5 in worksheet Sheet1.

The formula you use in cell A1 in Sheet1, to ensure you get the #N/A, so that the charting tool ignores it as a value (zero or blank) is:

=IF(Sheet2!A1=0,NA(),Sheet2!A1)

The NA() function places a #N/A into the cell. This method of clearing a non value or a zero in the cell so that the charting tool ignores it is useful especially if you are trying to superimpose a trendline.

One small caviat though. Any column that has an error value such as #N/A will not be processed if you try and apply a numberic function to it, such as SUM, or AVERAGE. The result of this will be #N/A.

However, one of the options you have to get around this is to check for every instance of #N/A, disregard it and SUM (or AVERAGE or whatever) the rest of the entries.

This can only be achieved if the formula is constructed as an array type. For example, if in the above example, you have numbers and #N/A's in cells A1 through A5. The formula would be:

{=SUM(IF(ISNA($A$1:$A$5),0,$A$1:$A$5))}

Here, the ISNA is used to test cells to see if the entry equals #N/A and if it does, to ignore it in the SUM function.

Note the curly {} brackets which formats the formula as an array. You create an array formula by creating the formula and then hitting CTRL-SHIFT-ENTER.

You could of course use the ISNUMBER option too, where the formula construct would look like:

{=SUM(IF(ISNUMBER($A$1:$A$5),$A$1:$A$5))}

or if you wanted and AVERAGE:

{=AVERAGE(IF(ISNUMBER($A$1:$A$5),$A$1:$A$5))}