Conditional Formatting Assistance

[cstickman]

Hey everyone,

I need assistance with some conditional formatting. Whatever reason I have never been good at using them. In column T3 is what the projected amount for the remainder of the year minus the approved amount. I would like to highlight the cells based on three conditions. If the number in T3 is greater than 10,000 I would like it to be green, If it is within 10,000 then go yellow and if it is less than 10,000 go in red.

(72,108) this would be green because it is more than 10K. Negative number because we are way below the budget amount.
(9,000) this would be yellow because we are within 10K
100 this would be red because we are over budget now.

Any assistance would be greatly appreciated. Thanks!!

 

[felixstraube]

Would you mind to explain in more detail? It is very confusing. In every post you say something different.

 

[cstickman]

Why dont you give a complete list of the relevant values, and in the column to the right you show us the expected result.

Excellent Idea. I put in some other numbers to show the big picture. It would not allow me to upload an Excel file

YTD	Budget	Projection
$ 384,209.73	$ 614,122.00	$ (229,912.27)	Green - we have more than 10,000 before reaching budget
$ 140,191.74	$ 148,751.00	$ (8,559.26)	Yellow - because we are within 10,000 of the budget
$ 66,325.45	$ 88,247.00	$ (21,921.55)	Green - we have more than 10,000 before reaching budget
$ 440.00	$ 346.00	$ 94.00	Red because we are now over budget with a positive number

 

[felixstraube]

Would this table be correct?:

Book1
	A	B	C	D	E	F
1			Proyection	Green	Yellow	Red
2			-229,912.27	TRUE	FALSE	FALSE
3			-21,921.55	TRUE	FALSE	FALSE
4			-11,000.00	TRUE	FALSE	FALSE
5			-10,001.00	TRUE	FALSE	FALSE
6			-10,000.00	FALSE	TRUE	FALSE
7			-9,999.00	FALSE	TRUE	FALSE
8			-8,559.26	FALSE	TRUE	FALSE
9			-1	FALSE	TRUE	FALSE
10			0	FALSE	TRUE	FALSE
11			1	FALSE	FALSE	TRUE
12			94.00	FALSE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=C2<-10000
E2:E12	E2	=AND(C2>=-10000, C2<=0)
F2:F12	F2	=C2>0

 

[etaf]

i dont follow . sorry

(10,001) or greater Green
Between (10,000) and (1.00) - yellow
Greater than 1.00 would be red

Are they negative numbers
so greater then - 10,000 (does not include 10,000 would be -12000 -14000 which in fact is LESS than
so rule 1 would be
T3<-10000

then from -1 to -10000
would be
T3<=-1
then
T3>-1

should be the 3 colours

 

[cstickman]

Would this table be correct?:

Book1
	A	B	C	D	E	F
1			Proyection	Green	Yellow	Red
2			-229,912.27	TRUE	FALSE	FALSE
3			-21,921.55	TRUE	FALSE	FALSE
4			-11,000.00	TRUE	FALSE	FALSE
5			-10,001.00	TRUE	FALSE	FALSE
6			-10,000.00	FALSE	TRUE	FALSE
7			-9,999.00	FALSE	TRUE	FALSE
8			-8,559.26	FALSE	TRUE	FALSE
9			-1	FALSE	TRUE	FALSE
10			0	FALSE	TRUE	FALSE
11			1	FALSE	FALSE	TRUE
12			94.00	FALSE	FALSE	TRUE
Sheet1

Cell Formulas
Range	Formula
D2:D12	D2	=C2<-10000
E2:E12	E2	=AND(C2>=-10000, C2<=0)
F2:F12	F2	=C2>0

Yes, that is correct and it works perfectly on my sheet!! Thank you very much!! I went in a marked it as the correct answer.

 

[felixstraube]

Ok then try this conditional formatting:

 

[felixstraube]

Yes, that is correct and it works perfectly on my sheet!! Thank you very much!! I went in a marked it as the correct answer.

Thanks for the feedback. Happy to help