G
Guest
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?
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?