1
00:00:01,160 --> 00:00:07,180
In this lecture let's take a look at different ways we can round values in Excel.

2
00:00:07,190 --> 00:00:14,240
Here we have a set of random numbers and you can see they have different digits to the right hand side.

3
00:00:14,240 --> 00:00:21,140
The Cell Formatting of these is the general format so we can see the entire values, the entire cell

4
00:00:21,140 --> 00:00:23,840
content in here.

5
00:00:23,840 --> 00:00:26,100
If I collapse the decimal.

6
00:00:26,210 --> 00:00:31,440
So if I go here and say decrease decimal, I'm going to see a 2 here

7
00:00:31,670 --> 00:00:35,030
but that doesn't mean I've rounded these values.

8
00:00:35,030 --> 00:00:39,940
The actual number in this cell is 1.56.

9
00:00:39,950 --> 00:00:43,040
It's not 2. To actually change this.

10
00:00:43,040 --> 00:00:49,830
I need to use the Round function. I'm just going to put this back to general format.

11
00:00:49,850 --> 00:00:56,330
Let's check out the different options we have with ROUND. The first argument is the number which is this one.

12
00:00:56,330 --> 00:01:04,640
In the first case I want to round to the integer value so I just need to put zero here, for the number

13
00:01:04,640 --> 00:01:07,670
of digits, close bracket, and press enter.

14
00:01:07,670 --> 00:01:12,130
I get a 2. Now the actual cell content is a 2.

15
00:01:12,220 --> 00:01:17,210
I see the formula in here but if I copy this, right mouse click, and paste

16
00:01:17,210 --> 00:01:18,530
this is as values.

17
00:01:18,680 --> 00:01:20,690
I can see a 2 in the cell.

18
00:01:21,290 --> 00:01:25,640
I'm just going to press control+z to take me back to my formula.

19
00:01:25,790 --> 00:01:33,440
Drag this down and I get my rounded values. So you can see the way Excel works with rounding.

20
00:01:33,650 --> 00:01:40,850
The moment we come across a 5 or a value higher than 5 after the decimal, it's going to round

21
00:01:40,970 --> 00:01:41,640
it up.

22
00:01:41,810 --> 00:01:46,970
If we have a value less than 5 after the decimal, it's going to round it down.

23
00:01:47,630 --> 00:01:52,570
Now let's move on to our next example. We want to round to one decimal.

24
00:01:52,700 --> 00:01:55,970
So the function is round, number is this one.

25
00:01:55,970 --> 00:01:59,220
What do I have to type in for number of digits?

26
00:01:59,240 --> 00:02:02,010
A one, close bracket, press enter.

27
00:02:02,210 --> 00:02:05,290
Now it's rounding this to one decimal place.

28
00:02:05,420 --> 00:02:07,950
Just double click to the side and send this down.

29
00:02:08,360 --> 00:02:16,160
Notice that this one became .40 because I have a 6 here. It's going to round up this value from

30
00:02:16,160 --> 00:02:18,020
a 3 to a 4.

31
00:02:18,020 --> 00:02:21,440
How about rounding to multiples of ten?

32
00:02:21,440 --> 00:02:27,410
Basically what if I didn't want to round values on the right hand side of the decimal but on the left

33
00:02:27,410 --> 00:02:32,470
hand side? Here you actually need to use the minus sign.

34
00:02:32,510 --> 00:02:34,550
So my number is this

35
00:02:34,550 --> 00:02:40,670
but now the number of digits is minus 1. Because I'm not rounding on the right hand side.

36
00:02:40,700 --> 00:02:44,030
I actually want to round these on the left hand side.

37
00:02:44,660 --> 00:02:50,020
So when I close a bracket and press enter for this I get a zero.

38
00:02:50,020 --> 00:02:51,620
Now if I push this down.

39
00:02:51,820 --> 00:02:53,420
Let's take a look at this one.

40
00:02:53,620 --> 00:02:56,170
I get 4850.00, right.

41
00:02:56,170 --> 00:02:58,370
It's rounding the two down.

42
00:02:58,900 --> 00:03:01,050
And here it's 120.00

43
00:03:01,120 --> 00:03:04,550
What if I wanted to round to a multiple of 100.

44
00:03:04,570 --> 00:03:06,720
What do I need to type in here?

45
00:03:06,720 --> 00:03:14,290
A minus two, close bracket, press enter. For the small numbers I'm going to get zero, for this one

46
00:03:14,290 --> 00:03:21,130
I'm going to get 4,900.00 and moving this way two decimal places and now I'm rounding

47
00:03:21,220 --> 00:03:23,410
up because I have a 5.

48
00:03:23,410 --> 00:03:27,330
So that's going to push this from 48 to 49.

49
00:03:27,430 --> 00:03:33,300
In this case I'm moving two decimal places and it's going to round this down because I have a two.

50
00:03:33,310 --> 00:03:36,060
So I'm going to get rounded down to a hundred.

51
00:03:36,550 --> 00:03:42,940
So the trigger point here on whether it's rounding up or down is whether we have a value equal to or

52
00:03:42,940 --> 00:03:46,180
greater than a 5 or less than a 5.

53
00:03:46,270 --> 00:03:49,350
But what if we didn't want this type of flexibility.

54
00:03:49,360 --> 00:03:53,200
What if we always wanted to round up our values.

55
00:03:53,200 --> 00:03:57,900
There is another function we can use and it's called the "RoundUp" function.

56
00:03:57,970 --> 00:04:03,100
Again we just need to give it our numbers and then the number of digits we want to round up.

57
00:04:03,100 --> 00:04:03,800
So let's say

58
00:04:03,880 --> 00:04:07,330
in this case we always want to round up to the closest integer.

59
00:04:07,330 --> 00:04:15,490
So I'm going to put a zero, close bracket, press enter, send this down. So notice for this case where

60
00:04:15,610 --> 00:04:22,000
the usual Excel behaviour with a round function is to round this down, with round up we always make sure

61
00:04:22,270 --> 00:04:23,930
we're rounding upwards.

62
00:04:24,500 --> 00:04:31,150
What if I wanted to round up to one decimal place. I use the RoundUp function and I just put in one

63
00:04:31,210 --> 00:04:37,150
for the number of digits. Now what if I wanted to round down to the integer?

64
00:04:37,180 --> 00:04:38,940
So I wanted it the other way round.

65
00:04:38,950 --> 00:04:42,240
If it's 1.56, I don't want it to go to 2.

66
00:04:42,250 --> 00:04:44,260
I wanted to go to 1.

67
00:04:44,260 --> 00:04:48,430
Here I can use the RoundDown function.

68
00:04:48,430 --> 00:04:53,350
Give it the number and tell it how many number of digits I want to round down to.

69
00:04:53,350 --> 00:05:00,280
So in this case I want to round down to the closest integer, put a zero, and send this down.

70
00:05:00,280 --> 00:05:05,060
Now what if I want to round down to one decimal place.

71
00:05:05,140 --> 00:05:09,980
We use the RoundDown function and we put a one for the number of digits.

72
00:05:10,030 --> 00:05:14,330
So now we can see here where we have 1.45

73
00:05:14,330 --> 00:05:17,770
The normal round function would give 1.5.

74
00:05:17,770 --> 00:05:21,760
The round up would give 1.5 but with round down

75
00:05:21,790 --> 00:05:24,970
We make sure we always push the number downwards.

76
00:05:24,970 --> 00:05:25,230
Okay.

77
00:05:25,240 --> 00:05:30,910
So that's how you can use the Round, RoundUp, and RoundDown functions in Excel.