WEBVTT

0
00:02.440 --> 00:04.260
Okay guys, welcome back.

1
00:04.260 --> 00:09.240
Now we're going to have a look at the three rounding functions that are built into Excel...and they are

2
00:09.240 --> 00:12.490
"Round", and "RoundUp", and "RoundDown".

3
00:12.610 --> 00:16.090
Now you may wonder why you would want to use a rounding function...

4
00:16.260 --> 00:20.850
So if we look over here at column L, you'll see that we have a couple of integers.

5
00:20.850 --> 00:23.120
We have 6 and we have 8.

6
00:23.160 --> 00:28.830
We have some numbers that calculated out to one decimal place, but we also have some that several decimal

7
00:28.830 --> 00:29.840
places.

8
00:29.850 --> 00:36.700
Now do we really want to know that our average daily sales of oranges is 6.166667?

9
00:36.710 --> 00:41.260
Or would we like to say, you know what, let's just call it 6.
Or let's just call it 7.

10
00:41.310 --> 00:44.310
So this is what we would use a rounding function for.

11
00:44.530 --> 00:48.630
Okay, let's begin, and have a look at all three of the rounding functions.

12
00:48.630 --> 00:53.010
Just so we can see the data clearly, I'm going to do what we did in the last lecture...

13
00:53.010 --> 00:56.510
I'm just going to hide all of our sales on Monday through Saturday.

14
00:56.580 --> 01:01.240
So I've just highlighted them all, I'm just going to right click, and click "Hide"...there you go...

15
01:01.260 --> 01:05.440
Now let's stick in the title of our three functions...we have "Round", we have

16
01:05.440 --> 01:10.210
"RoundUp", and we have "RoundDown".

17
01:10.500 --> 01:10.770
OK.

18
01:10.810 --> 01:16.050
Let's just make those a little bit wider so we can see nice and clearly...there you go. "Round" is a built in

19
01:16.050 --> 01:20.880
function, and because it's going to be a formula we're typing, we're just going to type "=".

20
01:20.880 --> 01:22.640
We're going to type "Round".

21
01:22.680 --> 01:27.660
We're going to hit open brackets, and now we can have a look at the tooltip to give us a clue as to the

22
01:27.660 --> 01:31.740
structure of the formula...so we can see that we already have round there,

23
01:31.740 --> 01:33.770
...so it's already narrowed it down for us.

24
01:33.960 --> 01:37.840
And the first thing it's looking for is a number, now the number we're going to work on

25
01:37.890 --> 01:39.290
is this one just here.

26
01:39.420 --> 01:44.730
So I'm just going to left click it, and we can see from the tooltip that the next thing we require is

27
01:44.820 --> 01:52.190
a comma. And now you can see that "number of digits" has been bolded...so what I'm going to do for now

28
01:52.430 --> 01:57.900
is type ZERO, close the brackets, which encapsulates the arguments, and hit "Enter".

29
01:58.140 --> 02:04.350
So there you go...we can see that our rounded 6.832333  has become 7.

30
02:04.350 --> 02:08.110
Now I'm just going to copy that formula all the way down and we've done this before.

31
02:08.160 --> 02:13.680
We just hover over this little box up the corner until our cursor changes, double click, and there you can see

32
02:13.680 --> 02:16.050
that our formula has been copied all the way down.

33
02:16.310 --> 02:16.590
Okay.

34
02:16.600 --> 02:19.560
Next Let's type in a "RoundUp" function.

35
02:19.560 --> 02:21.060
It would help if I could type!

36
02:21.210 --> 02:26.010
So "RoundUp", open brackets...again you can see that we need to enter a number...

37
02:26.010 --> 02:28.230
so we're just going to click the same number again.

38
02:28.350 --> 02:34.780
We're going to stick with the same format, hit comma, type "0", close the brackets and hit "Enter".

39
02:34.890 --> 02:40.250
And I'm just going to copy that formula down, and then I'm going to write in the "RoundDown" function.

40
02:40.260 --> 02:47.470
So "RoundDown", open brackets, and then I'm going to select the same number again...hit comma, number

41
02:47.470 --> 02:53.310
of digits, and then stick to zero just so we can see the differences, hit "Enter", and then copy that formula

42
02:53.310 --> 02:54.030
down.

43
02:54.400 --> 02:56.290
Okay let's have a look at what we've got.

