Conditional Highlighting if values are different

Question

Conditional Highlighting if values are different

I have a table where I am entering all the values from my paycheck and one where I am calculating the deductions based on the percentage I should be paying.

I would like for it to highlight a cell if the values are different.

I tried creating another table with Actual Check::B2=Calculated::B2 and then set the rule to highlight if the cell is FALSE.

This doesn't work because everywhere I have a formula in the calculated table the value on the new table is FALSE even if the results are the same as the value in the actual table.

I really don't feel like I explained this well but hopefully someone will understand or can at least ask the right questions to help me explain it better.

Posted on Mar 11, 2025 8:28 AM

Reply

Answer 1

Top-ranking reply

Camelot

Level 9

58,299 points

Mar 11, 2025 11:07 AM in response to xtzws

You could also round the values before comparison:

=IF(ROUND(A2,2)=ROUND(B2,2),TRUE,FALSE)

additionally, If you're just looking for a TRUE/FALSE flag you can eschew the IF() statement and just say:

=ROUND(A2,2)=ROUND(B2,2)

which will return TRUE or FALSE depending on whether the ROUNDed numbers match.

Reply

Answer 2

xtzws

Level 2

162 points

Mar 11, 2025 10:38 AM in response to xtzws

If you use percentages or multiplication, it’s likely that the formula will fail because of those cent fractions. E.g. if your actual check is 578.36 and calculated is 578.362, the formula will output false.

So here is my updated formula that checks to see if the calculated value is within 0.005% of the true value.

=IF(A2>(B2×0.99995),IF(A2<(B2×1.00005),TRUE,FALSE))

Reply

Answer 3

xtzws

Level 2

162 points

Mar 11, 2025 10:09 AM in response to jamesfromwakarusa

If you moved all the values to the same table, that could make it easier. I don’t know your exact layout, but in this formula, the actual value is in Column A and the calculated value is in Column B, with the formula going in Column C.

=IF(A2=B2,TRUE,FALSE)

Then add a conditional highlighting rule in Column C, like red for false and green for true.

Reply

Answer 4

Mar 11, 2025 11:18 AM in response to xtzws

This appears to be it! I should have mentioned this in the initial question. In the calculated version I am using the % that was supposed to be deducted for taxes and comparing it to what was actually taken out.

The only change I had to make was 0.05% instead of 0.005%.

Reply

Answer 5

Mar 11, 2025 11:19 AM in response to Camelot

I didn't think of using round. I seem to overcomplicate things

Reply