Hiding #N/A Labels on Column Chart

I used the NA() function suggested by Tushar for line charts to avoid
plotting blank cells as 0s. It worked great. When I try it for a
column chart, the #N/A data label appears when I select show data
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 the
data labels, the #N/A appears. How can I get rid of this without
having to modify the chart each time? (In other words, I can get rid
of it by manually clearing the cells with the #N/A in it each time, but
I'd prefer not to.)
Thanks.


---
Message posted from http://www.ExcelForum.com/

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

In article , szgd5h
says...
I used the NA() function suggested by Tushar for line charts to avoid
plotting blank cells as 0s. It worked great. When I try it for a
column chart, the #N/A data label appears when I select show data
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 the
data labels, the #N/A appears. How can I get rid of this without
having to modify the chart each time? (In other words, I can get rid
of it by manually clearing the cells with the #N/A in it each time, but
I'd prefer not to.)
Thanks.


---
Message posted from http://www.ExcelForum.com/

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

http://support.microsoft.com/default...&Product=xlw2K

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


---
Message posted from http://www.ExcelForum.com/

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

It seems to work so far.
Thanks again!


---
Message posted from http://www.ExcelForum.com/

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

In article , szgd5h
says...
Thanks Tushar! It opted to go the ChartLabeler route and use the isna
formula you suggested.

It seems to work so far.
Thanks again!


---
Message posted from http://www.ExcelForum.com/