Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 28
Default Data label with text "N/A"

Hello,

I've a simple column chart plotting % of daily on time delivery. There are
days that I don't have any delivery so I insert "N/A" to the relevant cells.
However, when I inserted data labels to the chart, those values with "N/A"
were shown as 0%.

Without hiding the rows with "N/A", what other ways can I do to have data
labels not showing "N/A" as 0%?

Many thanks in advance...


--
exalan
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 6,582
Default Data label with text "N/A"

A line or XY chart skips a point when it encounters #N/A (note the # prefix,
which makes it an official Excel error). No point, no data label. N/A is
treated as text, so it is plotted as a point at zero, and it will have a
legend.

In column or bar charts, #N/A is treated the same as text, so the best bet
is to leave the cell blank. However, you can use a little number format
trick. Show the labels, then give the labels a custom number format of

0%;;;

There are four settings, separated by semicolons. The first, for positive
numbers, uses 0%. The other three, for negatives, zero, and text, use no
format, so those values are not displayed. Note: if you use the official
Excel error #N/A, this error will appear regardless of number format.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"exalan" wrote in message
...
Hello,

I've a simple column chart plotting % of daily on time delivery. There are
days that I don't have any delivery so I insert "N/A" to the relevant
cells.
However, when I inserted data labels to the chart, those values with "N/A"
were shown as 0%.

Without hiding the rows with "N/A", what other ways can I do to have data
labels not showing "N/A" as 0%?

Many thanks in advance...


--
exalan



  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 28
Default Data label with text "N/A"

Hello Lon

Many thanks for the advice.
--
exalan


"Jon Peltier" wrote:

A line or XY chart skips a point when it encounters #N/A (note the # prefix,
which makes it an official Excel error). No point, no data label. N/A is
treated as text, so it is plotted as a point at zero, and it will have a
legend.

In column or bar charts, #N/A is treated the same as text, so the best bet
is to leave the cell blank. However, you can use a little number format
trick. Show the labels, then give the labels a custom number format of

0%;;;

There are four settings, separated by semicolons. The first, for positive
numbers, uses 0%. The other three, for negatives, zero, and text, use no
format, so those values are not displayed. Note: if you use the official
Excel error #N/A, this error will appear regardless of number format.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"exalan" wrote in message
...
Hello,

I've a simple column chart plotting % of daily on time delivery. There are
days that I don't have any delivery so I insert "N/A" to the relevant
cells.
However, when I inserted data labels to the chart, those values with "N/A"
were shown as 0%.

Without hiding the rows with "N/A", what other ways can I do to have data
labels not showing "N/A" as 0%?

Many thanks in advance...


--
exalan




  #4   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1
Default Data label with text "N/A"

On Thursday, March 12, 2009 at 5:45:26 PM UTC+1, Jon Peltier wrote:
A line or XY chart skips a point when it encounters #N/A (note the # prefix,
which makes it an official Excel error). No point, no data label. N/A is
treated as text, so it is plotted as a point at zero, and it will have a
legend.

In column or bar charts, #N/A is treated the same as text, so the best bet
is to leave the cell blank. However, you can use a little number format
trick. Show the labels, then give the labels a custom number format of

0%;;;

There are four settings, separated by semicolons. The first, for positive
numbers, uses 0%. The other three, for negatives, zero, and text, use no
format, so those values are not displayed. Note: if you use the official
Excel error #N/A, this error will appear regardless of number format.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"exalan" wrote in message
...
Hello,

I've a simple column chart plotting % of daily on time delivery. There are
days that I don't have any delivery so I insert "N/A" to the relevant
cells.
However, when I inserted data labels to the chart, those values with "N/A"
were shown as 0%.

Without hiding the rows with "N/A", what other ways can I do to have data
labels not showing "N/A" as 0%?

Many thanks in advance...


--
exalan


Hello Jon,

This little trick is not working for me.
I've creaed many charts, on which on each this trick is working except for one.

I changed the number format but still #NA is showing up on the chart.

Thanks for your help (if possible)

Greetings,

Paul
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
Series 1 Point "0" Data Label Keyrookie Charts and Charting in Excel 2 November 15th 07 02:06 PM
possiblity to set data label in bar chart to "no wrap"?? Rhapsody Charts and Charting in Excel 1 September 27th 06 12:29 PM
Toggle Data Label "Value" On and Off [email protected] Charts and Charting in Excel 1 June 9th 06 07:52 PM
Insert "-" in text "1234567890" to have a output like this"123-456-7890" Alwyn Excel Discussion (Misc queries) 3 October 25th 05 11:36 PM


All times are GMT +1. The time now is 12:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"