I have known about the challenges of binary rounding since I started working with computers many years ago. I however have not seen any instance of it until the last night.
Binary rounding occurs when fractional decimal numbers cannot easily be translated into an exact binary representation. (As all whole decimal numbers have an equally valid whole binary representation.)
In these cases the translation between the human decimal number and the machine binary number is not bi-directional. This means that all maths functions working on one of these numbers will produce incorrect results. This was the case in last night’s episode.
Working on an Excel spreadsheet for my wife I discovered three numbers and a formula that caused binary rounding problems.The numbers involved are 8239.66, 7804.85 and 434.81. For the astute, you may already have realised that these numbers in the correct combination should equal zero. (8239.66 – 7804.85 – 434.81 = 0)
Indeed all this was the formula I was trying to create as a validation of some data entry elements. However I ran into problems as according to Excel with the answer did not equal zero. Instead the answer appears to be 5.11591E-13, (or 0.000000000000511590769747272) , or at least Excel tells me so.
Changing the formula by rearranging the numbers did nothing, as did checking on a different version of Excel and even with Libre Office on a Linux laptop all producing the same result.
It might be to my previous experience with Excel has been primarily related to poll numbers, or scientific work which requires the decimal positions, that has prevented me seeing this problem until now. Last night involved currency, and indeed, selecting the currency format for the columns involved appeared to fix the problem.
However formulas that checked the values were still showing that there was no match between the numbers. This is because the formatting of columns in excel does not change the underlying number in the sale but just the way in which it is displayed. I needed to be able to convert the numbers into binary and back again with sufficient accuracy for my wife’s needs but without causing binary rounding errors to appear.
In this case the Excel ROUND() function solved my problem. By using this on each of the individual numbers in the formula it solved the problem, as the binary rounding was then rounded on being reconverted to decimal to the “correct” value.
However, what irks me, is that a simple calculator can do this right, yet all PCs will still trip up on these issues occasionally.