Custom Formatting a Chart Data Label

  • Thread starter Thread starter Telobamipada
  • Start date Start date
T

Telobamipada

I want to create a chart which will display a zero value on the data label
where there is in fact a zero value, and show N/A on the data label where it
didn't apply. The formula I am using in the spreadsheet uses a nested formula
to make this determination:
=IF(AND(B22+C22>0,C22>0),B22/C22,NA())
Presently, it works but displays the #N/A on the chart where I would prefer
it simply displayed as N/A. Can I do this with a custom format on the data
label??

Thanks so much! I don't know how you all do it but your amazing!
 
If your formula is being used for both the y-values AND the label, then you
will have to live with #N/A
If you are using a chart label utility (see below) then you could use this
for the label formula
=IF(AND(B22+C22>0,C22>0),B22/C22,"N/A")

Also it seems to me (after a big lunch and two glasses of wine) that you
could begin the formula
with =IF(B22*C22>0,
unless you are avoiding the case when both B22 and C22 are negative.

Label utilities
Rob Bovey's Chart Labeller, http://appspro.com
John Walkenbach's Chart Tools, http://j-walk.com
Tushar's Hover Chart Label utility:
http://tushar-mehta.com/excel/software/chart_hover_label/index.html

best wishes
 
Back
Top