invert if negative for directional arrows

[wpk2200]

Hi all-


I am creating bar charts demonstrating the % change in energy consumption for a given sector from year to year. So simplistically, it has years on the bottom with the corresponding % change (3.5%, -2%, etc.) as a bar chart.


I would like to make this a little more interesting visually by having all the positive values be red arrows pointing up, and the negative values be green arrows pointing down.


Invert if negative works great to make the positive values a solid red fill, while the negative values a solid green fill.


However, I have been struggling to get the directional arrows to correspond. Is there a way to do this?


Thanks!

 

[Faseeh]

Hi wpk2200,


Actually you can do that with a trick, see the attached file:


http://dl.dropbox.com/u/60644346/wpk2200_resolved.xlsx


Regards,

Faseeh

 

[wpk2200]

Thanks, that is a good trick! If anybody else knows how to do this without having to separate the columns, that would be awesome. I have about 20 sheets of data and don't really want to have to reformat all of it and ensure everything is still linked...

 

[NARAYANK991]

Hi ,


This can be done if you are willing to define named ranges for all those series :


For the data range itself , define a dynamic range as follows :

[pre]

Name        :   Data_Range

Refers To   :   =OFFSET(Sheet1!$E$2,0,0,COUNTA(Sheet1!$E:$E)-1)
For the positive points define your range as :

[pre][code]Name        :   Positive_only

Refers To   :   =((Data_Range>0)*Data_Range)

[/pre]
For the negative points define your range as :

Name : Negative_only

Refers To : =((Data_Range<0)*Data_Range)[/code][/pre]
All of the addresses refer to Faseeh's workbook.


Narayan