Hiding #N/A Labels on Column Chart

[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

 

[Tushar Mehta]

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

 

[szgd5h]

I'm using Excel 2002. I found the exact problem I mentioned in m
posting listed on this microsoft website. It sounds like it can b
manually fixed by following their instructions.

http://support.microsoft.com/default.aspx?scid=kb;en-us;200078&Product=xlw2K

I'd still like a workaround fix if anyone has any suggestions.
Thanks

 

[szgd5h]

Thanks Tushar! It opted to go the ChartLabeler route and use the isna
formula you suggested.

It seems to work so far.
Thanks again!

 

[Tushar Mehta]

You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions