Forum Discussion
Conditional formatting check amount
I want to check if the total of my specification agrees with my trial balance. But my check cell in K41 (with a value of 25.00 in the example attached turns green even if it isn't the same is the sum in K39 (-100.01). What am I doing wrong?
Also I'd like to build in a bit of a margin for rounding differences. So that a sum in K39 of 24.95 or 25.05 also turns K41 green. How do I do that?
ArnoutM Use this in stead:
To allow for the small rounding difference use this formula:
=ABS($K$41-$K$39)<=0.05
5 Replies
ArnoutM Use this in stead:
To allow for the small rounding difference use this formula:
=ABS($K$41-$K$39)<=0.05
- Dank!
It had to be something simple. Also thanks for the ABS formula, didn't think of that.
I can do quite a bit in Excel but conditional formatting remains tricky. As you might be able to guess I need a similar check for my debit amount in column J. I've copied the first rule and changed it to $J$41 and $J$39, but I'm sure there's an easier way to go about that too (more out of interest than out of necessity right now)?ArnoutM Glad I could help. Regarding the difficulties with CF, think of writing formulas that return either TRUE or FALSE and first enter them in the sheet itself. For instance in K43, enter the formula and copy it across to L43. Note that you should use relative reference (i.e. no $-signs).
If the results are correct copy the formula from K43 into CF and apply it to =$K$41:$L$41. Then it will always work as expected.
