Conditional Formatting Positive & Negative Number

[Data123]

Hi I am looking for a way to color positive numbers green and negative numbers red, but as the value becomes greater for green or less for red so does the intensity of the color. Also, I would like to copy this format for other columns and those + and - numbers will range greatly between columns (see below). Lastly, is there a way to choose the min and max green and red for the range of color? Thanks!

Column 1
500
200
100
0
-4
-500

Column 2
4
3
2
0
-1
-4

Zero could be colored white.

 

[dreid1011]

There isn't a single rule that will be able to do what you want. You would have to create multiple rules to do it, one for each separate color.

 

[Data123]

There isn't a single rule that will be able to do what you want. You would have to create multiple rules to do it, one for each separate color.

thanks, would the individual rules be able to be based on percentile of + or - numbers? like 1/3rd of the top positive numbers would be colored dark green, then middle 1/3rd of + numbers less dark and bottom 1/3rd lightest green? if so can you tell me how to input the first one please?

 

[dreid1011]

Something like this for the positive values:

 

[Data123]

Something like this for the positive values:
View attachment 107174

thank you and yes i have tried this before. the issue with this is that i need + numbers to be green and - numbers to be red. unless there is a formula for percentile to help with this?

 

[dreid1011]

Then that is where you are going to have to make individual rules for each color level.

Perhaps something like this then:

Book1
	A	B	C	D	E
1	Max	Max
2	500	4
3	500	4		1	1
4	200	3		0.4	0.75
5	100	2		0.2	0.5
6	0	0		0	0
7	-20	-1		-0.04	-0.25
8	-500	-4		-1	-1
Sheet4

Cell Formulas
Range	Formula
D3:D8	D3	=A3/$A$2
E3:E8	E3	=B3/$B$2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:B8	Expression	=AND(A3/A$2>-0.33,A3/A$2<0)	text	NO
A3:B8	Expression	=AND(A3/A$2>-0.66,A3/A$2<=-0.33)	text	NO
A3:B8	Expression	=A3/A$2<=-0.66	text	NO
A3:B8	Expression	=AND(A3/A$2>0,A3/A$2<0.33)	text	NO
A3:B8	Expression	=AND(A3/A$2>=0.33,A3/A$2<0.66)	text	NO
A3:B8	Expression	=A3/A$2>=0.66	text	NO

Where A2 and B2 are the max, and then you can divide each row by the max to get your percentile. Columns D & E showing the results of the division to give you an idea how I set it up.

 

[Data123]

thank you! may i ask would this setup be able to automatically deal with values that change daily? meaning the colors would still stay red for negative numbers and green for positive numbers as the values change?

 

[dreid1011]

thank you! may i ask would this setup be able to automatically deal with values that change daily? meaning the colors would still stay red for negative numbers and green for positive numbers as the values change?

Yes, CF rules still apply to the cell no matter what the value is, and you're welcome.

 

[Data123]

may i ask can you explain columns D and E from your table above please? it looks like a percentile value of the highest and lowest value. i have 5 columns with 850 cells each that i am looking to add conditional formatting to.

 

[dreid1011]

may i ask can you explain columns D and E from your table above please? it looks like a percentile value of the highest and lowest value. i have 5 columns with 850 cells each that i am looking to add conditional formatting to.

Columns D & E in my example are just for reference. The values in those columns are already figured into the CF formulas, I only put them on the sheet to see them and compare to the highlighting in columns A & B. But yes, that is how I was figuring the percentile values compared to the maximum values set in A2 and B2.

Example changed the max in column B from 4 to 20:

Book1
	A	B	C	D	E
1	Max	Max
2	500	20
3	500	4		1	0.2
4	200	3		0.4	0.15
5	100	2		0.2	0.1
6	0	0		0	0
7	-4	-1		-0.008	-0.05
8	-500	-4		-1	-0.2
Sheet4

Cell Formulas
Range	Formula
D3:D8	D3	=A3/$A$2
E3:E8	E3	=B3/$B$2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3:B8	Expression	=AND(A3/A$2>-0.33,A3/A$2<0)	text	NO
A3:B8	Expression	=AND(A3/A$2>-0.66,A3/A$2<=-0.33)	text	NO
A3:B8	Expression	=A3/A$2<=-0.66	text	NO
A3:B8	Expression	=AND(A3/A$2>0,A3/A$2<0.33)	text	NO
A3:B8	Expression	=AND(A3/A$2>=0.33,A3/A$2<0.66)	text	NO
A3:B8	Expression	=A3/A$2>=0.66	text	NO