Would you believe Microsoft Excel cannot add up these 3 simple numbers without getting an error. Try it yourself, but not before you watch this video.
You can view part two in this series Microsoft Excel Bug Workarounds
For more information about the bug please read Microsoft Excel Addition Bug
Visit Making of the Excel Addition Video to see how we made the video.
Transcript
1
00:00:00,0 –> 00:00:02,320
– [Narrator] Would you
believe Microsoft Excel
2
00:00:02,320 –> 00:00:05,560
cannot add up these three simple numbers?
3
00:00:05,560 –> 00:00:07,760
Try it in Excel yourself,
4
00:00:07,760 –> 00:00:10,520
but not until you’ve watched this video.
5
00:00:10,520 –> 00:00:13,280
(relaxing music)
6
00:00:19,600 –> 00:00:21,400
Minus 123.45
7
00:00:22,320 –> 00:00:23,880
123
8
00:00:24,600 –> 00:00:27,360
.45
9
00:00:27,360 –> 00:00:28,200
AutoSum.
10
00:00:31,840 –> 00:00:33,240
That’s not right.
11
00:00:34,720 –> 00:00:36,440
– Look’s like you’ve got a bug.
12
00:00:36,440 –> 00:00:38,280
Let me get it for you.
13
00:00:39,440 –> 00:00:41,160
– Who are you?
14
00:00:41,160 –> 00:00:44,200
What are you doing in my computer?
15
00:00:44,200 –> 00:00:47,160
Ah no, I think I’ve got another virus.
16
00:00:47,160 –> 00:00:49,720
Hey Marge, those kids
have been downloading
17
00:00:49,720 –> 00:00:53,360
virus-infected Minecraft mods again.
18
00:00:53,360 –> 00:00:54,840
– Will you relax?
19
00:00:54,840 –> 00:00:58,40
I’m not a virus, I don’t
want your credit card,
20
00:00:58,40 –> 00:00:59,800
I don’t want your bank account,
21
00:00:59,800 –> 00:01:01,320
I’m here to help.
22
00:01:01,320 –> 00:01:03,200
Look, people put up for years
23
00:01:03,200 –> 00:01:06,80
with a bouncing talking
paperclip didn’t they?
24
00:01:06,80 –> 00:01:08,160
– Will you get out of here?
25
00:01:08,160 –> 00:01:10,200
Look, I can fix this myself.
26
00:01:10,200 –> 00:01:13,440
I’ll just add them together
using lots of plusses.
27
00:01:13,440 –> 00:01:15,520
– It’s not going to work.
28
00:01:19,640 –> 00:01:23,560
– [Narrator] Copy here,
equals this, plus this,
29
00:01:26,560 –> 00:01:27,920
plus this, bang.
30
00:01:30,880 –> 00:01:31,720
What?
31
00:01:32,640 –> 00:01:34,80
It’s still wrong.
32
00:01:36,240 –> 00:01:39,240
So what is minus 2.83107E-15 anyway?
33
00:01:42,760 –> 00:01:44,440
I mean, is that even a number?
34
00:01:44,440 –> 00:01:47,520
Minus 2.8E-15 is scientific notation.
35
00:01:48,720 –> 00:01:51,560
It means negative decimal point
36
00:01:51,560 –> 00:01:54,840
followed by 14 zeros, two eight.
37
00:01:54,840 –> 00:01:57,840
Okay, but that still doesn’t
explain why it’s wrong.
38
00:01:57,840 –> 00:02:01,600
Numbers are firstly converted
into binary representations
39
00:02:01,600 –> 00:02:04,0
made up of ones and zeroes.
40
00:02:04,0 –> 00:02:06,720
Unfortunately for decimal numbers,
41
00:02:06,720 –> 00:02:10,960
these binary representations
are not always accurate.
42
00:02:10,960 –> 00:02:13,280
So when the binary ones and zeroes
43
00:02:13,280 –> 00:02:15,960
are converted back to decimal,
44
00:02:15,960 –> 00:02:17,800
small errors creep in.
45
00:02:18,800 –> 00:02:22,680
Adding decimals together
can make it even worse.
46
00:02:22,680 –> 00:02:24,680
This is all done according to
47
00:02:24,680 –> 00:02:28,40
a rather ancient electrical standard,
48
00:02:28,40 –> 00:02:30,880
Not that old, more like 1975,
49
00:02:30,880 –> 00:02:32,240
called IEEE 754.
50
00:02:34,240 –> 00:02:37,0
– Now here is where it
gets really interesting,
51
00:02:37,0 –> 00:02:39,760
try that calculation again but this time,
52
00:02:39,760 –> 00:02:43,200
put the negative number on the bottom
53
00:02:43,200 –> 00:02:44,880
and see how it goes.
54
00:02:50,440 –> 00:02:53,40
See, I told you I could help.
55
00:02:53,40 –> 00:02:55,560
– [Narrator] So all I have to
do is put the negative figures
56
00:02:55,560 –> 00:02:57,160
at the bottom of my list.
57
00:02:57,160 –> 00:02:59,160
– No, we just got lucky.
58
00:03:00,40 –> 00:03:02,320
– So what does Microsoft say?
59
00:03:02,320 –> 00:03:04,360
Are they doing anything about it?
60
00:03:04,360 –> 00:03:08,40
– Keep in mind this error
has been in Microsoft Excel
61
00:03:08,40 –> 00:03:10,40
since version 1.0,
62
00:03:10,40 –> 00:03:11,800
that’s over 30 years.
63
00:03:12,800 –> 00:03:14,920
According to Microsoft’s website
64
00:03:14,920 –> 00:03:17,760
they implement IEEE 754 correctly.
65
00:03:20,560 –> 00:03:22,760
They note the limitations
66
00:03:22,760 –> 00:03:25,760
but they recommend going into ‘Options’
67
00:03:25,760 –> 00:03:26,560
‘Advanced’
68
00:03:27,640 –> 00:03:30,200
scroll all the way to the bottom
69
00:03:30,200 –> 00:03:33,600
and click on ‘Set precision as displayed’
70
00:03:34,520 –> 00:03:36,480
but guess what Microsoft?
71
00:03:36,480 –> 00:03:37,800
It doesn’t work.
72
00:03:39,80 –> 00:03:41,160
– [Narrator] In primary
school, children are taught
73
00:03:41,160 –> 00:03:44,440
that when adding or subtracting
numbers with decimals,
74
00:03:44,440 –> 00:03:48,0
the maximum number of
decimal places in the answer
75
00:03:48,0 –> 00:03:52,160
can only be the maximum
number of decimal places
76
00:03:52,160 –> 00:03:55,80
in any of the numbers in the sum.
77
00:03:55,80 –> 00:03:59,760
– The specification IEEE
754 is seriously lacking
78
00:03:59,760 –> 00:04:01,600
if it does not perform this check
79
00:04:01,600 –> 00:04:03,280
at the end of a calculation.
80
00:04:03,280 –> 00:04:07,160
Especially if it expects
to have rounding errors.
81
00:04:07,160 –> 00:04:09,600
When IEEE 754 was created,
82
00:04:09,600 –> 00:04:12,960
computers were a lot
slower than they are today.
83
00:04:12,960 –> 00:04:15,280
They had a lot fewer cycles.
84
00:04:16,480 –> 00:04:18,880
– [Narrator] Not those kind of cycles.
85
00:04:18,880 –> 00:04:21,40
Computer processor cycles.
86
00:04:22,120 –> 00:04:26,560
No that’s an analog cycle,
we want a digital cycle.
87
00:04:26,560 –> 00:04:28,40
That’s the one.
88
00:04:28,40 –> 00:04:29,800
Much faster.
89
00:04:29,800 –> 00:04:30,880
Faster still.
90
00:04:33,440 –> 00:04:36,920
– Anyway, there were so
many spare CPU cycles
91
00:04:36,920 –> 00:04:38,320
that they could have used them
92
00:04:38,320 –> 00:04:41,160
to calculate the correct
number of decimal places,
93
00:04:41,160 –> 00:04:44,760
but instead of doing that
they created Mr Clippy,
94
00:04:44,760 –> 00:04:47,240
an idea they later killed off.
95
00:04:48,160 –> 00:04:50,640
– They killed Mr Clippy?
96
00:04:50,640 –> 00:04:54,920
– Ah sweetie, he was very old
and he lived a happy life.
97
00:04:54,920 –> 00:04:57,120
Here’s Mrs Clippy, go play with her.
98
00:04:57,120 –> 00:04:58,0
– Yay!
99
00:04:58,0 –> 00:04:59,360
– And here’s some Clippettes.
100
00:04:59,360 –> 00:05:00,200
– Oh boy!
101
00:05:02,880 –> 00:05:05,0
– Now, where was I?
102
00:05:05,0 –> 00:05:06,400
Oh yes.
103
00:05:06,400 –> 00:05:08,600
Today’s computers are so fast
104
00:05:08,600 –> 00:05:11,480
that Microsoft could
perform the calculation
105
00:05:11,480 –> 00:05:13,40
according to the standard
106
00:05:13,40 –> 00:05:14,840
but then check the precision
107
00:05:14,840 –> 00:05:18,160
to the correct number of decimal places
108
00:05:18,160 –> 00:05:21,160
which is exactly what Google do.
109
00:05:21,160 –> 00:05:22,560
– [Narrator] That’s right,
110
00:05:22,560 –> 00:05:26,160
the error doesn’t happen in Google Sheets.
111
00:05:26,160 –> 00:05:29,0
I guess Google don’t let
standards get in the way
112
00:05:29,0 –> 00:05:30,440
of a correct answer.
113
00:05:30,440 –> 00:05:33,80
Yeah, and they also don’t
have that mongrel ribbon.
114
00:05:33,80 –> 00:05:34,960
So isn’t there anything I can do?
115
00:05:34,960 –> 00:05:36,240
– Yes there is,
116
00:05:36,240 –> 00:05:40,680
please share this video with
your friends and colleagues.
117
00:05:40,680 –> 00:05:42,240
If we can get enough support,
118
00:05:42,240 –> 00:05:46,40
maybe Microsoft will
finally fix this problem.
119
00:05:46,40 –> 00:05:48,480
Oh and please like, or subscribe,
120
00:05:48,480 –> 00:05:51,800
so that you can get more
informative and entertaining videos
121
00:05:51,800 –> 00:05:53,680
just like this one.
122
00:05:53,680 –> 00:05:54,720
Thanks for watching.
123
00:05:54,720 –> 00:05:57,480
(relaxing music)
124
00:06:14,840 –> 00:06:15,760
Hi sweetie.
125
00:06:17,200 –> 00:06:18,760
What are you doing?
126
00:06:20,320 –> 00:06:22,320
Don’t click that!
127
00:06:22,320 –> 00:06:24,880
– [Daughter] You’re bad, Daddy.
128
00:06:29,200 –> 00:06:31,0
– Not Comic Sans!
129
00:06:31,0 –> 00:06:33,800
– [Daughter] You’re funny, Daddy.
130
00:06:33,800 –> 00:06:35,800
Now you’re a good Daddy.
