Rounding Numbers & Removing Decimals in Excel
Excel includes quite a few functions that round or remove decimals from numbers. The video below demonstrates the difference between all these functions. Download the featured file here.
Here’s our grand tour…
INT Function
Rounds a number down to the nearest integer
=INT(A1)
TRUNC Function
Truncates a number by removing the decimal portion. Has a Num_digits argument that allows you to specify precision – so for example 1 would leave one decimal point, -1 would zero the number to the left of the decimal place. =TRUNC(A1, 1)
ODD Function
Rounds away from zero to the next odd integer. So for example 2.1 become 3 and -2.1 becomes -3.
=ODD(A1)
EVEN Function
Rounds away from zero to the next even integer. So for example 3.1 becomes 4 and -3.1 becomes -4.
ROUND Function
Values up to 0.49999999 are rounded towards zero; greater than are rounded away from zero to the next integer.
ROUNDUP Function
Rounds away from zero to the next integer. Includes a Num_digits argument which allows you to specify the number of digits you want to round – a negative Num_digits value would round the figure to the left of the decimal place.
=ROUNDUP(A1, 1)
ROUNDDOWN Function
Rounds towards zero to the next integer. Includes a Num_digits argument which allows you to specify the number of digits you want to round – a negative Num_digits value would round the figure to the left of the decimal place.
MROUND Function
Rounds a number to the multiple you specify. In the example below the retail prices need to be rounded to the nearest 25 pence. The formula used is =MROUND(C2, 0.25)
CEILING/CELING.MATH Function
Rounds a number up to the nearest multiple of significance. In the example below I need to order in multiples specified in column C. Column B tells me how much I actually need, the CEILING function tells me how many I need to order.
=CEILING(B3, C3)
CEILING or CEILING.MATH?
The difference between CEILING and CEILING.MATH is the way it rounds negative numbers. CEILING rounds positive and negative numbers away from zero, whereas CEILING.MATH rounds positive numbers away from zero and negative number towards zero. CEILING.MATH is only available in Excel 2013, and is renamed from CEILING.PRECISE in Excel 2010.
FLOOR/FLOOR.MATH Function
Rounds a number down to the nearest multiple of significance. In the example below I need return items but in the specified multiples in column C. Column F tells me how many I want to return, the FLOOR function tells me how many I can return.
FLOOR.MATH will round negative numbers away from zero, whereas FLOOR would round them towards zero.
=FLOOR(F3,C3)