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

+11000111.00000000

——————

00000000.11001100

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.

Wow. Truly nerdy stuff. I’ll have to pull out my Altair 8800 and think about this really hard.

At excel 2003, expanding the decimals gives 0.8000000 without a problem

If the binary subtraction doesn’t work at parties, try the imaginary math (sqare root of minus 1 stuff). You’ll never be lonely again – I should know.

Keep going Gustavo. Expand it out to >15 decimal places.

If you’re really bored you could write a function that converts all decimal numbers to BCD (Binary Coded Decimal) then do your subtraction. This would result in a real number.

Dick: “Just like translating your VCR instructions into Japanese and back into English comes with a cost”

Yeah, check out this site: http://engrish.com

Hilarious.

Good work on the explanation of the binary stuff BTW.

Too simplify my previous post you could multiply each item by 10 to get rid of the decimal place, then divide the result by 10 to get back where you started.

good job on most of the detail, but if you’re going to this much trouble, how about filling in a couple gaps?

“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?

(which isn’t demonstrated or explained)

and end up with this equation

00111001.11001100

+11000111.00000000

————

00000000.11001100

which seems to be missing the overflowed digit, which could also use some explaining. Nice article, might as well make it perfect!