WEBVTT

00:02.600 --> 00:08.420
Okay guys time to look at building our first complex formula now at the moment.

00:08.420 --> 00:13.830
We have our average daily sales over here which currently displays at six point eight.

00:13.850 --> 00:19.700
But you'll remember that if we increase the decimal places its actual value is six point eight three

00:19.700 --> 00:22.940
three three and three recurse forever now.

00:22.980 --> 00:29.690
Also we've worked out over here that to round that number actually makes it six point eight because

00:29.690 --> 00:32.610
we've rounded to one decimal place.

00:32.630 --> 00:36.400
Now let's imagine that we actually won a race on this to one decimal place.

00:36.420 --> 00:40.790
That having to have another column where we performed that calculation.

00:40.790 --> 00:46.940
So what we're going to do now is we're going to turn this into a complex formula utilizing the formula

00:46.940 --> 00:51.730
we already written just have here but insert inserting this formula into it.

00:51.920 --> 00:53.570
Okay here goes.

00:53.570 --> 00:58.570
Now you remember from an earlier age and the phrase bless my dear Aunt Sally.

00:58.790 --> 01:00.780
And you'll also hopefully remember that.

01:00.830 --> 01:03.760
That is the order of mathematical operation.

01:03.860 --> 01:11.480
So it's brackets multiplication division addition subtraction not because we know that this formula

01:11.480 --> 01:13.530
already works perfectly well.

01:13.640 --> 01:18.910
What we going to do is we're going to keep that part of the formula just to keep it nice and clean and

01:18.930 --> 01:20.620
a click in the formula bar.

01:20.860 --> 01:25.360
I'm going to insert a bracket before the formula after the calls.

01:26.400 --> 01:32.850
I'm going to close it off at the end with close brackets are now formalises open brackets equals 5 which

01:32.850 --> 01:36.500
is our total divided by 6 and close brackets.

01:36.720 --> 01:39.040
It still has exactly the same result.

01:39.060 --> 01:42.610
All we've done is enclose that formula in brackets.

01:42.710 --> 01:49.410
Now for the clever part what we're going to do is we're going to incorporate this formula into this

01:49.410 --> 01:55.240
formula so we end up with one perfect complex formula that gives us the answer we're looking for.

01:55.530 --> 02:02.320
Okay now just to make this simple what minute do is just can delete the equals and I hit centre.

02:02.490 --> 02:06.170
So no we can actually see what that formula say's.

02:06.210 --> 02:10.300
Now you'll remember that L5 was this cell here.

02:10.500 --> 02:17.100
So this formula looked at this result and then rounded to one decimal place.

02:17.250 --> 02:22.880
So all we need to do is look at the format of this formula and this formula to match.

02:23.130 --> 02:27.780
So we can see that the L5 value is immediately after the brackets.

02:27.840 --> 02:35.040
So all we can do here is come before our brackets we're going to time round imagining that round is

02:35.040 --> 02:41.620
the option that we wanted to select and we have to open brackets to enclose our arguments.

02:41.730 --> 02:47.180
So now we can see that number is selected but we've already calculated our number because we bracketed

02:47.180 --> 02:48.940
this off already here.

02:49.140 --> 02:55.650
So I'm just going to click at the end of the formula here Carmen and then go straight to number of digits

02:55.710 --> 02:59.900
which was one and a tight one and I'm going to close brackets.

03:00.060 --> 03:06.570
So now you can see that what we've done is exactly the same formula but rather than referencing cell

03:06.630 --> 03:12.730
L5 we've actually calculated the value of L5 by enclosing it in brackets.

03:12.780 --> 03:19.860
So when we hit enter we can see that we get an answer of six point eight which is exactly what we wanted

03:19.860 --> 03:20.360
to do.

03:20.400 --> 03:22.400
And now I'm just going to decrease the.

03:22.440 --> 03:23.010
Which ones.

03:23.010 --> 03:26.000
Christ I'm just going to decrease the decimal places.

03:26.160 --> 03:31.500
So they go no we can say that we have six point eight they're just to demonstrate that that works let's

03:31.500 --> 03:32.700
just increase the brackets.

03:32.710 --> 03:37.810
You can see we have many decimal places in this one here and this one here and this one here.

03:37.920 --> 03:43.590
So I'm just going to copy that formula down and they go now you can see that they all only go to one

03:43.590 --> 03:44.760
decimal place.

03:44.940 --> 03:49.040
So let's just reduce that which is that one.

03:49.050 --> 03:55.410
So they go and you have just built your first complex formula by season two formulas that you already

03:55.410 --> 03:58.620
had and just copying the answers from one to the other.

03:58.820 --> 04:03.460
Oh just so that's complete when you when you download this file.

04:03.480 --> 04:04.970
I'm just going to put that one back.

04:05.190 --> 04:10.230
So they go with known negated the need to have a second formula to calculate the answer that we wanted

04:10.230 --> 04:11.040
to get to.

04:11.270 --> 04:11.750
OK.

04:11.850 --> 04:13.740
That's how you build complex formulas.

04:13.890 --> 04:15.780
But now you know how they work.

04:15.840 --> 04:16.290
Okay.

04:16.320 --> 04:17.680
See you in the next lecture.
