Forum Discussion
Solved
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...
ArnoutM Use this in stead:
To allow for the small rounding difference use this formula:
=ABS($K$41-$K$39)<=0.05
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.
- Confusing, but I'm guessing the formula used in the 'Rule' section has to be the one that applies to the left-most cell in the 'applies to' range (and top-most if the conditional formatting applies to more than one row)?
So for instance if the formula in the 'Rule' was =ABS(L41-L39)<=0.05, while the 'Applies to' range remains =$K$41:$L$41, then I suppose in cell K41 the conditional formatting would look at L41 and L39, and in cell L41 it would look at M41 and M39 (which I don't want, obviously). Am I right?
