Round to a Nickel in Excel

If you’ve been following the Canadian news (and who isn’t?), you know that the penny has been eliminated from circulation. To honour the occasion, Google made a special doodle for google.ca on February 4, 2013.

pennylastday_20130204

Rounding Guidelines

If you’re shopping with cash now, the final amount will be rounded up or down, to the nearest nickel. There are guidelines posted on the Royal Canadian Mint’s website: Eliminating the Penny: Rounding

rounddownmint

Rounding to the Nearest Cent

As an example, the Mint’s website shows the purchase of coffee and a sandwich, with tax, for a grand total of $4.86.

The tax department says to round the tax to the nearest cent, so you can use Excel’s ROUND function for to calculate the HST. Just multiply the subtotal by the tax rate, and round to 2 decimal places. Here is the formula in cell B6:

=ROUND(B5*D6,2)

roundpenny01

Rounding to the Nearest Nickel

With the HST, the grand total for the lunch is $4.86. We don’t have pennies now, so the cash payment will be rounded to the nearest nickel. Excel’s ROUND function can’t help with that.

Fortunately, there is another rounding function – MROUND – that can round to a specified amount. The MROUND function has two arguments – the number, and the multiple.

In this example, we want to round the grand total, which is in cell B7. We’ll enter the multiple in cell B9, to show how the cash payment was rounded. Here is the cash payment rounding formula in cell B10:

=MROUND($B$7,$B$9)

roundpenny02

Test the MROUND Formula

A nickel is worth 5 cents, so what happens if you enter a 5 in cell B9, to use as the multiple?

roundpenny03

Oops! That rounds the amount to 5 dollars, instead of the nearest nickel.

Change the amount in cell B9 to 0.05, which is the way that you’d enter a nickel amount in a worksheet.

roundpenny04

Perfect! With the MROUND function, and a multiple of 0.05, you can round those sales totals to the nearest nickel.

____________________