Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
You've probably rounded values up quite a few times in your life. At a restaurant you can round the bill up to the nearest $10 increment and tell your server to take the change, or you can describe the cost of a new building as about $10,000, even though the price is a little higher or lower. Excel has several functions that you can use to round off worksheet values. You should become familiar with them, so you can determine which function is best for your particular task. The most familiar rounding function is round, which takes two arguments: the value to be rounded, in this case the value in cell A2, and the number of digits to the right of the decimal point to which you want to round the answer.
As an example, suppose that I want to round this value, 12.875, to two digits to the right of the decimal place. To do that, you would create the formula =round(A2, which is the cell that contains the value, a comma, and then the number of digits. And again, in this context, that means the number of digits to the right of the decimal place. I want to round it two digits, so I type the number two, a right parenthesis, hit Return, and you see the value, 12.88.
You can also use the round function to round to tens, hundreds, thousands or larger values. To round to the tens, which is one digit to the left of the decimal point, you would make the second argument -1. So remember, positive numbers round based on digits to the right of the decimal point, negative numbers to the left of the decimal point. So if I were to change this digit from two to negative one and hit Return, Excel rounds the value to 10. The Round function rounds any value from 0.5 or higher up and rounds the rest down, and also as you can see, when it's working with tens, it would round the number 15 up to 20, and anything less than 15 down to 10.
If you'd prefer to have Excel round any number that contains a decimal value up to the next higher number, you can do so using the roundup function. This type of calculation is common when a phone company calculates the number of minutes you've used on your cell phone, where a partial minute of any length counts as a full minute against your plan. The roundup function takes the same arguments as the round function, so the formula =roundup(A2,0), meaning that I'm rounding to a whole number, no digits to the right or left of the decimal point, and right parenthesis to close the formula.
Press Return and Excel rounds the value up to 13. Now if I do the same thing here with the value 11.1, =roundup(A3,0) even though .1 is less than .5, because I'm using roundup, Excel will roundup the value to 12. If I use round(A3,0) it would round it to 11. So again, it all depends on which direction you want to go. And as you suspected, there is a rounddown, function, which rounds the value down.
For example, if you assume you want to round a value to the hundreds place, such as here in A4, so if I wanted to round this value to the hundreds. I would type =round(A4, which is the cell that contains the value, a comma, and then the number of digits. Again, I'm rounding to the left of the decimal point so I would type minus two for the hundreds place, right parenthesis to close, Return, and it rounds it up to 1200. If I want to round it down, I would type =rounddown(A4, and then the number of digits, again, to the left of the decimal point, so it's negative two, right parenthesis, Return, and Excel basically discards the 60 and rounds it down to 1100.
Now let's suppose that you want to round the number to the nearest one-half, or quarter, or even the closest value of seven. To do that, you can use the mround function. The mround function takes two arguments, and I'll just start typing in the formula mround, left parenthesis. It takes in the number, which in this case is in cell A5, and then it takes the multiple. So let's say that I want to round 11.6 to the closest value of .5, in other words the closest half. If I type .5) and press Return, then Excel rounds it down to 11.5.
If I were to change the value in cell A5 to 11.75, then the formula in C5 will round the value up to 12. Remember, anything that's one half of whatever you're rounding or greater will go up; anything less than that will go down. The rounding functions give you great deal of control over how Excel handles your numbers. Once you establish the rule by which you want round your worksheet values, you can create a formula that follows that rule.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 64729 Viewers
80 Video lessons · 124331 Viewers
52 Video lessons · 60260 Viewers
59 Video lessons · 46099 Viewers