Rounding up and down in Excel

There are some simple formulas in Excel for rounding your results to the nearest decimal, and I’ll also cover the functions which enable you to round to the nearest multiple.

ROUND, ROUNDUP and ROUNDDOWN round to a specified number of decimal places. In the below example we are firstly rounding to the nearest whole number, then rounding up by 1 decimal and down by no decimals. Columns F and G are examples of using the CEILING and FLOOR functions to round to the nearest multiple. The formulas used in Row 4 are copied below the data for your reference.

=ROUND(B4,0)

=ROUNDUP(B4,1)

=ROUNDDOWN(B4,0)

=CEILING(B4,5)

=FLOOR(B4,10)

CEILING and FLOOR can also work to decimal places and TRUNC truncates the decimal places to work in very much the same way as ROUNDDOWN.=TRUNC(B4) produces the same result as =ROUNDDOWN(B4,0).

MROUND can be used to round numbers to the nearest multiple e.g. =MROUND(E6,5) will returning 15 having rounded the value to it’s nearest 5, =MROUND(E6,10) would return 20 when rounding to the nearest 10.