Skipping Empty Cells When Plotting 3

[mathias1979]

I know I can go to chart options and select to skip empty cells when plotting a graph. The problem is, the way I've programmed my spreadsheet, the cells aren't technically empty.

I am averaging groups of cells to create my time series. When there is missing data for a certain time period, the equation is programmed to return a blank to avoid a DIV/0 error...i.e.

=IF(ISNUMBER(AVERAGE(A3:A50)),AVERAGE(A3:A50),"")

But if a period returns a blank value, instead of treating this as a true empty cell and skipping the point on my graph, Excel treats it as a zero.

Is there anyway around this? How can I force Excel to treat this as a true empty cell?

Thanks,
Matt

 

[SkipVought]

Hi,

Instead of a formula, you must run a VBA procedure to populate your data.

Skip,
[sub]
[red][/red]
[/sub]

 

[xlhelp]

Change your formula to
=IF(ISNUMBER(AVERAGE(A3:A50)),AVERAGE(A3:A50),"#N/A")


Member- AAAA Association Against Acronym Abusers

 

[mathias1979]

xlhelp...that doesn't solve the problem. The problem is that for Excel to completely skip the points on a graph, the cell seems to have to be truely empty. Excel treats a blank (""), #N/A, or #DIV/0 as zeros when plotting.

Skip...can you elaborate any more on how VBA can solve my problem?

 

[SkipVought]

"#N/A" did not work, but NA() does as far as leaving gaps in a plot.
[tt]
=IF(ISNUMBER(AVERAGE(A3:A50)),AVERAGE(A3:A50),NA())
[/tt]
Good tip xlhelp!

Skip,
[sub]
[red][/red]
[/sub]

 

[mathias1979]

yes, NA() gets the job done...thanks guys! huge help!

 

[AnotherHiggins]

I do this pretty often.

The problem with xlhelp's formula is that you don't put quotes around the #N/A. If you do, then Excel considers it a text string.

[COLOR=blue white]=IF(ISNUMBER(AVERAGE(A3:A50)),AVERAGE(A3:A50),#N/A)[/color]
will work.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.