WEBVTT

0
00:02.820 --> 00:07.740
Okay guys. Before we get to the next lecture, we're just going to do a quick one, and it's going to be a primer

1
00:07.770 --> 00:10.620
on how to build a complex formula.

2
00:10.650 --> 00:11.510
All right.

3
00:11.520 --> 00:15.490
Two simple formulas and then squash them together.

4
00:15.570 --> 00:15.920
Okay.

5
00:15.920 --> 00:22.620
Let's crack on! The simplest way to work out how to build a complex formula is to use what you already

6
00:22.620 --> 00:23.390
have.

7
00:23.400 --> 00:27.850
Now this is just a screen cap of the spreadsheet that we're already building.

8
00:28.280 --> 00:36.000
And if we look at our average sales in cell L5 we can see that we have the formula of "=",  which tells

9
00:36.000 --> 00:42.140
Excel is a formula...I5, because it's looking at our total of sales for that line.

10
00:42.450 --> 00:50.080
And then over 1 in column M5, we wrote our "Round" formula, which was =ROUND(

11
00:50.250 --> 00:56.580
Then we referenced L5, comma 1, to show that it's to one decimal place, and then we closed off our brackets

12
00:56.580 --> 00:58.680
to encapsulate the arguments.

13
00:58.680 --> 01:05.670
Hit "Enter", and there you go. We're all good so far. Right, now this is what we have, this is just cut down view.

14
01:05.710 --> 01:13.740
Now the thing to remember in this formula here on the right is that where we said Round L5, L5 is just

15
01:13.740 --> 01:18.430
using the value that's been calculated in the cell of L5.

16
01:18.660 --> 01:24.240
So it's not L5 itself, it's the value that's been calculated in L5.

17
01:24.410 --> 01:31.080
Now you'll remember from an earlier lecture that we use the phrase "Bless My Dear Aunt Sally", which gave

18
01:31.080 --> 01:34.020
us the order of mathematical operations.

19
01:34.020 --> 01:40.010
So whenever you're in doubt when you're building a complex formula, always bracket off your formulas

20
01:40.080 --> 01:43.950
it's not going to do any harm, and it just makes them a little easier to read.

21
01:43.950 --> 01:50.970
So what we're going to do first, is we're going to bracket off formula in L5...at the moment it says =

22
01:51.210 --> 01:52.980
5/6

23
01:53.160 --> 01:58.300
And we're going to insert a bracket before the I5 and after the 6.

24
01:58.350 --> 02:03.150
So it's going to look something like this...and you can see down here in the bottom corner, that we've

25
02:03.150 --> 02:05.230
now got that formula just there.

26
02:05.400 --> 02:06.300
Now remember.

27
02:06.300 --> 02:08.440
Bless My Dear Aunt Sally.

28
02:08.670 --> 02:14.570
So in this video we're going to have a look at the round formula that's in cell M5...

29
02:14.700 --> 02:19.910
And what we're going to do to make this a perfect formula, is we're going to scudgy this part over...

30
02:19.910 --> 02:21.580
just a little bit.

31
02:21.630 --> 02:24.480
We're going to take the L5 reference...

32
02:24.480 --> 02:30.120
We're going to get rid of it and we're going to take the formula from the other cell and we're going

33
02:30.120 --> 02:31.520
to drop it in there.

34
02:31.620 --> 02:32.400
Okay.

35
02:32.400 --> 02:36.540
So now we have a perfectly built complex formula.

36
02:36.540 --> 02:42.400
We no longer reference in the cell, were actually calculating the whole thing all in one formula.

37
02:42.660 --> 02:46.740
And that, my friends, is how you build a complex formula.

38
02:46.890 --> 02:47.710
If you're ever in doubt...

39
02:47.730 --> 02:55.770
just remember the phrase "Bless My Dear Aunt Sally".
Always do it simply first, so if you can work out what

40
02:55.770 --> 02:56.930
your formula should do...

41
02:56.940 --> 03:00.650
...but you need to take a couple of steps to do it, and that's perfectly fine.

42
03:00.810 --> 03:05.490
And then when she worked out how to do it simply...squash them together.

43
03:05.700 --> 03:07.620
Okay see you in the next lecture.