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