Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Using "OR"
I'm wanting to use multiple conditions using "OR" for conditional formatting.
Here's the criteria for percentage of completion of 6 and 9 months from entering training: 6 months from entering training 0-25% Red 26-50% Yellow 51-100% Green 9 months from entering training 0-50% Red 51-75% Yellow 76-100% Green Here's an example of a formula that didn't work: =OR(B3+182.5<=TODAY()-182.5,AI3<=25%) B3 is the date entered training and AI3 is the percentage of completion and where I'm wanting to have the background change to match the color criteria. This 1st formula didn't work, so I couldn't continue. Thanks, Robbie -- Michael Robinson |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Using "OR"
Well, Robbie, you are adding 6 months onto the starting date and then
taking 6 months off today's date, so I don't think it will work. I think you need a formula like this: =OR(AND(B3+182.5<=TODAY(),AI3<=25%),AND(B3+273.75< =TODAY(),AI3<=50%)) and use this to turn the cell red. For your second condition you will have a very similar formula: =OR(AND(B3+182.5<=TODAY(),AI3<=50%),AND(B3+273.75< =TODAY(),AI3<=75%)) and set the cell to yellow. Then for your third condition: =OR(AND(B3+182.5<=TODAY(),AI3<=100%),AND(B3+273.75 <=TODAY(),AI3<=100%)) and choose green. You can use the Format Painter to apply this CF to other cells in the same column. Hope this helps. Pete On Jun 25, 12:16*am, Robbie wrote: I'm wanting to use multiple conditions using "OR" for conditional formatting. *Here's the criteria for percentage of completion of 6 and 9 months from entering training: 6 months from entering training 0-25% Red 26-50% Yellow 51-100% Green 9 months from entering training 0-50% Red 51-75% Yellow 76-100% Green Here's an example of a formula that didn't work: =OR(B3+182.5<=TODAY()-182.5,AI3<=25%) B3 is the date entered training and AI3 is the percentage of completion and where I'm wanting to have the background change to match the color criteria. * This 1st formula didn't work, so I couldn't continue. Thanks, Robbie -- Michael Robinson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Using "OR"
Thanks, Pete! The formulas and color-coding are functioning properly!
Robbie -- Michael Robinson "Pete_UK" wrote: Well, Robbie, you are adding 6 months onto the starting date and then taking 6 months off today's date, so I don't think it will work. I think you need a formula like this: =OR(AND(B3+182.5<=TODAY(),AI3<=25%),AND(B3+273.75< =TODAY(),AI3<=50%)) and use this to turn the cell red. For your second condition you will have a very similar formula: =OR(AND(B3+182.5<=TODAY(),AI3<=50%),AND(B3+273.75< =TODAY(),AI3<=75%)) and set the cell to yellow. Then for your third condition: =OR(AND(B3+182.5<=TODAY(),AI3<=100%),AND(B3+273.75 <=TODAY(),AI3<=100%)) and choose green. You can use the Format Painter to apply this CF to other cells in the same column. Hope this helps. Pete On Jun 25, 12:16 am, Robbie wrote: I'm wanting to use multiple conditions using "OR" for conditional formatting. Here's the criteria for percentage of completion of 6 and 9 months from entering training: 6 months from entering training 0-25% Red 26-50% Yellow 51-100% Green 9 months from entering training 0-50% Red 51-75% Yellow 76-100% Green Here's an example of a formula that didn't work: =OR(B3+182.5<=TODAY()-182.5,AI3<=25%) B3 is the date entered training and AI3 is the percentage of completion and where I'm wanting to have the background change to match the color criteria. This 1st formula didn't work, so I couldn't continue. Thanks, Robbie -- Michael Robinson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Using "OR"
You're welcome, Robbie - glad to hear it.
Pete On Jun 25, 4:19*pm, Robbie wrote: Thanks, Pete! *The formulas and color-coding are functioning properly! Robbie -- Michael Robinson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting cells with formulas = "#VALUE!" | Excel Discussion (Misc queries) | |||
conditional formatting "if part of cell contents contains string" | Excel Worksheet Functions | |||
Conditional Formatting Bottom 10 with "ties" | Excel Worksheet Functions | |||
Conditional Formatting Bottom 10 with "ties" | Excel Discussion (Misc queries) | |||
Conditional Formatting-can a "then" be placed with the "if". | Excel Worksheet Functions |