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" <exalan@discussions.microsoft.com> wrote in message news:7177877D-C4F8-48D3-B6BB-69DE46A256E0@microsoft.com...
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
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)