Equal Values Inconsistency

[AspiringAutomater]

Excel appears to give inconsistent results on comparing values when the compared values are decimals other than .5 or integers. Oddly I found an approximate cut-off point for 0.9, but the inconsistency strikes other decimal figures at different thresholds. I believe I've eliminated formatting, display issues, and hopefully user error, and these did not go away after installing updates and relaunching/rebooting etc. Has anyone seen this before? What am I doing wrong or do you know how it can be fixed? Thank you.

 

[KRice]

Yes...although I can't tell what the issue might be based on the image you've posted. See my commentary here:

Rounding Daily Totals on Time Sheets Without Changing Employee Entries

I have looked everywhere, but am coming up empty. I work at a small business that does not require any overtime from the employees. I tried using the code below, but 2 employees ended up with overtime on their calculation. 40.15 and 40.2 respectively. =IFERROR(IF(COUNT(TimeSheet[@[Time...

www.mrexcel.com

I suspect you've encountered an Excel numerical precision issue that can lead to problems when logical comparisons are made. You may want to consider forcing numerical rounding to a known level of precision and then making the logical comparison.

 

[AspiringAutomater]

Thank you Kirk for such a quick answer. I read your explanation to the attached query and I agree you are probably right. I've included a tolerance figure, and I now get "TRUE" results. Instead of =A2=D2, =ABS(A2-D2)<.000005....but it works without ABS() whether A-D or D-A, which I believe also confirms your suspicion. Bit perplexing that this can apply to figures to only the tenths and hundredths places but we aren't going to solve that here!

 

[KRice]

This issue appears regularly, even in some unexpected places where we know a number should be expressed exactly...but because of limitations handing the binary representation of values, there are some precision-rounding errors. Microsoft describes the issue here:

Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps

Discusses that floating-point arithmetic may give inaccurate results in Excel.

docs.microsoft.com