1
00:00:00,810 --> 00:00:05,720
Let's talk about how Excel runs calculations and in which order it does it.

2
00:00:05,730 --> 00:00:10,290
It's really important to understand this if you want to make sure you get the right numbers.

3
00:00:10,410 --> 00:00:11,700
So let's do a quick test.

4
00:00:12,240 --> 00:00:12,780
What do you think

5
00:00:12,780 --> 00:00:17,040
The answer of this is? It's 12.

6
00:00:17,050 --> 00:00:21,080
What about this one? It's 10.

7
00:00:21,190 --> 00:00:23,520
2+1 is calculated first.

8
00:00:23,530 --> 00:00:30,520
The answer is multiplied by 3 and that answer is added to 1. Understanding the order in which

9
00:00:30,520 --> 00:00:33,470
calculations happen is key here.

10
00:00:33,760 --> 00:00:35,080
Let's go through them.

11
00:00:35,080 --> 00:00:42,040
The number one type of calculation that's run is any values that are in parenthesis or use reference operators.

12
00:00:42,070 --> 00:00:44,470
By reference operators,

13
00:00:44,470 --> 00:00:49,420
I mean the colon and the comma for example what we're used to seeing in the sum function.

14
00:00:49,420 --> 00:00:57,400
Everything there occurs first and is calculated first before any other calculations occur.

15
00:00:57,400 --> 00:01:04,160
Number two is negation. Then comes percentage, and then exponentiation.

16
00:01:04,180 --> 00:01:07,730
After that we get multiplication and division.

17
00:01:07,750 --> 00:01:14,800
So notice here in this example, we don't multiply first but we do two to the power of two first and then

18
00:01:14,800 --> 00:01:17,160
the result is multiplied by two.

19
00:01:17,230 --> 00:01:22,050
In the case of multiplication and division, if both exist in the formula

20
00:01:22,270 --> 00:01:25,770
Excel evaluates from left to right. After this

21
00:01:25,780 --> 00:01:27,990
we get addition and subtraction.

22
00:01:28,030 --> 00:01:33,400
So if we have a formula like this, the first thing that's going to calculate is the 3+1 because

23
00:01:33,400 --> 00:01:35,050
that's in brackets.

24
00:01:35,080 --> 00:01:37,790
After that the multiplication is going to happen.

25
00:01:37,900 --> 00:01:40,120
And after that the addition is going to happen.

26
00:01:40,120 --> 00:01:44,790
So just remember it's MBA: multiplication before addition.

27
00:01:44,980 --> 00:01:47,430
Number seven is comparison operators.

28
00:01:47,430 --> 00:01:53,620
So for example, if we have 1+2 equals 1+2 it's not just going to check the 2 with

29
00:01:53,620 --> 00:01:59,830
the 1. It's first going to run the addition because that occurs before comparing and then it's going

30
00:01:59,830 --> 00:02:01,690
to run the comparison.

31
00:02:01,690 --> 00:02:04,290
So, in this case the result would be true.

32
00:02:04,300 --> 00:02:07,670
Let's jump to Excel and do some examples.

33
00:02:07,780 --> 00:02:14,290
I'm in the start file for Section 5 in the first tab here you can test your knowledge before you see

34
00:02:14,290 --> 00:02:15,460
the results.

35
00:02:15,460 --> 00:02:23,380
So actually what I suggest you do is to pause this video and try to come up with the right answers and

36
00:02:23,380 --> 00:02:27,950
then come back to the video and let's cross-check your answers.

37
00:02:28,030 --> 00:02:32,180
Just one note here where I have SUM(C3:C4)

38
00:02:32,200 --> 00:02:36,550
these are actually the numbers that are used in these formulas here.

39
00:02:36,550 --> 00:02:37,880
OK so have a go at it.

40
00:02:37,900 --> 00:02:40,920
Press pause and then come back.

41
00:02:40,960 --> 00:02:42,100
Now we're back.

42
00:02:42,100 --> 00:02:43,580
How did you do?

43
00:02:43,600 --> 00:02:45,880
How many did you get right?

44
00:02:45,880 --> 00:02:48,760
Let's take a closer look at these ones.

45
00:02:48,820 --> 00:02:51,730
These are using our comparison operators, right?

46
00:02:51,730 --> 00:02:57,180
We said that the reference operators in the brackets they happen before comparing.

47
00:02:57,190 --> 00:03:05,560
So here and in here we get both false because we are summing B3 to B4 and comparing it to

48
00:03:05,830 --> 00:03:07,430
C3 to C4.

49
00:03:07,480 --> 00:03:08,700
They're not equal.

50
00:03:08,740 --> 00:03:13,560
So we get false and it's not greater than we get false. It's actually less than

51
00:03:13,560 --> 00:03:14,790
so we get true.

52
00:03:14,980 --> 00:03:22,540
But check this out. If you ever want to get advanced in Excel this is great to know. What happens if I

53
00:03:22,540 --> 00:03:27,310
take a false and I add it to a true.

54
00:03:27,310 --> 00:03:36,200
What do you think I'm going to get? A 1. The moment Excel runs any mathematical operation on true and false

55
00:03:36,200 --> 00:03:42,090
values it translates a false to a zero and a true to a 1.

56
00:03:42,140 --> 00:03:50,440
So what happens if I multiply a false with a true? I get a zero because it becomes zero

57
00:03:50,450 --> 00:03:51,990
multipled by 1.

58
00:03:51,990 --> 00:03:53,390
It's a zero.

59
00:03:53,390 --> 00:03:57,260
What happens if I add a true to a true?

60
00:03:57,260 --> 00:03:59,630
I get two. A one plus a one

61
00:03:59,660 --> 00:04:00,690
is a two.

62
00:04:00,720 --> 00:04:06,260
This is something to keep in mind if you ever want to become advanced in Excel. You're going to be

63
00:04:06,260 --> 00:04:11,090
working a lot with comparison operators inside Excel functions.

64
00:04:11,120 --> 00:04:13,010
It's going to save you a lot of time

65
00:04:13,100 --> 00:04:17,720
If you understand this concept. Try to do these in your head

66
00:04:18,110 --> 00:04:21,589
and now let's cross-check results.

67
00:04:21,620 --> 00:04:23,390
These are the values that we get.

68
00:04:27,660 --> 00:04:29,500
I have a bonus tip for you here.

69
00:04:29,500 --> 00:04:36,160
If you're wondering how I get the formulas here in a separate cell. I'm actually using a function called

70
00:04:36,160 --> 00:04:37,660
formula text.

71
00:04:37,660 --> 00:04:43,540
Excel has a lot of great functions and we're going to take a look at some important basic functions

72
00:04:43,570 --> 00:04:50,710
in the next sections. But this comes in really handy if you want to show your formulas in separate cells

73
00:04:50,830 --> 00:04:52,150
as well as your numbers.

74
00:04:52,180 --> 00:04:58,660
If you're giving a training or you're discussing a formula you can use formula text just reference the

75
00:04:58,660 --> 00:05:05,000
cell in which you have your formula and press enter and you get that formula typed here and and it's live.

76
00:05:05,000 --> 00:05:07,900
The moment you change something in your formula.

77
00:05:07,960 --> 00:05:11,920
Multiply this with eight you can see it reflected in the cell as well.

78
00:05:12,310 --> 00:05:14,210
OK, so that was the bonus tip.

79
00:05:14,290 --> 00:05:18,130
Let's move on to looking at simple formulas in Excel.