There are Excel formulas for rounding numbers according to what the user wants: rounding up, rounding down with a specific number of digits after the decimal point. You can also round off to get whole numbers. In this article, we will see how to do it.
Sometimes working with decimals is not easy. For reasons of clarity and for better readability, rounding is very useful. To perform a rounding in Excel, it’s simple. It is enough to master the formulas which allow to do it: classic rounding, upper or lower with the possibility of forcing a precise number of digits after the decimal point.
Rounding Excel: formulas
The basic formula for classic rounding is easy to remember. Just enter = ROUND (number; no_digits) in the cell. Instead of number, put the number to be rounded. No_digits meanwhile represents the number of digits after the decimal point. Instead of the number, you can also put the reference of a cell (ex: A2, B3, D6) or nest the ROUND formula in another formula if the number to be rounded must be obtained from an automatic calculation.
Ex: we want to round the number 1234.5678 to two digits after the decimal point. The formula for doing this is as follows: = ROUND (1234.5678,2). The result is 1234.57. As you can see, Excel has automatically retained a higher rounding since the number that comes in third position after the decimal point is greater than or equal to 5.
If this figure were for example 2, 3 or 4, the rounding would have been 1234.56, or the classic rounding down when the following figure is less than 5. In addition, it is also possible to round Excel by obtaining a whole number, therefore without decimal. To do this, we simply put a 0 at the number_num level. Ex:= ROUND (1234.5678,0). The result will be 12345.
You might want to force rounding up or down in Excel cells regardless of the conventional rule. In this case, the formula to be used is only a variant of that presented above. For higher roundings, it will suffice to do = ROUNDUP (number, digit_num). For rounding down, the formula will be = ROUNDDOWN (number, digit_num).
Round calculated numbers in Excel
Obviously when using Excel, the numbers displayed in cells usually come from calculations. To apply rounding, the formula must therefore be nested in the one used for the calculation. Ex: to round to three digits after the comma the number of a cell which already contains the formula = B2 * H2, we will do as follows: = ROUND (B2 * H2; 3). You now have all the tools to round in Excel, with the desired degree of customization.