Conditional formatting check amount

[ArnoutM]

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?

[#2 · Accepted solution]

@ArnoutM Use this in stead:

To allow for the small rounding difference use this formula:

=ABS($K$41-$K$39)<=0.05

[#3]

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)?

[#4]

@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.

[#5]

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?

[#6]

@ArnoutM I believe so. And there may be occasions where you need to use absolute references (row, column or both). Just test the formula in the grid and, indeed, copy the formula from the top left cell. The CF rule will behave exactly the same as a formula on the grid. TRUE will apply the format, FALSE will skip it.