Ooh, now here’s a topic everyone can enjoy. Did you ever wonder why Excel can’t subtract 57 from 57.8? If you try it and expand your decimals out far enough, you’ll see that Excel returns .799999999999997 instead of .8. The reason is that Excel can only “think” in binary (1s and 0s). It must convert your base 10 numbers into base 2 numbers before it can do anything, then convert them back when it’s done. Just like translating your VCR instructions into Japanese and back into English comes with a cost, so does converting numbers between decimal and binary.

Let’s get into the details. First convert the numbers into their binary forms.

57 = 00111001

Each place in the binary number is 2^x, where x starts at zero on the right and increases by one. So this would be

0 x 2^7 = 0 x 128 = 0
0 x 2^6 = 0 x 64 = 0
1 x 2^5 = 1 x 32 = 32
1 x 2^4 = 1 x 16 = 16
1 x 2^3 = 1 x 8 = 8
0 x 2^2 = 0 x 4 = 0
0 x 2^1 = 0 x 2 = 0
1 x 2^0 = 1 x 1 = 1

and 0+0+32+16+8+0+0+1 = 57

The good people at Furman University’s Computer Science Department show us how to convert the decimal. Start with the decimal and multiply by 2. At each iteration multiply the decimal portion by 2 and take the whole number portions and string them together to make your binary number. When the fractional part is 0 or it starts to repeat, you’re done.

0.8
1.6 -> 1
1.2 -> 1
0.4 -> 0
0.8 -> 0

Since we’re back to the beginning, the decimal will repeat this pattern infinitely. Now our equation looks like this

00111001.11001100 – 00111001.00000000

I’m only repeating the decimals out to 8 bits, but Excel would take it much farther than that. Next, helpwithpcs.com says that we don’t subtract binary numbers, rather we add binary numbers with different signs. That means that our 57.8 – 57 should really be 57.8 + -57. Using the two’s complement method, we turn 57 to -57 and end up with this equation

That’s not really a shock. The stuff to the left of the decimal is all zeros and the stuff to the right of the decimal is unchanged, just as was expected. The imprecision comes in when you try to convert the answer back to decimal. Remember Excel is using a lot more numbers than I am, so my precision will be horrible.

Now we take our decimal (.11001100) and do the reverse of what we did above. Start with the right-most zero and start dividing by two.

0: 0 / 2 = 0
0: 0 + 0 = 0 -> 0 / 2 = 0
1: 0 + 1 = 1 -> 1 / 2 = .5
1: .5 + 1 = 1.5 -> 1.5 / 2 = .75
0: .75 + 0 = .75 -> .75 / 2 = .375
0: .375 + 0 = .375 -> .375 / 2 = .1875
1: .1875 + 1 = 1.1875 -> 1.1875 / 2 = .59375
1: .59375 + 1 = 1.59375 -> 1.59375 / 2 = .79688

The result, .79688, isn’t quite .8. Now you can use this example at parties. If you can’t impress the ladies with this, then I don’t know what works.