• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Colour scale conditional formatting with a range of both positive and negative values

D

davecrt

Member
Hi at all my dear friends

Another wounderfull challange to share with you

I have a range of both positive and negative values

I would get as result that, if values is negative, then the colour scale should be different gradient of red, with intensinty increasing at bigger negative values. Same thing for positive values, but with a green scale.

If i do this with Format cells based on their values i get as result that excel does not "distinguish" between positive and negative values; therefore, if for instance the first rule applied is the one for the negative values, then i get the same red intesity cell colour for both, say, -10000 and +10000 and -5 and +5.

There is any chance to get that the above mentioned logic works separately for negative and positive values?

Version: Excel 2007

Thanx to all as ever
 
Good day davecrt

Have you looked at the CF data bars? or the icon sets.

I use 2013 and I am assuming that these are avalible in 2007;)
 
Hi Bob

data bars in excel 2010 do not make distinction between positive and negative data values. While in 2013 and 2012 version the bar go from left to right or right to left depending if the values is a positive or negative one, becoming also red if the values in sengative or green if positive.

therefore the tool unluckly does not help with the issue. Icons on the contrary do not highlight the cell, so are not helpful for this tasks.

the only solution i have found knopw is to create conditional formatting rules with different grades of green(positive values) or red (neg values) and then different range of number associated. It is a time consuming activity cause you have at least to create 5 rules with 5 grades of colour for both pos and neg values, but no other solution found...
 
Dear Davecrt

Can't you use a 3 color scale with the bottom set to red, the middle set to white and the top set to green. with the bottom set to lowest number, the middle to be 0 and the top to be the highest number.

A la attached picture
 

Attachments

  • Capture.PNG
    Capture.PNG
    11.2 KB · Views: 29
Jake, really an easy solution that when comes friday evening i'm not able to see..

Thanks a lot, really appreciated
Dave
 
You must log in or register to reply here.
Back
Top