ROUND and ROUND again with Excel

Getting rounding right

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

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “ROUND and ROUND again with Excel

  1. Hi, little remark: on my machine 1.250 will round to 1.20 with the mround() formula and to 1.25 with the round() formula (excel 2010 on win7pro).