May 04 2023 03:05 AM - edited May 04 2023 03:06 AM
Hi, I've attached an example to help understanding.
I have applied conditional formatting to visualise a RAG status. The payroll status is working fine, as long as the figure is below 1.05 it will be green. Between 1.05-1.1 will be yellow, as it falls between the 5-10% overbudget range. Then red for over 10%.
The problem I have is I need to apply another rule that is applied when it sees that the budget figure is negative. If the budget figure is negative, then i want it to follow the rule set out below named Labour RAG. I need these two rules to be applied to an entire column and I don't know if it can work. Thank you in advance
May 04 2023 03:17 AM
Could you provide more examples, with the desired color manually indicated in a separate column?
May 04 2023 03:41 AM
Sure, I hope this makes more sense. The current RAG status for the negative budget makes sense. It's saying the actual figure was not as negative as budgeted, therefore it is good. However I need it to ignore this logic and apply a separate rule.
May 04 2023 04:06 AM
SolutionYou cannot do that with a rule of type icon sets. It's possible with two rules of type 'Use a formula to determine which cells to format'.
See the attached version. The cells have green as default fill color; the rules apply yellow or red depending on a formula that refers to columns C and E.
May 04 2023 04:27 AM
May 04 2023 04:32 AM
May 04 2023 05:16 AM
Sure. I simply selected E2:E10 (or further down if required) and selected green as fill color.
In the attached version, the conditional formatting rules look at B2/C2 instead of at E2.
That allows us to clear the formulas from E2:E10.
May 04 2023 04:06 AM
SolutionYou cannot do that with a rule of type icon sets. It's possible with two rules of type 'Use a formula to determine which cells to format'.
See the attached version. The cells have green as default fill color; the rules apply yellow or red depending on a formula that refers to columns C and E.