Need some help with an IF logic test -

[youhavegreattaste]

I’m trying to create a logic test to use throughout a really large time data set. The two cells subtracted need to be consecutive and separated by only 0.001 so I want to be able to quickly identify discrepancies using a function. The the test I tried to use didn’t work correctly. The cells selected do have a subtracted result of 0.001 (16.904-16.903) and so I expected the true value to be visible. It showed the false value (Missing) instead. Please help me fix this! Pulling my hairs out but haven’t found a solution yet.

the test function I used: but the answer didn’t match the truth.

=IF(L3-M2=0.001, “ “, “Missing”)

[#2]

@youhavegreattaste 

Perhaps

=IF(ROUND(L3-M2,3)=0.001,"","Missing")

[#3]

Wow thank you. What does round mean? Why did that work? I don’t use excel much clearly.. sorry if this is an annoying question :)

[#4]

@youhavegreattaste 

That's a very good question!

ROUND(number, 3) rounds number to 3 decimal places, so for example 3.4192 becomes 3.419 and 2.1678 becomes 2.168.

I used ROUND because calculations involving decimals are often not 100% accurate - there can be tiny rounding errors caused by the conversion from the decimal notation that we use to the binary notation that Excel uses internally, and back.

Since your numbers have 3 significant decimal places, rounding the difference to 3 decimal places takes care of the inaccuracy.