WEBVTT

0
00:03.310 --> 00:09.280
Okay guys, we're going to have a little break from the screencasts, to have a look at the order of mathematical

1
00:09.280 --> 00:10.420
operation.

2
00:10.420 --> 00:14.200
Now you may remember this from your school days depending on how old you are.

3
00:14.200 --> 00:18.420
I know I'm certainly old, but it's critical to your success in Excel.

4
00:18.460 --> 00:23.740
So let's have a look at the order of mathematical operation or is I sometimes like to call it "when

5
00:23.740 --> 00:26.700
your formula doesn't do what you thought it would do".

6
00:27.070 --> 00:28.910
Okay let's move on.

7
00:28.990 --> 00:30.580
Now, when using Excel,

8
00:30.580 --> 00:33.910
we are at some point going to use complex formulas.

9
00:33.910 --> 00:39.400
Now we don't need to worry about complex formulas, because complex doesn't necessarily mean that they are

10
00:39.400 --> 00:47.050
complicated.  All complex formula is, is a formula that has more than one mathematical operator.

11
00:47.050 --> 00:52.720
Now, don't be worried by the term "mathematical operator" either, because you probably already know most

12
00:52.720 --> 01:01.600
of the common ones. They are "+" for addition, "-" for subtraction...multiplication and...division.

13
01:01.730 --> 01:03.720
Okay so far so good.

14
01:03.760 --> 01:04.870
Let's move on.

15
01:05.810 --> 01:08.980
First let's go over how you think it would work.

16
01:08.990 --> 01:11.910
So far we've created simple formulas.

17
01:11.920 --> 01:13.260
We've done basic addition.

18
01:13.280 --> 01:18.360
We've done basic multiplication, basic subtraction, and basic division.

19
01:18.450 --> 01:22.330
Now at the moment, even when we did it the old fashioned way and we did, this...

20
01:22.340 --> 01:25.250
..plus this plus this plus this.

21
01:25.250 --> 01:31.720
It was fine, and we got the answer we expected because we only used a single operator in the formula. Whether

22
01:31.730 --> 01:37.000
we use that same operator over and over again makes no difference, because you will always get the answer

23
01:37.000 --> 01:37.870
that you want.

24
01:38.090 --> 01:45.230
So one plus one will always equal two, and one plus one plus one plus one plus one will always equal

25
01:45.230 --> 01:45.990
five.

26
01:46.030 --> 01:50.140
And this is because we used only one mathematical operator.

27
01:50.290 --> 01:59.060
But if we were to say three plus three divided by two and we wrote the formula as we "said" it, that is

