Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.charting
SteveChap
 
Posts: n/a
Default Conditional Formatting in Bar Charts with Labeling

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   Report Post  
Posted to microsoft.public.excel.charting
Andy Pope
 
Posts: n/a
Default Conditional Formatting in Bar Charts with Labeling

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   Report Post  
Posted to microsoft.public.excel.charting
SteveChap
 
Posts: n/a
Default Conditional Formatting in Bar Charts with Labeling



"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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
cannot use ISEVEN or ISODD functions in Conditional Formatting Scott Paine Excel Worksheet Functions 6 December 6th 05 10:44 PM
enhanced conditional formatting Stuart Excel Discussion (Misc queries) 13 November 13th 05 08:20 PM
Conditional Formatting Error ddate Excel Worksheet Functions 0 May 5th 05 10:00 PM
Conditional formatting in Charts? Todd Charts and Charting in Excel 1 January 11th 05 07:32 PM
Copy conditional formatting across multiple rows? Gil Excel Discussion (Misc queries) 1 January 11th 05 12:27 PM


All times are GMT +1. The time now is 12:36 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"