How to Apply Different RAG Conditional Formatting Rules for Positive and Negative Budgets in WPS Spreadsheets
Question details
The user needs to apply formula-driven RAG conditional formatting to a budget status column, with separate threshold rules for positive and negative values.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Formatting a full budget status column so that positive and negative figures receive green, yellow, or red status based on their own Labour RAG thresholds.
- Observed behavior
- Standard icon sets or a single RAG rule cannot distinguish between positive and negative budget values, causing incorrect status formatting.
Decide the exact green, yellow, and red threshold bands for both positive and negative budget values, and identify the first data cell in the RAG status column before you create the rules.
Create formula-based RAG rules for positive and negative budget values
Use this when a single icon set or fixed rule cannot color the full budget column correctly because positive and negative figures need different Labour RAG thresholds.
Formula rules evaluate both the sign and the threshold of each cell. By creating one rule per RAG color with OR/AND logic, you can apply separate positive and negative threshold bands across the entire column without changing the source data. Replace the sample numbers with your actual Labour RAG values.
Select the full range, for example C2:C100, and note the first cell in the selection, such as C2.
Click Home > Conditional Formatting > New Rule, then choose Use a formula to determine which cells to format.
In the formula box enter =OR(AND($C2>0,$C2>=100),AND($C2<0,$C2<=-100)), click Format, choose a green fill, and confirm.
Add a yellow rule with =OR(AND($C2>0,$C2>=50,$C2<100),AND($C2<0,$C2<=-50,$C2>-100)) and a red rule with =OR(AND($C2>0,$C2<50),AND($C2<0,$C2>-50)), setting yellow and red fills.
Click Home > Conditional Formatting > Manage Rules, set the rule order to Green, Yellow, Red, enable Stop If True for Green and Yellow, and set Applies to to the full range =$C$2:$C$100.
Set Up Sign-Based RAG Conditional Formatting in WPS Spreadsheets
WPS Spreadsheets lets you apply separate positive and negative RAG thresholds using formula-based conditional formatting, giving the same visual status column without changing your budget data.
- 1. Open the budget workbook: Open the budget workbook in WPS Spreadsheets and select the RAG status column.
- 2. Create a formula rule: Go to Home > Conditional Formatting > New Rule and choose Use a formula to determine which cells to format.
- 3. Add sign-aware RAG formulas: Create green, yellow, and red rules with formulas that include AND($C2>0,$C2>=threshold) for positive values and AND($C2<0,$C2<=threshold) for negative values.
- 4. Set rule order: Open Conditional Formatting > Manage Rules, order the rules Green-Yellow-Red, and check Stop If True for Green and Yellow.
- 5. Apply and verify: Set the Applies to range to the entire column and test representative positive and negative values.
Frequently Asked Questions
Can I use Icon Sets for separate positive and negative RAG thresholds?
Icon Sets do not branch cleanly by sign and custom thresholds across a whole column. Use formula-based rules with fills in WPS Spreadsheets instead, because you can test both sign and threshold in the same rule.
What formulas separate positive and negative RAG rules?
Use OR with two AND blocks: one for positive values such as =OR(AND($C2>0,$C2>=100),AND($C2<0,$C2<=-100)) for green. Adjust the thresholds for yellow and red using the same pattern.
How do I stop a cell from receiving multiple RAG colors?
Open Home > Conditional Formatting > Manage Rules, place Green, Yellow, Red in that order, and enable Stop If True for the Green and Yellow rules so the first match wins.
Can I apply these rules to an entire column when new rows are added?
Yes. Set the Applies to range to =$C:$C or a large range like =$C$2:$C$1000, and use relative row references such as $C2 so each row evaluates independently.
