comment on

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.

variable_a has different maximum value:
  Change list: 101.945484 (Intermediate Variables)
  Range file : 101.945484 (Intermediate Variables)
variable_b has different minimum value:
  Change list: -80.5498336616321 (Intermediate Variables)
  Range file : -80.5498336616321 (Intermediate Variables)
variable_c has different maximum value:
  Change list: 45142.388774415 (Intermediate Variables)
  Range file : 45142.388774415 (Intermediate Variables)
variable_d has different minimum value:
  Change list: -159.905028384 (Intermediate Variables)
  Range file : -159.905028384 (Intermediate Variables)
variable_e has different minimum value:
  Change list: -159.905028384 (Intermediate Variables)
  Range file : -159.905028384 (Intermediate Variables)
variable_f has different maximum value:
  Change list: 181.019658002611 (Intermediate Variables)
  Range file : 181.019658002611 (Intermediate Variables)
variable_g has different maximum value:
  Change list: 1.57615706403821 (Intermediate Variables)
  Range file : 1.57615706403821 (Intermediate Variables)
[download]

and the perl snippet that produces this output:

# Testing. Script reporting differences in variables that have
# no visual difference. Try assigning to temporary intermediates.
# (no difference, obviously, or I wouldn't be writing this node)
my $change_min = $changes{$variable}{'min'};
my $change_max = $changes{$variable}{'max'};
my $range_min  = $range_file{$variable}{'min'};
my $range_max  = $range_file{$variable}{'max'};

if ($change_min != $range_min) {
    print "$variable has different minimum value:\n";
    print "  Change list: $changes{$variable}{min} ($changes{$variable
+}{sheet})\n";
    print "  Range file : $range_file{$variable}{min} ($range_file{$va
+riable}{sheet})\n";
}
    
if ($change_max != $range_max) {
    print "$variable has different maximum value:\n";
    print "  Change list: $changes{$variable}{max} ($changes{$variable
+}{sheet})\n";
    print "  Range file : $range_file{$variable}{max} ($range_file{$va
+riable}{sheet})\n";
}
[download]

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.

In reply to False positive on inequality comparison by Nkuvu

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


We don't bite newbies here... much
	PerlMonks