Re: How to calculate the sum of columns to be equal to 100?

[sundialsvc4]

In any programming language (much to the vexation of accountants ... who, I cordially submit, get whatever they deserve), dollars-and-cents when treated as floating-point numbers will not always add-up “to the penny.”   In fact they can be off by several cents, depending on exactly how the information was processed.

One floating-point technique that is sometimes used is called banker’s rounding,” which rounds even-ending numbers one way, odd-ending numbers the other.   (I don’t rightly recall if Perl implements it.)   Another technique, used in (for instance) Microsoft Access for its CURRENCY data-type, is to use scaled integers.   Internally, the number is multiplied by 10000 to give a fixed four digits of precision. But, even then, it might not add-up perfectly.   It’s the math.   Accountants, you see, inadvertantly “lose the money ahead of time” by rounding to (say) two decimal-places first.   Their desk-calculators have a special mode to do that, which will produce different answers if instead set to ordinary floating-point mode.   (Politically-connected accountants have a different mode yet, which allows the answer to be “anything you want!”)   ;-)

Reproduce a similar issue in your accountant’s precious Microsoft Excel Spreadsheet, which you certainly can, and (s)he’ll be quiet(er).

ac - count - ant (n):
A person who will cheerfully spend a dollar looking for a penny, then bill you for it.

au - dit -or (n):
A highly-paid professional who will certify that both answers are equally wrong.

[Laurent_R]

One floating-point technique that is sometimes used is called banker’s rounding,” which rounds even-ending numbers one way, odd-ending numbers the other. (I don’t rightly recall if Perl implements it.)

Yes, Perl implements it, because it uses C libraries which implement the rounding recommended by IEEE.

The idea is the following. Suppose, to take a simple case, that you want to round to the unit numbers which have only one decimal digit. Any number where the decimal digit is less than 5 will be rounded down and any number where the decimal digit is larger than 5 is rounded up. But what do you do if the decimal part is exactly 5? Say, for example, how do you round 3.5? The most usual method rounds such a number up. But bankers claim that this introduces a bias towards rounding up: out of ten possible decimal digits, one will not be rounded (0), 4 will be rounded down (1, 2, 3 and 4) and 5 will be rounded up (5 to 9). This can make a difference if you add a long series of numbers. So they decided that the rounding of the 5 decimal digit will be rounded up or down, depending on whether the previous digit is odd or even.

This is what you can see in the somewhat strange output of a Perl one-liner below:

$ perl -e 'print "raw\t\trounded\n"; printf "%f\t%.0f\n", $_+0.5,  $_+
+0.5 for 0..10; '
raw             rounded
0.500000        0
1.500000        2
2.500000        2
3.500000        4
4.500000        4
5.500000        6
6.500000        6
7.500000        8
8.500000        8
9.500000        10
10.500000       10

I actually once had to write a special rounding module just because my client considered the above to be simply wrong and wanted 2.5 to be rounded to 3.

[Athanasius]

Thanks Laurent_R, this is useful information I hadn’t come across before. Is it documented anywhere? I don’t see anything in sprintf or the Camel Book, and the latest C Standard doesn’t seem to cover it either?

I actually once had to write a special rounding module just because my client considered the above to be simply wrong and wanted 2.5 to be rounded to 3.

Simply adding a minimum field width to the format seems to do the trick:

 1:03 >perl -wE "printf qq[%9f\t%.0f\t%2.0f\n], ($_ + 0.5) x 3 for 0 .
+. 10;"
 0.500000       0        1
 1.500000       2        2
 2.500000       2        3
 3.500000       4        4
 4.500000       4        5
 5.500000       6        6
 6.500000       6        7
 7.500000       8        8
 8.500000       8        9
 9.500000       10      10
10.500000       10      11

 1:03 >perl -v

This is perl 5, version 18, subversion 0 (v5.18.0) built for MSWin32-x
+86-multi-thread-64int

It should perhaps also be pointed out that where the internal representation of a floating point number is inexact, all bets are off:

 1:29 >perl -wE "printf qq[%21.18f\t%.1f\t%4.1f\n], ($_ + 0.05) x 3 fo
+r 0 .. 10;"
 0.050000000000000003   0.1      0.1
 1.050000000000000000   1.1      1.1
 2.049999999999999800   2.0      2.0
 3.049999999999999800   3.0      3.0
 4.049999999999999800   4.0      4.0
 5.049999999999999800   5.0      5.0
 6.049999999999999800   6.0      6.0
 7.049999999999999800   7.0      7.0
 8.050000000000000700   8.1      8.1
 9.050000000000000700   9.1      9.1
10.050000000000001000   10.1    10.1

 1:29 >

Cheers,

[Laurent_R]

Just found a reference to what I described: http://answers.oreilly.com/topic/415-how-to-round-floating-point-numbers-in-perl/

[Laurent_R]

I read it originally in the documentation of the standard C library. You can certainly find the IEEE standard on this.

Strangely, your "trick" with the format does not work for me on 5.14.2:

$ perl -wE 'printf qq[%9f\t%.0f\t%2.0f\n], ($_ + 0.5) x 3 for 0 .. 10;
+'
 0.500000       0        0
 1.500000       2        2
 2.500000       2        2
 3.500000       4        4
 4.500000       4        4
 5.500000       6        6
 6.500000       6        6
 7.500000       8        8
 8.500000       8        8
 9.500000       10      10
10.500000       10      10