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]

Could you give the exact range you want each color?
Lets say:
Green: value > 12000.
Yellow: 12000 >= value >= 8000
Red: 8000 > value
Would that work? or how do you want to define the "within 10k"?

 

[etaf]

put in 3 rules , in order

FOR GREEN
use a formula
=T3>10000 - use stop if true

new rule for yellow
=T3>???? - use stop if true

New rule for RED
=T3>??? - use stop if true

i'm not following the ranges

so for yellow if above say 1000
then
new rule for yellow
=T3>1000

and red ?? again what number

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
T3 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=T3>10000

Format [Number, Font, Border, Fill] FILL Green
choose the format you would like to apply when the condition is true
OK >> OK

repeat for the other rules

make sure green is the top rule and STOP IF TRUE

 

[cstickman]

Could you give the exact range you want each color?
Lets say:
Green: value > 12000.
Yellow: 12000 >= value >= 8000
Red: 8000 > value
Would that work? or how do you want to define the "within 10k"?

That is where I am confused because the negative number but negative means good in my case. It would be like the below:

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

 

[felixstraube]

Smaller than 1 would be red?

 

[felixstraube]

You can try this conditional formating for T3:

 

[cstickman]

You can try this conditional formating for T3:

View attachment 114495

I think we are getting closer. When I put in that formula here are the results:
100.00 = Red / correct
(37,807.41) = green / correct
61,536.61 = Red / correct
(7,508.00) = green / incorrect. It should be yellow because it is within 10,000.

 

[felixstraube]

I think you entered the formula incorrectly.
100 should give you yellow as you asked.
when you say (37,807.41) does that mean that it is a negative value?
Do you want he whole thing with the absolute value of the cell?

 

[felixstraube]

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

 

[cstickman]

I think you entered the formula incorrectly.
100 should give you yellow as you asked.
when you say (37,807.41) does that mean that it is a negative value?
Do you want he whole thing with the absolute value of the cell?

Yes, in this case a negative value is good. We want to be under budget so it will be negative. A positive value means we went over our approved budget and now we in the red.