Forum Discussion

youhavegreattaste's avatar
youhavegreattaste
Copper Contributor
Oct 18, 2022

Need some help with an IF logic test -

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

 

excel
Formulas and Functions

3 Replies

    • youhavegreattaste's avatar
      youhavegreattaste
      Copper Contributor
      Oct 18, 2022
      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 🙂
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Oct 18, 2022

        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.

Resources