|Problems? Is your data what you think it is?|
False positive on inequality comparisonby Nkuvu (Priest)
|on Apr 03, 2009 at 19:11 UTC||Need Help??|
Nkuvu has asked for the
wisdom of the Perl Monks concerning the following question:
OK, I'm stumped. I have a script that looks in two Excel files. The first file lists a bunch of changes to variable ranges. The second file contains the actual ranges. The script is just to verify that the range in the "range_file" has been updated properly according to the values given in the "change_file".
For all but seven of the variables, the comparison is working just fine. With these seven, though, a difference is reported -- except that the values are identical. Copy and pasted between the two spreadsheets, even. My best guess is that the inequality is due to floating point representation, even though I have about three or four dozen other floating point variables that match just fine. But I'm really not sure that that's the problem.
Note that the script is working fine for what I want to do, and this isn't a critical issue -- I can easily overlook the false positive difference report on a handful of variables. But it's more of a curiosity on "why is it doing this?" than trying to get my script to report these as the same.
The output below is cut and paste, except with variable names sanitized. The values listed are the exact values being compared. The perl snippet after that is a direct copy and paste. It's been far too long since I've had to convert to floating point binary representation. But if the "change_file" and "range_file" both have the same value in the cell, shouldn't the binary representation be the same? None of the values are the results of any sort of calculations, just straight assignment from numeric values in cells to hash entries.
and the perl snippet that produces this output:
If any more information is needed, please let me know.
Update: Thanks to all for the ideas on how to narrow down the problem. As it turns out, the fault lies with Excel (I know, big surprise). I updated the cell contents for the values that differed -- merely by editing the cell and pressing enter, not actually changing the value in any way. Once re-saved, my script no longer reported any differences. But I'm still glad I asked the question, picked up a few ideas on how to compare floats.