Forum Discussion
Condition formula required
Hi every one, I need your kind hep to create a formula for following scenerio
If the difference value is greater than 3% or Less than 3% of the monthly balance value, the colour red should appear in condition formatting; otherwise, the colour green should appear in condition formatting.
| Monthly balance | Next month estimated balance | Difference |
| 100 | 120 | 20 |
| 200 | 110 | -90 |
Let's assume that your data are in columns A to C, with headers in row 1.
Select the range you want to format conditionally, for example C2 and down, or A2:C2 and down.
The active cell in the selection should be in row 2.
Set the fill colour (or font colour, whichever you prefer) to red. This will be the default.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=ABS($C2)<3%*ABS($A2)
Click Format...
Activate the Fill tab (or the Font tab).
Select green as colour.
Click OK, then click OK again.
