WEBVTT

0
00:02.500 --> 00:07.630
Okay guys this lecture is the result of something that I learned only yesterday.

1
00:07.630 --> 00:13.780
Now, I've been using Excel for 20 something years and I've never even seen this function before but it's

2
00:13.780 --> 00:14.960
so useful...

3
00:15.050 --> 00:21.600
I had to squeeze it into the curriculum just so that you're aware of it, and it will really help to cement

4
00:21.670 --> 00:25.210
what we've actually just done on the order of mathematical operation.

5
00:25.450 --> 00:25.880
OK.

6
00:26.000 --> 00:26.890
Here goes.

7
00:26.890 --> 00:31.840
Now, what we're going to use is a tool that I only discovered yesterday.

8
00:32.140 --> 00:34.780
And you'll find this in the formulas ribbon.

9
00:35.020 --> 00:40.450
So if you come up here to "Formulas", just left click and this ribbon will open up.

10
00:40.450 --> 00:46.480
Now the formula we looked at in the order of mathematical operation was equals three plus three divided

11
00:46.480 --> 00:52.750
by two... okay, so all we're going to do is enter that formula in our cell and then we're going to come

12
00:52.750 --> 00:57.500
up to the "Formula Auditing" section of the formulas ribbon.

13
00:57.510 --> 00:59.650
And we're going to click "Evaluate Formula".

14
00:59.650 --> 01:06.340
Now, let me just drag this into shot so I can zoom in and you will see clearly in this little

15
01:06.340 --> 01:08.080
window appears here.

16
01:08.200 --> 01:16.570
And what this does is, it will allow Excel to walk through, step by step, every single part of your formula

17
01:16.900 --> 01:23.400
so you can see that our formula, up here in the formula bar says = 3 + 3 / 2

18
01:23.530 --> 01:30.490
And if you look in the evaluation section of the Evaluate formula box you can see that instantly Excel

19
01:30.490 --> 01:36.970
has underlined which part it's going to do first...so you can see that it's going to ignore the three

20
01:36.970 --> 01:41.150
plus for the moment, and it's underlined three divided by two.

21
01:41.200 --> 01:46.840
So now you can see instantly that that's the first part of our formula that's going to be executed.

22
01:46.840 --> 01:52.270
So let's go ahead and click the "Evaluate"...I'm just going to left click there, and now you can see that it's

23
01:52.270 --> 01:58.170
changed the 3 / 2 to 1.5, because it's completed that part of the formula.

24
01:58.270 --> 02:02.950
And now we can see what it's left with is 3 + 1.5

25
02:02.950 --> 02:08.770
So if we click again, there you go...our answer's going to be 4.5.
Hit restart and it will go through

26
02:08.770 --> 02:09.700
it again.

27
02:10.760 --> 02:11.090
OK.

28
02:11.120 --> 02:16.460
Now that's one way of doing it when we just use the values...now what we want to do now rather than using

29
02:16.550 --> 02:22.520
a number in the cell, we're going to type our values into these cells here, and then we're going to write

30
02:22.520 --> 02:24.890
a formula that references those.

31
02:24.950 --> 02:29.220
Now all that means is we're going to click the cell rather than type the value in the formula.

32
02:29.300 --> 02:32.540
So we're going to type "=", to tell Excel it's a formula.

33
02:32.780 --> 02:37.760
We're going to click that plus that divided by.

34
02:38.840 --> 02:47.030
That...I nearly pressed 2 then!  That plus that divided by that... hit "Enter" reselect the cell and hit "Evaluate

35
02:47.030 --> 02:48.440
Formula".

36
02:48.500 --> 02:50.720
Then we just move that down a little bit.

37
02:51.020 --> 02:51.430
OK.

38
02:51.450 --> 02:57.920
Now, let's quickly go through this...Right, okay, now because we didn't type our value into formulas such

39
02:57.920 --> 03:04.020
as 3 + 3 / 2, and we used the cell references...the very first thing that Excel needs

40
03:04.020 --> 03:08.850
to do is go to those cells and actually extract what the value is.

41
03:08.960 --> 03:14.100
So at the moment our formula says A1 plus A2 divided by A3

42
03:14.120 --> 03:20.090
Now we've got new function that you can see in here which is called "Step In"...and you can see that A1

43
03:20.180 --> 03:21.170
is underlined.

44
03:21.160 --> 03:25.660
So the first thing Excel's going to do is retrieve the value from A1.

45
03:25.730 --> 03:33.950
And if we hit "Step In", you can see that A1 has been highlighted and it's been selected on our spreadsheet

46
03:34.430 --> 03:38.330
and it says that the value in A1 is 3.

47
03:38.440 --> 03:38.690
OK.

48
03:38.720 --> 03:40.580
So step out...if you want to step in, 

49
03:40.580 --> 03:41.420
that's what it does.

50
03:41.420 --> 03:42.140
It will select the cell

51
03:42.150 --> 03:44.060
it's just about to look at.

52
03:44.240 --> 03:47.210
Now you can see there that previously we had A1...

53
03:47.260 --> 03:50.200
And now it says 3, because it's retrieved the value.

54
03:50.400 --> 03:52.380
Now it is going to take a look at A2

55
03:52.400 --> 03:57.640
So when we hit "Evaluate", it will say in A2 have the value of 3.

56
03:57.800 --> 04:01.240
And finally, it's going to have a look at A3 to see what values are there.

57
04:01.250 --> 04:07.470
So when we click "Evaluate", it will say 2...and then it's going to say, "Okay, I've retrieved all my values...

58
04:07.490 --> 04:09.650
Now I'm going to work on the formula itself".

59
04:09.770 --> 04:13.900
And again, just as it did before, it's going to look at the division part first.

60
04:13.910 --> 04:17.290
So it's underlining 3 / 2

61
04:17.540 --> 04:23.270
And when we click "Evaluate" it will calculate that, and say 3 / 2 is 1.5, and then

62
04:23.300 --> 04:27.140
the final part of my formula is to add 3 to that answer.

63
04:27.170 --> 04:30.890
So when we hit "Evaluate", there you go...4.5.

64
04:30.890 --> 04:31.670
That's how we get it.

65
04:31.690 --> 04:36.650
And if you want to go through it again, hit "Restart", and just click your left mouse button on this button...

66
04:36.710 --> 04:39.020
and it will go all the way through.

67
04:39.350 --> 04:45.750
And there you go..."Evaluate Formula"...very useful, and I've missed it for as long as it's been in Excel.

68
04:45.770 --> 04:47.390
But there you go, now you know about it,

69
04:47.390 --> 04:48.100
Enjoy.

70
04:48.200 --> 04:49.560
See you in the next lecture.