Microsoft Excel Addition Bug Workaround

Here is part 2 in my series about the Microsoft Excel Addition Bug. I demonstrate 2 ways of working around the problem.

Please watch the Microsoft Excel Bug video first so that you know what the heck I am talking about.

 Transcript of Microsoft Excel Addition Bug Workaround

1
00:00:00,972 –> 00:00:02,483
– Good day, I’m Jay Burleigh

2
00:00:02,483 –> 00:00:04,625
and have I got a workaround for you.

3
00:00:04,625 –> 00:00:07,625
(slow techno music)

4
00:00:13,826 –> 00:00:17,071
Hi, I’m Jay Burleigh
from the Excel bug video.

5
00:00:17,071 –> 00:00:19,465
What, you haven’t seen it yet?

6
00:00:19,465 –> 00:00:22,500
Stop, go back, and have
a look at it first.

7
00:00:22,500 –> 00:00:23,792
You’ll be glad you did.

8
00:00:23,792 –> 00:00:26,609
In the last video, I looked at an error

9
00:00:26,609 –> 00:00:30,969
that had been in Microsoft
Excel for the 30 years.

10
00:00:30,969 –> 00:00:34,373
I’ve had heaps of
comments about that video

11
00:00:34,373 –> 00:00:38,979
and lots of people have
suggested ways to work around it,

12
00:00:38,979 –> 00:00:42,035
but they’re all wrong and
I’m gonna show you why.

13
00:00:42,035 –> 00:00:44,853
So the most frequent comment
I got on the last video

14
00:00:44,853 –> 00:00:47,683
was simply format the cell

15
00:00:47,683 –> 00:00:49,796
and if we do that everything looks great

16
00:00:49,796 –> 00:00:53,171
until we perform a calculation on it

17
00:00:53,171 –> 00:00:56,042
and you can see immediately
the error’s back.

18
00:00:56,042 –> 00:00:57,803
So the next most frequent comment

19
00:00:57,803 –> 00:01:00,820
was format the entire spreadsheet

20
00:01:00,820 –> 00:01:04,864
and when we do that
everything looks hunky-dory,

21
00:01:04,864 –> 00:01:09,618
up until we do some accounting
type calculations on it,

22
00:01:09,618 –> 00:01:12,429
such as balancing two columns of figures,

23
00:01:12,429 –> 00:01:14,497
which is quite common.

24
00:01:14,497 –> 00:01:16,780
So what we’ll do is in the second column

25
00:01:16,780 –> 00:01:19,162
we’re gonna put the same figures back in,

26
00:01:19,162 –> 00:01:20,849
but in a different order

27
00:01:20,849 –> 00:01:24,794
and then we’ll put a sum
underneath them to add them up.

28
00:01:24,794 –> 00:01:27,250
And lo and behold they look identical,

29
00:01:27,250 –> 00:01:31,520
but what we’ll do is we’ll
put in an if calculation

30
00:01:31,520 –> 00:01:33,046
just to compare the two numbers

31
00:01:33,046 –> 00:01:36,747
to see if in fact they are identical.

32
00:01:36,747 –> 00:01:39,830
So, our little if statement will say,

33
00:01:41,707 –> 00:01:46,068
if the two numbers are the
same, show the word balanced

34
00:01:46,068 –> 00:01:50,054
and if they’re not, show not balanced.

35
00:01:50,054 –> 00:01:54,129
And lo and behold, it says, not balanced.

36
00:01:54,129 –> 00:01:57,594
The real way to fix the
problem, is quite simply,

37
00:01:57,594 –> 00:02:01,927
to wrap a round statement
around every sum in excel.

38
00:02:03,730 –> 00:02:08,613
So in this case, we’ll put
in equals round, bracket,

39
00:02:08,613 –> 00:02:11,542
and then at the end of the
sum we’ll put in comma two

40
00:02:11,542 –> 00:02:15,082
for two decimal places
and close the bracket off.

41
00:02:15,082 –> 00:02:17,255
And that appears to have worked.

42
00:02:17,255 –> 00:02:18,985
Now, to be entirely correct,

43
00:02:18,985 –> 00:02:20,703
we need to have it on both sums,

44
00:02:20,703 –> 00:02:24,786
so we’ll just drag it
across and that fixed that,

45
00:02:26,505 –> 00:02:28,109
so that’s one way of fixing the problem,

46
00:02:28,109 –> 00:02:32,009
there is another way and it’s way quicker.

47
00:02:32,009 –> 00:02:35,362
So we’ll just undo the
changes we’ve made there.

48
00:02:35,362 –> 00:02:39,444
And now we’ve formatted
the entire spreadsheet.

49
00:02:39,444 –> 00:02:43,027
So now what we’re going to
do is simply go in to file,

50
00:02:43,027 –> 00:02:47,975
options, advanced, scroll
all the way to the bottom,

51
00:02:47,975 –> 00:02:51,292
and click set precision as displayed.

52
00:02:51,292 –> 00:02:55,789
And we when click ok, it’s
all fixed and correct.

53
00:02:55,789 –> 00:02:58,463
Now you’re probably
watching this video thinking

54
00:02:58,463 –> 00:03:00,457
why do I need to go to all that trouble

55
00:03:00,457 –> 00:03:02,554
when I know what scientific notation is

56
00:03:02,554 –> 00:03:06,258
and if it’s a problem, I
can just format it anyway.

57
00:03:06,258 –> 00:03:09,492
Well, that’s well and good
if it’s just you using it,

58
00:03:09,492 –> 00:03:12,730
but so many times I get
called out to a situation

59
00:03:12,730 –> 00:03:16,668
where some I.T. guru has
created a spreadsheet

60
00:03:16,668 –> 00:03:18,809
for some poor staff member to use

61
00:03:18,809 –> 00:03:21,426
and they’re completely confused

62
00:03:21,426 –> 00:03:23,145
they don’t know why it doesn’t work

63
00:03:23,145 –> 00:03:25,453
and before you know it
they’re off on stress leave

64
00:03:25,453 –> 00:03:27,329
because they’re being forced to use

65
00:03:27,329 –> 00:03:29,245
this thing that’s just useless.

66
00:03:29,245 –> 00:03:31,007
So in my professional experience

67
00:03:31,007 –> 00:03:33,384
it’s better to get it right, first time.

68
00:03:33,384 –> 00:03:36,583
So there you have two
ways to solve the problem

69
00:03:36,583 –> 00:03:40,932
to work around it till
Microsoft finally fix the bug.

70
00:03:40,932 –> 00:03:42,074
Thank you for watching.

71
00:03:42,074 –> 00:03:45,921
I hope you’ve enjoyed
this video, if you have,

72
00:03:45,921 –> 00:03:48,944
please share it with your
friends and colleagues,

73
00:03:48,944 –> 00:03:51,515
like and subscribe, and I’ll
see you in the next video.

74
00:03:51,515 –> 00:03:53,163
Thanks for watching.

75
00:03:53,163 –> 00:03:56,163
(slow techno music)

76
00:04:00,633 –> 00:04:02,248
Good day, I’m Jay Burleigh,

77
00:04:02,248 –> 00:04:04,644
have I got a workaround for you.

78
00:04:04,644 –> 00:04:07,644
(slow techno music)

79
00:04:16,433 –> 00:04:18,327
I’m not doing that again.

80
00:04:18,327 –> 00:04:21,327
(slow techno music)