Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
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? |
#2
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.charting
|
|||
|
|||
![]() "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions | |||
enhanced conditional formatting | Excel Discussion (Misc queries) | |||
Conditional Formatting Error | Excel Worksheet Functions | |||
Conditional formatting in Charts? | Charts and Charting in Excel | |||
Copy conditional formatting across multiple rows? | Excel Discussion (Misc queries) |