Oct 18 2022 01:25 PM
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.
Oct 18 2022 01:33 PM
Oct 18 2022 01:37 PM
Oct 18 2022 01:47 PM - edited Oct 18 2022 01:47 PM
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.