what every programmer should know about floating point

IIRC, unless the denominator of your fraction is able to be represented as sums of 1/2**N (mlx: clarified) (1/2 + 1/8, for example), you end up with an approximation of the value. Many times, this tends to be "close enough". If dealing with money, for example, you will often want to use some other representation than floating point under the hood (an integer representation of millicents or some such comes to mind).

An example REPL - perl -ne 'printf("%.60f\n", $_)'.

Repost of Re: Unexpected results when storing a Math::Round nearest value in MongoDB

Update: See My floating point comparison does not work. Why ? for more information

Update 2: Clarified after apparent confusion

Hmmm... didn’t I once hear someone saying that “1/10” could not be exactly represented in base-two, just as “1/3” (0.333....) cannot be exactly represented in base-ten?

I am not sure I understand your point. I don't think that I said that 1/10 could be represented in base-two.

--MidLifeXis

This is because you're using a digital computer. It's nothing to do with Excel or Perl. http://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html

Regards,

John Davies

Hi runrig,

I am printing $cell from below

 my $cell = $sheet -> {Cells} [$row] [$col];

this itself give me incorrect result.

I have used

$cell_value=sprintf("%s", $cell -> {Val});

to get the values

Thanks,

Looks like a rounding error - can't you format the results to show values in the needed precision?

Hi space_monk,

Thnks for your repply. This happens only with some record like in 1,000 records only with 5-6 record. So can't format it or do round off again as this will affect other value.I dont want other value to be rounded off. There are values which have values upto 3 places or 4 places.

thanks,

Sandeep

Your calculations will still round up if you accept 5 or 6 decimal places, From your examples It is only the least significant bit at 14-15dp or so that is causing the problem

1.139999999999999 will round up to 1.14 at anything up to 14dp (if I've counted correctly)

If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)

It has been a while since anyone posted to this thread but the following trick may help someone else.

This feature of SpreadsheetXLSX has annoyed me for several years.

When the Cell is Numeric, try using $cell->unformatted() +0 ;

Worked for me ... it did remove the excess precision from the numbers.

-- kjh

Example:

# place these somewhere in the BEGIN Section
#
my $DateFmt    = "yyyy-mm-dd" ;

my %HtmlShitAry ;
   $HtmlShitAry{ ' ' } = ' ' ;
   $HtmlShitAry{ ''' } = "'" ;
   $HtmlShitAry{ '"' } = '"' ;   # Watch this one -- it bites !!!
   $HtmlShitAry{ '&' }  = '&' ;
   $HtmlShitAry{ '&lt;' }   = '<' ;
   $HtmlShitAry{ '&gt;' }   = '>' ;

my $HtmlShitREx = join "|", keys %HtmlShitAry ;
   $HtmlShitREx = qr/$HtmlShitREx/ ;
#
# place those somewhere in the BEGIN Section

:

#
# then, to 'fix' excess precision, add 0 to unformatted() ;
#
   if ( defined $Cell->{Type} )
   {
      if ( $Cell->{Type} eq 'Date' )
      {
         $Val = ExcelFmt( $DateFmt, $Cell->{Val} );
      }
      elsif ( $Cell->{Type} eq 'Numeric' )
      {
         $Val = ( $Cell->unformatted() )
                ? $Cell->unformatted() +0  # fix precision !
                : 0 ;
      }
      else
      {
         $Val =~ s/($HtmlShitREx)/$HtmlShitAry{ $1 }/g ;
      }
   }
   else
   {
      $Val =~ s/($HtmlShitREx)/$HtmlShitAry{ $1 }/g ;
   }
[download]