See What Every Programmer Should Know About Floating-Point Arithmetic.

Tom Scott also has a nice video on the topic on Youtube: https://www.youtube.com/watch?v=PZRI1IfStY0

perl -e 'use MIME::Base64; print decode_base64("4pmsIE5ldmVyIGdvbm5hIGdpdmUgeW91IHVwCiAgTmV2ZXIgZ29ubmEgbGV0IHlvdSBkb3duLi4uIOKZqwo=");'

Thanks.. I'll check that.

haukex correctly identified the issue as floating point error. While I'm sure the site that was linked does a better job of explaining this problem than I will, but even though there are a bazillion such explanations on the web already, I'm going to explain it briefly again, and then I'm going to relate it to your specific example, and then I'm finally going to show you how to avoid this class of errors.

Floating point errors (a very truncated explanation)

Your problem is not a problem with Excel or Perl, but a problem with how your computer stores floating point (non-integer) numbers. Computers store numbers in binary (base 2), while you are asking for them to be printed in decimal (base 10). For integers up to a maximum size, there is no loss of information converting between binary and decimal. Floating point numbers offer no such guarantee. They are stored as three distinct parts: a sign bit, the exponent, and the mantissa/significand, which means your number has to be converted to look like number = sign * mantissa * 2^exponent. The problem is there are only so many bits available for the mantissa: 23 for single precision, or 52 for double. Those bits (b₀ .. b₂₃) are added together like so: b₀/2 + b₁/4 + ... Do you see now why some decimal values can't be represented exactly?

For example, -10.1 (your first example) looks like this in single precision IEEE-754 floating point format:

S Exponent        Mantissa
- -------- -----------------------
1 10000010 01000011001100110011010
- 130 => 3 (1/1) + 0/2 + 1/4 + 0/8 + 1/16 + 1/32 + ... ~ 1.26250004768
+37158
[download]

The actual exponent is exponent - 127, so 130 - 127 gives you 2³. This is to allow for negative exponents.

The mantissa is read from the back to the front (so the rightmost 0 corresponds to the 1/2s place, the one to the left of that is the 1/4s place, and so on). There's an implicit (1/1) added on that is not stored in the floating point number itself.

So putting all of that together, you get -1 * 2³ * (0/2 + 1/4 + 0/8 + 1/16 + 1/32 + ...), and, you guessed it, that doesn't work out exactly to -10.1.

How to fix floating point¹

In general, you can't; floating point binary numbers do not always convert exactly to decimal. You can increase the precision, but in practice, that just increases the count of unwanted 9s or 0s on the end of your number. You could use fixed point arithmetic if your numbers fall within a narrow enough range of exponents. You can use arbitrary precision like Math::BigFloat, but there is a massive performance penalty associated with that (not to mention the unbounded memory requirements), and you don't typically need unlimited precision anyway. So that brings me to the commonly used option, which is rounding to the required number of decimal places.

But you still need to be careful. Let's say you need two decimal places. You might think you can do printf "%.2f", $value and rely on that result. Not if you care about that last decimal place! Here's a classic example:

In other words, numbers that should be identical may be rounded differently. To get around this, you need to request one more decimal place than you need, and then look at the last digit (in decimal form) to decide whether to round up or down. Thankfully, there's a module for that:

use Math::Round;
printf "%s == %s\n", nearest(0.01, 7.525), 
                     nearest(0.01, 6.02 * 1.25); # 7.53 == 7.53
[download]

When to round is also important. You typically want to round right before you display or output the number, not before, or you will lose precision. And don't round and then shove the result back into a variable Perl will treat as a number, or you won't have fixed anything!

Floating point numbers are full of other fun pitfalls, especially if you start mixing formats. Please do read the link that haukex gave you for a much more complete discussion.

_______________
1. Pun absolutely intendianed.

Thanks a lot.. This helped me understand many things along with my issue

use warnings;

for(10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9) {
  printf "%.18g\n", $_;
}

__END__
Outputs:

10.0999999999999996
10.1999999999999993
10.3000000000000007
10.4000000000000004
10.5
10.5999999999999996
10.6999999999999993
10.8000000000000007
10.9000000000000004
[download]

use warnings;

for(10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9) {
  printf "%.3g\n", $_;
}

__END__
Outputs:

10.1
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
[download]

Add this to your program to show what versions are you using. Also what version of Excel was used to create myxls.xls ?

#!perl
use Spreadsheet::ParseExcel::SaveParser;
printf "OS %s
Perl %s
ParseExcel %s\n",$^O,$^V,$Spreadsheet::ParseExcel::VERSION;
[download]

Running your scripts I get

OS MSWin32
Perl v5.16.1
ParseExcel 0.65
-10.1-  -10.2-  -10.3-  -10.4-  -10.5-  -10.6-  -10.7-  -10.8-  -10.9-

It was surprising to me that your system doesn't show any apparent floating point error. This prompted me to (finally) try the OP's the code myself, and sure enough, I had perfect rounding, too! Yet the OP's output corresponds to what I would actually expect from a double precision IEEE-754 float:

perl -le 'printf "%.18f", 10.1'
10.099999999999999645
[download]

Suspecting skulduggery, I dug into the Spreadsheet::ParseExcel version 0.65 code, and I was not disappointed. See Utility.pm, around line 800. Indeed, the module does its own rounding because of RT#45626:

    # Fix for Perl and sprintf not rounding up like Excel.
    # http://rt.cpan.org/Public/Bug/Display.html?id=45626
    if ( $data =~ /^${number_result}5/ ) {
        $number_result =
            sprintf "%0${str_length}.${after_decimal}f",
            $data . '1';
    }
[download]

This section was last changed ten years ago, suggesting OP may have a very old version, so your request for the version number was apropos.

Aside: The sub that's in is called ExcelFmt and is almost 900 lines long. The entire package is 45000 lines. I'm pretty sure the section around line 800 is responsible for the rounding, but that's a lot of code to grok in five minutes. I'd be happy to be corrected on the specifics, but the point is, something is doing the rounding. :-)

And yes, OP, I'd still suggest doing your own rounding for the reasons stated in my comment, to ensure you are rounding to your own specifications.

It looks like your Perl was compiled to use "long double" for floating point numbers, hence 18 digits of precision instead of 15 (i.e. "%.18g" format) for default output. But Excel stores numbers as IEEE-754 doubles, so you simply loose precision on export to xls. What you are reading back from xls is worse approximation of e.g. "10.1" (which can't be represented precisely in binary floating point of whatever size) from your Perl POV, than what it converts source string "10.1" to.

Not sure if my next advice will be popular, but if your task is relatively short/simple scripts, and if you have to compare floating point numbers restored from xls binary data with e.g. user input (or any ASCII text file input), then, for simplicity/sanity, it just could be easier to use Perl which uses plain old IEE-754 doubles.

>perl -V:nvsize
nvsize='8';

>perl -MConfig -E "say $Config{uselongdouble}"


>perl -E "say unpack 'd', pack 'd', 10.1"
10.1

>perl -E "say unpack 'f', pack 'f', 10.1"
10.1000003814697
[download]