Numerus rerum omnium nodus

This Latin inscription appears on a wall of the Istituto Nazionale di Statistica in Rome. The four words are a short form of a clause Cicero wrote in his Scipio’s dream and they mean “a number is the knot of almost everything” (at least, according to this web page).

Imho, this part of Scipio’s dream has come true nowadays, because many people having no clue of what numbers are as well as of how to properly deal with them can be found using high-tech equipment which usually relies heavily on binary numbers, e.g. These people get frightened by every unexpected result appearing on the display of their computer/smartphone/… Thus, particularly the manufacturers of popular spreadsheet programs have decided to hide nearly all the ugly details of calculations from their customers. But now, the experts get frightened, too, because they have no means to check every single step of a very complex calculation in detail. But of course, where there is a will there is a way.

Consider the “strange” results that can be observed in the following example obtained using the spreadsheet software called Microsoft®™ Excel®™. The first factor of 2^55 is huge, but in the second factor the very simple decimal numbers 0.5, 0.4, and 0.1 are combined in a way that should yield exactly 0 as result: (0.1+(0.4-0.5)) = (0.1+(-0.1)) = (0.1-0.1) = 0, thus yielding an overall result of 2^55*0 = 0. But this does not happen; in fact, the result is found to be 1. Because some decimal figures of the intermediate results are hidden, even the experts can hardly predict this final result.

In order to demystify this effect I’ve written a short but tricky module called Double_2_Hex which provides the functionality of displaying the bitwise internal representation of a number in Microsoft®™ Excel®™ as a hexadecimal string. More precisely, it deals with the data typ ‘Double’ in VBA®™. You may right-click here in order to download the code and use it if you obey to the GPL V3 license.

Dbl2Hex example

The first column of the table shows the formulae used to calculate the numbers given in the second column. Finally, in the third column the results obtained when applying the function Dbl2Hex to the numbers in second column are provided. Now it is no longer miraculous why the result of 2^55*(0.1+(0.4-0.5)) is 1 and not 0: the magnitude of the internal binary representation of 0.1 is larger than that of 0.5-0.4 by the tiny amount of 1/2^55 which approximately equals 3E-17. But unfortunately, Microsoft®™ Excel®™ shows 15 decimal digits of a number at maximum, so without the function Dbl2Hex there is little chance to understand what is going (wr)on(g) here.

There will be follow-up posts on some other facts concerning doubles as well as other spreadsheet programs.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: