I have created conditional formatting in a bar chart, but need to know how to
suppress N/A or 0 in the chart. My bar chart shows differences between this
year's data series, and last year's. For example, category A is -2 compared
with last year, and category B is +9. We conduct statistical testing (like in
election polling) -- and highlight negative significant differences with a
red bar, positive significant differences with a green bar, and all
insignificant differences with a grey bar. I aligned my 16 category scored in
a column, and then in the column to the right, denoted significance testing
with a -1 (negative), 0 (insignificant), and 1 (positive). Then using an IF
statement, I created the conditional formatting just fine (for example, one
of my array statements is: IF($E9=T$8,$D9,NA()). WHere E9 is the marker for
significance testing next to the actual values, and T8 is the lookup value
(-1,0-1), and D9 is my actual difference. When the cart is created, I get the
three different conditional colors I need, but when I apply the data value
lables, I get not only the value, but also N/A all over the place. And,
depending on whether it is negative/insignificant/positive, the bars are in
slightly different vertical orientations (it's really treating each category
as if it has three different series, with -1 being the first bar, 0 the
second, and 1 the third). Any suggestions on either of these conundra?

Hi,

One way would be to replace the NA() in your formula with text.
IF($E9=T$8,$D9,"N/A")
This will stop a column/bar from displaying as the text will be treated
as zero. You can then use a custom number format to hide zero's
General;-General;;

Cheers
Andy

SteveChap wrote:
> I have created conditional formatting in a bar chart, but need to know how to
> suppress N/A or 0 in the chart. My bar chart shows differences between this
> year's data series, and last year's. For example, category A is -2 compared
> with last year, and category B is +9. We conduct statistical testing (like in
> election polling) -- and highlight negative significant differences with a
> red bar, positive significant differences with a green bar, and all
> insignificant differences with a grey bar. I aligned my 16 category scored in
> a column, and then in the column to the right, denoted significance testing
> with a -1 (negative), 0 (insignificant), and 1 (positive). Then using an IF
> statement, I created the conditional formatting just fine (for example, one
> of my array statements is: IF($E9=T$8,$D9,NA()). WHere E9 is the marker for
> significance testing next to the actual values, and T8 is the lookup value
> (-1,0-1), and D9 is my actual difference. When the cart is created, I get the
> three different conditional colors I need, but when I apply the data value
> lables, I get not only the value, but also N/A all over the place. And,
> depending on whether it is negative/insignificant/positive, the bars are in
> slightly different vertical orientations (it's really treating each category
> as if it has three different series, with -1 being the first bar, 0 the
> second, and 1 the third). Any suggestions on either of these conundra?

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

"Andy Pope" wrote:

> Hi,
>
> One way would be to replace the NA() in your formula with text.
> IF($E9=T$8,$D9,"N/A")
> This will stop a column/bar from displaying as the text will be treated
> as zero. You can then use a custom number format to hide zero's
> General;-General;;
>
> Cheers
> Andy
>
> SteveChap wrote:
> > I have created conditional formatting in a bar chart, but need to know how to
> > suppress N/A or 0 in the chart. My bar chart shows differences between this
> > year's data series, and last year's. For example, category A is -2 compared
> > with last year, and category B is +9. We conduct statistical testing (like in
> > election polling) -- and highlight negative significant differences with a
> > red bar, positive significant differences with a green bar, and all
> > insignificant differences with a grey bar. I aligned my 16 category scored in
> > a column, and then in the column to the right, denoted significance testing
> > with a -1 (negative), 0 (insignificant), and 1 (positive). Then using an IF
> > statement, I created the conditional formatting just fine (for example, one
> > of my array statements is: IF($E9=T$8,$D9,NA()). WHere E9 is the marker for
> > significance testing next to the actual values, and T8 is the lookup value
> > (-1,0-1), and D9 is my actual difference. When the cart is created, I get the
> > three different conditional colors I need, but when I apply the data value
> > lables, I get not only the value, but also N/A all over the place. And,
> > depending on whether it is negative/insignificant/positive, the bars are in
> > slightly different vertical orientations (it's really treating each category
> > as if it has three different series, with -1 being the first bar, 0 the
> > second, and 1 the third). Any suggestions on either of these conundra?
>
> --
>
> Andy Pope, Microsoft MVP - Excel
> http://www.andypope.info
>