So here we are, with calculations that give long decimal results.The value of PI is 3.14159265...god knows where it ends. But most of our work is done by just 3.14. This is called rounding. Let’s see how we can round the numbers in excel.
Let's say in cell A2 we have 92.45477919. To limit these decimal points you can go to Home Tab? Decrease Decimal and click it until you reach to your desired number of decimals.
Wouldn’t it be easy and time-saving if you can do it with a formula?
Excel provides three formulas for Rounding Numeric numbers:
| ROUND (value, num of decimal digits) | ROUNDUP(value, num of decimal digits) | ROUNDDOWN (value, num of decimal digits) |
Examples will make it easy.
The round function simply limits decimal value. It takes two arguments. First the value (the number that needs to be rounded) and second the number of decimals.
For example here in Cell A2, I have a random decimal number generated by RAND() function and then multiplied by 100. Now in Cell B2 I want to have only 1 decimal digit, so I write:
You can see the result in A2 in the below image.
It shows only one digit.
Same as the ROUND function of Excel, the ROUNDUP function limits the decimal points shown but it always increases the last decimal digit by one.
This, too, takes two arguments. First the value (the number that needs to be rounded) and second the number of decimals.
Now in Cell C2, I want to have only 1 decimal digit but I want to increase it by one to compensate the leftover digits. So I write this formula in C2:
You can see that the value is rounded (92.5) to one decimal and it is increased by one.
The ROUNDDOWN function just shows the last decimal digit as it is and makes all following decimal digits 0. In short, it prevents the rounding up of a number.
In Cell D2 I have written this formula:
You can see that 92.454779 is now 92.4. Similarly, all numbers in column D are shown as they depend on defined decimal digits.
Now you know how to harness control of these decimal digits to your desired number digits. I tried to explain ROUND functions in Excel in the most precise way. These Rounding functions are available in Excel 2016, 2013, 2010 and in older Excel versions.
Let me know if that would be helpful. If your problem is not solved then post it in the comments section.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
I want to take my results for example, 213 and 212 and round up to 215 and round down to 210. I am trying to round up to "5" and down to "0". Does anyone know how to do this?
"The way I normally do this, is by dividing by 5, rounding to the nearest whole number, and then multiplying back by 5:
If the number you want to round in this way is in A1, then:
=ROUND(A1/5,0)*5
Does that solve your problem? "
Thanks Alan. The formula does exactly what I need it to do. Perfect.
Thanks William, it was what I was really looking for.
Hi all, I'm trying to round (to the nearest hundreth) the result of the sum of a range of numbers divided by another number.
Here's what I've attempted so far but it's not working (currently results in: #VALUE):
=ROUND(SUM(H7:V7/5280),2)
Any help would be greatly appreciated.
try this: =ROUND(SUM(H7:V7)/5280,-2)
you can use this article too https://www.exceltip.com/excel-format/using-the-round-function-to-round-numbers-to-thousands.html
How do I use the roundup formula to a cell which already has a formula eg
cell A1 value = 60
cell A2 value = 8
cell A3 has a value =A1/A2. This leaves me a value of 7.5
I want to round off that value in cell A4. how would you write out the formula to achieve an 8 value
It is simple Bradley. Use Formula in A4 =ROUNDUP(A3,0)
Hi
I have a formula that is date related as per the below, this calculates monthly budget lines but if the month is short by a day - ie: Nov it changes the budget line as it is calculating a 'short month' 0.9666667 instead of 1
do you know how I can round up the end formula whilst keeping the below
=(DATEDIF([@[Activity Start Date2]],[@[Activity Finish Date3]],"d"))/30
use ROUNDUP Function.
=ROUNDUP(DATEDIF([@[Activity Start Date2]],[@[Activity Finish Date3]],”d”))/30)