Blank if zero

[Andiroou]

HI guys, i have a 3 part question.


1) I've created a bargraph that has both positive and negative values. However, the graph visually has a bar(small albeit) for zero values. I want to get rid of this...which leads to part 2.

2) I figured I would do this by creating an IF statement like the one below

=IF(A9=0," ",A9)

The problem is that A9 is a percentage, and this formula does not seem to work when it is in this formatting.


3) Lastly, for the graph itself, how would I automate colors to negative and positve values? For example, whenever the graph has a positive value it will be colored Green..negative Red. Would I have to manually change the colors whenever I change the data?

[qinyg]

try replacing your formula with:

=IF(A9=0,NA(),A9)

[Andiroou]

i figured out why the if statement didn't work.

A9 was the Difference of two other percentages, the percentages in those cells were rounded off to the nearest whole number. Therefore when A9 showed 0%, it was infact like .00025%

I used ROUND to fix this.



Now if anyone can help me with #3.

Thanks

[martindwilson]

not that i really know this stuff but look here
http://www.peltiertech.com/Excel/Cha...nalChart1.html

[zbor]

You can divide data into two columns >0 and <0 and draw two graphs in same chart

[martindwilson]

ok based on that link i posted here's an example

[Andiroou]

awesome guys.. thanks a million.

Conditional charting is quite nifty.