Cells that "appear" empty plot as zero. Do not want cells to plot.

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I read thru some of the posts about plotting empty cells
and can't seem to find the right answer to my problem. I
have a bar chart (Jan thru Dec) which I currently show
data running through June. All cells contain the same
formula (therefore none are "really" empty). My formula
uses the " " to make the cells for July thru Dec "appear"
empty, therefore the graph is plotting these cells as
zero. I know that I could use 'NA()' and force a #NA
error, but I don't want that error message to appear in my
cells. Is there a way to not get the #NA to appear?!!
I've tried using different options in tools/options/charts
and haven't found anything that will work. Am I out of
luck or does anyone have any suggestions? Thanks.
 
Diane said:
I read thru some of the posts about plotting empty cells
and can't seem to find the right answer to my problem. I
have a bar chart (Jan thru Dec) which I currently show
data running through June. All cells contain the same
formula (therefore none are "really" empty). My formula
uses the " " to make the cells for July thru Dec "appear"
empty, therefore the graph is plotting these cells as
zero. I know that I could use 'NA()' and force a #NA
error, but I don't want that error message to appear in my
cells. Is there a way to not get the #NA to appear?!!
I've tried using different options in tools/options/charts
and haven't found anything that will work. Am I out of
luck or does anyone have any suggestions? Thanks.
Click to expand...

Here's one possibility. Maybe someone else has a better suggestion.

You could set your formula to return a #NA, then use conditional formatting
to make the text "invisible" in that case. The conditional formatting would
go something like this: Select cell A1, Format | Conditional Formatting |
Formula is | =NA(A1). Then set the font color to the same as the background
color (i.e. white). You can copy that cell and Paste Special | Formats

It's a workaround, but I think it might work for you.

Dave
dvt at psu dot edu
 
Back
Top