Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Skipping Empty Cells When Plotting 3

Status
Not open for further replies.

mathias1979

Technical User
Sep 28, 2005
27
US
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
 



Hi,

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

Skip,
[sub]
[glasses] [red][/red]
[tongue][/sub]
 
Change your formula to
=IF(ISNUMBER(AVERAGE(A3:A50)),AVERAGE(A3:A50),"#N/A")


Member- AAAA Association Against Acronym Abusers
 
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?
 



"#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]
[glasses] [red][/red]
[tongue][/sub]
 
yes, NA() gets the job done...thanks guys! huge help!
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top