Hiding #N/A Labels on Column Chart

  • Thread starter Thread starter szgd5h
  • Start date Start date
S

szgd5h

I used the NA() function suggested by Tushar for line charts to avoi
plotting blank cells as 0s. It worked great. When I try it for
column chart, the #N/A data label appears when I select show dat
labels. I have the following data:

Joe 8 10 80%
Bob 8.5 10 85%
John 9 10 90%
Joyce 9.5 10 95%
Cindy 10 10 100%
Paul 10 10 100%
Al 0 0 #N/A
Julie 0 0 #N/A

I calculate the % with the following formula:
=IF(C1=0,NA(),B1/C1)

When I plot the data in column 1 and 4 in a column chart and show th
data labels, the #N/A appears. How can I get rid of this withou
having to modify the chart each time? (In other words, I can get ri
of it by manually clearing the cells with the #N/A in it each time, bu
I'd prefer not to.)
Thanks
 
For a column chart, unless you change the default value where the x-
axis crosses the y-axis, there is no difference between plotting 0 and
N/A -- at least in terms of a visible column. So, the NA() that's
required for a line chart may be unnecessary for a column chart.

That said, if you just plot cols 1 and 4 you will get Al and Julie as
names along the x-axis with no column above their respective names. I
assume that is what you want.

Option 1:
If you haven't changed the default setting mentioned in para 1, you can
simplify your work a bit.

Plot columns 1 and 3. Change column 4's formula to be "" rather than
NA(). Then, use either XY Chartlabeler (www.appspro.com) or Chart
Tools (www.j-walk.com) to add column 4 as the data label for the
column.

Option 2: This alternative is insensitive to the condition identified
in para 1 above. Add column 5 (ie., column E). E1 will contain the
formula =IF(ISNA(D1),"",D1). Copy E1 as far down col. E as necessary.

Now, plot columns 1 and 4 and use Chartlabeler (or Chart Tools) to set
column E as the labels column.


--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Thanks Tushar! It opted to go the ChartLabeler route and use the isna
formula you suggested.

It seems to work so far.
Thanks again!
 
Back
Top