44
02:56.610 --> 03:03.300
Okay if we just look at our top line, our apples, we can see that our average daily sales exactly work out to 

45
03:03.300 --> 03:06.880
6.8 and a lot of 3's after it.

46
03:06.930 --> 03:13.770
If we round 6.8 to zero decimal places, we can see that it rounds to 7, which is pretty obvious

47
03:13.770 --> 03:18.080
because 6.8 is nearer to 7, than it is to 6.

48
03:18.120 --> 03:23.250
If we look over at the "RoundUp", we can see gain that it's been rounded to 7, because this time we've

49
03:23.250 --> 03:29.370
explicitly told Excel to round up.
And if we look at the "RoundDown", we can see that it's ignored the

50
03:29.370 --> 03:34.260
8.3, and it's rounded down to the nearest whole number being 6.

51
03:34.260 --> 03:41.340
So rounding down takes you down to the nearest whole number. Rounding up takes you up to the nearest whole

52
03:41.340 --> 03:47.340
number. And round just rounds to the nearest whole number based on whatever the decimal places are, after

53
03:47.340 --> 03:48.500
your integer.

54
03:48.870 --> 03:49.600
Okay.

55
03:49.620 --> 03:56.070
So we can see if we look down the data that's 6.1666, has been rounded to 6, obviously because

56
03:56.070 --> 03:58.260
it's nearer to 6 than it is to 7.

57
03:58.470 --> 04:03.120
Rounding up takes us up 7 because 7 is the next nearest large number.

58
04:03.240 --> 04:07.220
And rounding down again takes us specifically down to 6.

59
04:07.320 --> 04:08.760
And so on and so forth.

60
04:08.760 --> 04:11.720
So all very nice and straightforward so far.

61
04:11.730 --> 04:17.030
Now you'll remember that we had comma zero, which means that we're going to have no decimal places.

62
04:17.130 --> 04:23.130
If you change your mind, and you want to change everything to...let's say, one decimal place there's a very

63
04:23.130 --> 04:24.580
quick way of doing this.

64
04:24.770 --> 04:28.770
If we click in here, we can see we have "Round, 0".

65
04:29.010 --> 04:31.630
And if we scroll through them, and we just move the cursor out the way,

66
04:31.800 --> 04:36.170
if we scroll through them, we can see that every single one of them say comma zero.

67
04:36.450 --> 04:41.520
And there's a very fast way to change all of those in one go.
What we're going to do is highlight the

68
04:41.520 --> 04:43.240
entire range.

69
04:43.380 --> 04:45.470
We're going to hit CTRL and F...

70
04:45.540 --> 04:51.420
And what we're going to do is type comma zero (,0)...which is the one thing that's common to all of our formulas

71
04:51.420 --> 04:52.600
that they've just written.

72
04:52.800 --> 04:56.800
We're going to hit the "Replace" button, and we're going to say comma...

73
04:56.880 --> 05:01.750
...did we say 1? Let's say 1!... comma 1 (,1) and then we're going to hit the "Replace All" button.

74
05:03.200 --> 05:08.300
And there you go! You can see that Excel has told us it's made 33 replacements...just click "OK".

75
05:08.450 --> 05:14.950
We can close that. Now if we have a look at our formula, we can see that we now have "Round" L5 comma 1

76
05:15.320 --> 05:22.520
so that formula changed, that formula's changed to comma 1, as has that, as has that one...in fact all of

77
05:22.520 --> 05:23.840
them now say comma one (,1).

78
05:23.910 --> 05:29.240
So that's a very quick way of changing all your formulas, if they have a common element, and

79
05:29.240 --> 05:31.550
you just want to change one aspect of them.

80
05:31.740 --> 05:36.980
Okay, so now we can see if we've gone to one decimal place, that all of our answers now equate to a whole

81
05:36.980 --> 05:41.280
number, a decimal place, and one digit after the decimal place.

82
05:41.300 --> 05:46.240
Now at the moment it looks a bit ugly because we've got lots of decimal places displayed.

83
05:46.280 --> 05:50.720
So what we're going to do in the next lecture is have a look at how to format our text,  so it looks a little more

84
05:50.720 --> 05:51.480
uniform.

85
05:51.560 --> 05:51.990
Okay.

86
05:52.040 --> 05:53.380
So you in the next lecture.