×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*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.

Students Click Here

Jobs

Microsoft: Office FAQ

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 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

My Archive

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close