In this lecture let's take a look at different ways we can round values in Excel.
Here we have a set of random numbers and you can see they have different digits to the right hand side.
The Cell Formatting of these is the general format so we can see the entire values, the entire cell
content in here.
If I collapse the decimal.
So if I go here and say decrease decimal, I'm going to see a 2 here
but that doesn't mean I've rounded these values.
The actual number in this cell is 1.56.
It's not 2. To actually change this.
I need to use the Round function. I'm just going to put this back to general format.
Let's check out the different options we have with ROUND. The first argument is the number which is this one.
In the first case I want to round to the integer value so I just need to put zero here, for the number
of digits, close bracket, and press enter.
I get a 2. Now the actual cell content is a 2.
I see the formula in here but if I copy this, right mouse click, and paste
this is as values.
I can see a 2 in the cell.
I'm just going to press control+z to take me back to my formula.
Drag this down and I get my rounded values. So you can see the way Excel works with rounding.
The moment we come across a 5 or a value higher than 5 after the decimal, it's going to round
it up.
If we have a value less than 5 after the decimal, it's going to round it down.
Now let's move on to our next example. We want to round to one decimal.
So the function is round, number is this one.
What do I have to type in for number of digits?
A one, close bracket, press enter.
Now it's rounding this to one decimal place.
Just double click to the side and send this down.
Notice that this one became .40 because I have a 6 here. It's going to round up this value from
a 3 to a 4.
How about rounding to multiples of ten?
Basically what if I didn't want to round values on the right hand side of the decimal but on the left
hand side? Here you actually need to use the minus sign.
So my number is this
but now the number of digits is minus 1. Because I'm not rounding on the right hand side.
I actually want to round these on the left hand side.
So when I close a bracket and press enter for this I get a zero.
Now if I push this down.
Let's take a look at this one.
I get 4850.00, right.
It's rounding the two down.
And here it's 120.00
What if I wanted to round to a multiple of 100.
What do I need to type in here?
A minus two, close bracket, press enter. For the small numbers I'm going to get zero, for this one
I'm going to get 4,900.00 and moving this way two decimal places and now I'm rounding
up because I have a 5.
So that's going to push this from 48 to 49.
In this case I'm moving two decimal places and it's going to round this down because I have a two.
So I'm going to get rounded down to a hundred.
So the trigger point here on whether it's rounding up or down is whether we have a value equal to or
greater than a 5 or less than a 5.
But what if we didn't want this type of flexibility.
What if we always wanted to round up our values.
There is another function we can use and it's called the "RoundUp" function.
Again we just need to give it our numbers and then the number of digits we want to round up.
So let's say
in this case we always want to round up to the closest integer.
So I'm going to put a zero, close bracket, press enter, send this down. So notice for this case where
the usual Excel behaviour with a round function is to round this down, with round up we always make sure
we're rounding upwards.
What if I wanted to round up to one decimal place. I use the RoundUp function and I just put in one
for the number of digits. Now what if I wanted to round down to the integer?
So I wanted it the other way round.
If it's 1.56, I don't want it to go to 2.
I wanted to go to 1.
Here I can use the RoundDown function.
Give it the number and tell it how many number of digits I want to round down to.
So in this case I want to round down to the closest integer, put a zero, and send this down.
Now what if I want to round down to one decimal place.
We use the RoundDown function and we put a one for the number of digits.
So now we can see here where we have 1.45
The normal round function would give 1.5.
The round up would give 1.5 but with round down
We make sure we always push the number downwards.
Okay.
So that's how you can use the Round, RoundUp, and RoundDown functions in Excel.