Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Hi gurus. I'm using OLE with ActiveState perl5.003 (it's not my choosing) to read from an Excel spreadsheet. In particular, I read two largely negative float values (ie -3809482.42154223) and I am subtracting them. 
#x & y are both -3809482.42154223
my $diff = abs( $x - $y );

[download]
to my shock & dismay, $diff ended up with the value '-4.65661287307739e-010'. I ran the same code in a test perl script to maintain my level of sanity. 
my $x='-3809482.42154223';
my $y='-3809482.42154223';

my $diff = abs( $x - $y );
print $diff, "\n";

[download]
and lo and behold, $diff ended up to be zero. What is going on? Any clues? thanks, Michael

Replies are listed 'Best First'.
Re: weird excel problem
by fglock (Vicar) on Oct 04, 2002 at 16:37 UTC

    Your 2 numbers might have different internal binary representations. See example below:

    my $x='-3809482.4215422305'+0;
my $y='-3809482.4215422301'+0;

my $diff = abs( $x - $y );
print $x,"\n",$y,"\n",$diff,"\n";

    [download]

    Output:

    -3809482.42154223
-3809482.42154223
4.65661287307739e-10

    [download]

    (perl 5.6.1 on linux)

[reply]
[d/l]
[select]
Re: weird excel problem
by RMGir (Prior) on Oct 04, 2002 at 16:39 UTC
    First, what's the result of 
    if($x==$y) {
    print "EQUAL\n";
}
else {
    print "UNEQUAL\n";
}

    [download]
    in the excel code?

    Just because the 2 values print out as being equal out to 8 decimal places doesn't mean there isn't a difference in the 10th decimal place, which is what Excel is telling you...

    Although I think those numbers look pretty close to the precision you can expect from doubles.

    Another alternative would be to print out $x-$y at the perl level before calling Excel... I'm not sure your test is working with the same values Excel is, even if they are what got printed out.
    --
    Mike

[reply]
[d/l]
Re: weird excel problem
by jmcnamara (Monsignor) on Oct 04, 2002 at 21:55 UTC
[reply]

Back to Seekers of Perl Wisdom