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.