INTELLIGENT WORK FORUMS FOR COMPUTER PROFESSIONALS
Log In
Come Join Us!
Are you a Computer / IT professional? Join TekTips Forums!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
 Students Click Here
*TekTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden. Students Click Here

Best of Excel
How do I create a graph in Excel and get it to ignore blanks or zeros? by Hasit
Posted: 15 Nov 01

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 CTRLSHIFTENTER.
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))}

Back to Microsoft: Office FAQ Index
Back to Microsoft: Office Forum 


