# Thread: Plotting blank cells in a chart (2000)

1. ## Plotting blank cells in a chart (2000)

I am making an xy scatter plot. The y range cells have a formula of the type IF(condition, number, ""). So, some of the cells (the "legal" ones) have a number to be plotted, while others (the "invalid" ones) contain nothing. The problem is how the "nothing" is interpreted. I can apply a numerical formula to the range, such as to compute the average value, and the "nothing" cells are indeed ignored like they should be (the result is the average of the valid cells, as opposed to the average of all cells). But in the scatter plot, the "nothing" cells are interpreted as "zeros," meaning that they generate a plot symbol at the zero position. Is there any way to get around this, and have the "nothing" cells ignored in the chart?

2. ## Re: Plotting blank cells in a chart (2000)

Use
IF(condition, number, na())

The na() is the ONLY non number that is NOT interpreted by the chart as a zero. It will be ignored and the points before and after wil be connected.

Steve

3. ## Re: Plotting blank cells in a chart (2000)

Thanks Steve! I had a feeling there was a function like this, I just couldn't find it.
Unfortunately, while it solves my plotting problem, it destroys my AVERAGE calculation--The average formula now returns a #N/A error.
So I'm afraid my problem remains: How can I get Excel to avoid the "invalid" cells in both numerical calculations AND charts?

4. ## Re: Plotting blank cells in a chart (2000)

You have now to introduce a different AVERAGE formula:

=AVERAGE(IF(Range,Range))

which you need to confirm with control+shift+enter, instead of just enter.

5. ## Re: Plotting blank cells in a chart (2000)

Don't you mean something like this: (confirm with ctrl-shift-enter)
=AVERAGE(IF(NOT(ISNA(range)),Range))

=AVERAGE(IF(Range,Range))
will give #na error with #na and #value with any text values

Steve

6. ## Re: Plotting blank cells in a chart (2000)

I meant indeed to post:

=AVERAGE(IF(ISNUMBER(Range),Range))

which must be confirmed with control+shift+enter, not just enter.

Thanks for pointing out the omission.

7. ## Re: Plotting blank cells in a chart (2000)

Thanks everybody, you've been most helpful!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•