Excel offers a number of different options when it comes to rounding. You can round to specific decimal places both to the left, and to the right, of the decimal point. You can round up and round down and even round to a specific value.

Rounding in general

The ROUND function allows you to round values to specific decimal places. Assume A1 contains 155.566.

=ROUND(A1,2)

will round to 2 decimal places and will display 155.57. This is commonly used for currency calculations.

=ROUND(A1,-2)

will round to hundreds, or 2 decimals places to the left of the decimal point and will display 200. This is useful at budget time when you don’t really need to round to dollars.

=ROUND(A1,0)

rounds to whole numbers (no decimals) and will display 156. Use for whole dollar calculations

Rounding up and down

Sometimes you need to round up eg when calculating how many sea containers of a certain size you need to fill a certain order. Generally you can’t hire part of a sea container. So if you are left with 0.4 of a sea container you have to order a whole sea container.

Excel has a ROUNDUP function. It works like the ROUND but all amounts are always rounded up. This function rounds away from zero.

When you need to round down you can use the ROUNDDOWN function. This function rounds towards zero. This is handy if you don’t need the decimal part of the result.

Rounding to a specific value

If you are working with cash in Australia our lowest value coin is 5 cents. To round to the closest 5 cents you can use the MROUND function.

=MROUND(A1,0.05)

The second argument in the function is the value to round to.

Interestingly you can also use the ROUND function to round to a specific value. The following formula will also round to the nearest 5 cents.

=ROUND(A1/0.05,0)*0.05