×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# Microsoft: Office FAQ

## Best of Excel

 How do I create a graph in Excel and get it to ignore blanks or zeros? by Hasit faq68-1277 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 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))} Back to Microsoft: Office FAQ Index Back to Microsoft: Office Forum

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!