Charts Not Taking Alpha Character from Formula

**Excellerameter** · 11-11-2011, 11:24 AM

Hello All,
Ran into a little problem this week that I can't figure out. Am calculating percent changes in multiple fields between a base year and test year, and display results in a column chart. If the base year value is 0, I want the percent change to display "NA". This works in the percent change column, but dangit, the chart shows "0.00%" no matter what I do. Using formula:

=IF(A1=0,"NA",((A2-A1)/ABS(A1)))

where A1 is base year value and A2 is test year value. Also tried IFERROR with same result. What am I doing wrong? Overall, I am very pleased with Excel 2010 over 2003. Any input is appreciated.

**Andy Pope** · 11-11-2011, 11:47 AM

"NA" for the chart will be text and text is treated as zero.

You can use an additional series which uses the data values as the category labels as well as the data values.
Plot this on the secondary axis. Delete secondary value axis.
Apply data labels, showing category, to the second series
clear formatting of second data series.