28
01:59.150 --> 02:03.680
equals (because it's a formula) 3 + 3 / 2

29
02:03.860 --> 02:10.700
We may think that we already know that the answer should be 3 but in Excel it isn't the actual answer

30
02:10.700 --> 02:12.610
would be 4.5.

31
02:12.650 --> 02:14.120
If that just blew your mind, 

32
02:14.120 --> 02:18.060
don't worry,  because we're about have a look at how it actually does work in Excel.

33
02:19.320 --> 02:26.900
Now, since mathematics began, mathematical operations have always been executed in the same order.

34
02:27.140 --> 02:30.800
Anything in brackets is always done first.

35
02:30.800 --> 02:34.710
Next comes any multiplication part of the formula.

36
02:35.090 --> 02:38.740
After that comes any division part of the formula.

37
02:38.850 --> 02:40.910
Fourth we do any addition.

38
02:40.910 --> 02:42.550
Parts of the formula.

39
02:42.730 --> 02:46.360
And finally we do any subtraction parts of the formula.

40
02:46.550 --> 02:48.990
Now, if you think that's going to be difficult to remember,

41
02:49.010 --> 02:56.120
Just remember the phrase "Bless My Dear Aunt Sally", and you'll notice that that is the order of mathematical

42
02:56.120 --> 02:59.150
operations in the first letter of each of those words.

43
02:59.300 --> 03:01.410
So B would be brackets.

44
03:01.460 --> 03:03.440
M would be multiplication.

45
03:03.440 --> 03:04.830
D would be division.

46
03:04.970 --> 03:06.400
A would be addition.

47
03:06.560 --> 03:08.990
An S would be subtraction.

48
03:08.990 --> 03:10.820
So when in doubt just remember.

49
03:10.820 --> 03:13.270
Bless My Dear Aunt Sally.

50
03:13.750 --> 03:16.390
Okay let's have a look at how it works.

51
03:16.550 --> 03:21.800
Now, when we say a formula like 3 + 3 / 2

52
03:21.860 --> 03:23.780
This is what we do in our head.

53
03:23.810 --> 03:28.330
We first say three plus three, and we work out that that equals six.

54
03:28.340 --> 03:29.900
So far so good.

55
03:29.960 --> 03:35.420
Then we take our six, and we divide it by two, which gives us the answer of three...which is why you

56
03:35.420 --> 03:41.700
may have thought that the answer to the formula we just looked at would be three...but remember...

57
03:41.720 --> 03:43.760
Bless My Dear Aunt Sally.

58
03:43.760 --> 03:51.500
The order of mathematical operation.
Now because the D for division, comes before A for addition in our

59
03:51.560 --> 03:53.670
phrase "Bless My Dear Aunt Sally",

60
03:53.720 --> 03:57.930
We have to adapt our formula so it actually works in that order.

61
03:58.250 --> 04:05.650
So in a formula the division will always be done before the addition because D comes before the A.

62
04:05.840 --> 04:12.170
So the way Excel will actually do it, is it will do the division part first...so what it will say is 3

63
04:12.170 --> 04:16.940
divided by 2...is 1.5, and that's the division out of the way.

64
04:17.180 --> 04:19.850
Then comes the addition part of it.

65
04:19.850 --> 04:26.150
So we already know the answer to 3 divided by 2 is 1.5, which we can see here...and now Excel

66
04:26.150 --> 04:32.940
will execute the addition part of the formula, which is the 3 + our answer...which gives us the answer

67
04:32.940 --> 04:34.390
of 4.5.

68
04:36.240 --> 04:39.770
And that is how Excel will do it every single time.

69
04:39.780 --> 04:42.740
It will always do anything in brackets first.

70
04:42.810 --> 04:48.540
It will then do its multiplication, it will then do its division,  then do it additions...and finally it will

71
04:48.540 --> 04:50.400
do the subtractions.

72
04:50.400 --> 04:57.870
So our formula "3 + 3 / 2", will actually be done as 3 divided by two and then three

73
04:57.870 --> 04:59.300
will be added to the answer.

74
04:59.430 --> 05:02.830
Okay so now let's look at a good habit to get into.

75
05:03.000 --> 05:05.870
Now, when you're planning your formula in your head.

76
05:06.000 --> 05:10.270
It may help you to think about it in terms of brackets first.

77
05:10.510 --> 05:15.780
If we looked at the three plus three in the formula we've just done we would know that that would be

78
05:15.780 --> 05:20.490
calculated first because B comes before everything else.

79
05:20.490 --> 05:26.190
So three plus three would be six, and then, because we had already bracketed that off and it's already

80
05:26.190 --> 05:32.730
been calculated...we then divide the answer by three, because everything comes after the brackets.

81
05:32.820 --> 05:39.670
If in doubt just remember the phrase "Bless My Dear Aunt Sally", so our actual formula

82
05:39.690 --> 05:48.030
in Excel would be = ( 3 + 3 ) / 2...and we see that excel would

83
05:48.030 --> 05:54.600
now work out three plus three give, us our answer of six, and divide that answer by 2, which would give

84
05:54.600 --> 05:55.870
us the answer of three.

85
05:55.950 --> 05:58.120
And that's the way to structure your formulas.

86
05:58.140 --> 06:01.650
So you always get the answer you expect.

87
06:01.660 --> 06:04.260
Now hopefully that's made it a little bit clearer for you.

88
06:04.410 --> 06:09.790
But when in doubt always remember the phrase "Bless My Dear Aunt Sally"

89
06:09.840 --> 06:15.660
So if the formula you've just entered doesn't give you the result that you thought it would, always take

90
06:15.660 --> 06:20.910
a look at it and see if you can insert brackets around the things that you thought will execute.

91
06:20.910 --> 06:27.090
first. Within a few days of writing your own formulas, you'll be an absolute master at this, so don't worry.

92
06:27.090 --> 06:29.000
So when in doubt just remember.

93
06:29.010 --> 06:34.460
"Bless My Dear Aunt Sally", and that gives you the order of mathematical operation.

94
06:34.560 --> 06:35.040
Okay.

95
06:35.100 --> 06:36.520
See you in the next lecture.