Is it possible to do a 3-color scale conditional format using a formula.

[Hoppingmad9]

My data set is mostly negative integers with some positive integers. This means that the regular 3-color scale makes the few positive numbers all the most extreme green so you can't tell the difference.
So when I'm only interested in the positive numbers is makes the regular 3-color scale fairly useless.
So I'm hoping to do a conditional formula along the lines of "=AND((3-color scale on data set),(Cell>0)

Thanks in advance
Sam

 

[tygrrboi]

use the number setting? Instead of percentile, choose the lowest value based on "number". Lowest value obviously being zero. Then choose your midpoint value using a "formula" =MAX(range)/2 Finally keep the highest value as it is... ("highest value")

 

[tygrrboi]

Additionally, if you would like the negative numbers to be removed from the 3 color system, add a new conditional format based on Formula and it is =(activecell)<0 Format it with no fill (or whatever you want negative numbers to be...). Then make sure this formatting rule is above the 3 color rule in the order of conditional formatting.

 

[Hoppingmad9]

Thank you so much! It works perfectly!

 

[tygrrboi]

Happy to help.