if formula showing as incorrect when it is correct

Spread sheet that is checking if the total in column J is the same as tab "kit"

Try

Formula:

Please Login or Register  to view this content.

When doing a direct comparison of two values that are the result of mathematical manipulation, it's best to round those values because there may be slight inaccuracies (out to the 15th decimal place) due to the fact that computers calculate using binary and we calculate in a decimal system. Hope that helps.

The value in kit H30 (for the record) is -52.5999999999998.

You have two solutions for this (that are the same) so I will take a minute to explain the problem.

When dealing with real numbers on computer, some numbers cannot be represented exactly. (Think of how you would write 2/3 as a decimal number--you can't write it exactly because the 6 would go on forever. So it usually ends up rounded with the final digit being 7.)

When you do arithmetic with real numbers on a computer, even if you think two numbers should be the same, they might not be. This gets complicated so I won't explain the technical details, but here are the two numbers you are comparing. These are the actual values, although your formatting is rounding the numbers to two decimal places. You can see if you go out to the 13th place, they are not the same (Excel only maintains 15 significant digits).

Download!J250 = -£52.5999999999999

Kit!H30 = -£52.5999999999998

The general rule is that whenever you are comparing two real numbers for equality, you must round to the precision of interest get a meaningful result. In this case you need to round to the nearest pence.