Hi everyone, I am Emmanuel Katto and I would appreciate your guidance on a specific issue I'm facing with conditional formatting.
I have a dataset that tracks monthly sales figures for different products across several regions. The layout is as follows:
Column A: Product Name
Column B: Region
Column C: Sales Figures
Column D: Target Sales
I want to apply conditional formatting to the Sales Figures (Column C) so that:
If the sales figures (Column C) meet or exceed the target sales (Column D), the cell should be highlighted in green.
If the sales figures fall below 80% of the target sales, the cell should be highlighted in red.
If the sales figures are between 80% and 100% of the target sales, the cell should be highlighted in yellow.
The Challenge:
I’m not sure how to set up these multiple rules for conditional formatting. I tried using the 'Manage Rules' option, but I’m having difficulty figuring out the correct formulas to use for each condition.
What I’ve Tried:
I created individual conditional formatting rules but had trouble prioritizing them properly, resulting in some cells not formatting as expected.
I attempted using formulas directly in the conditional formatting rules, such as:
For green: =C1>=D1
For red: =C1<0.8*D1
For yellow: =AND(C1<0.8*D1, C1<D1)
However, I believe I might be missing some adjustments because they don’t seem to work correctly.
Could someone please provide a step-by-step guide on how to set up conditional formatting based on these criteria? Also, any tips on how to ensure that the rules are evaluated in the correct order would be greatly appreciated.
Thank you in advance for your assistance!
Best,
Emmanuel Katto
Bookmarks