#N/A And Data Labels

[Bruce Harris]

I created a bar graph, for each month I am displaying
three items. For future months I use the value #N/A so the
graph will ignore it.

My problem is that the data label picks up the #N/A rather
than the correct value for the last month. For example if
my values are Jan=2, Feb=3, Mar=5 and Apr to Dec = #N/A,
the graph prints correctly, but the Data Labels show
Jan=2, Feb=3, Mar=#N/A.

Any thoughts, thanks

 

[Andy Pope]

Hi Bruce,

The #N/A data labels will all be clustered around the bar with the last
value, in your case Mar. I think you will find Mar data label is there
but covered.

To get the data labels to display correct you can link the data labels
to another helper column of text.

For example if your chart data is in A2:B13 , a column of month names
and data, with B5:B13 containing #N/A. In cells C2:C13 add this formula.
=IF(ISNUMBER(B2),B2," ")
and copy down thru C13.

Now link your data labels to the range C2:C13. To do this use this free
addin from Rob Bovey, http://www.appspro.com/Utilities/ChartLabeler.htm

Cheers
Andy