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.

____________________

6 thoughts on “Round to a Nickel in Excel”

  1. That’s interesting. When the halfpenny was eliminated in the UK, it was eliminated entirely. But we have one and two penny coins.

    1. Hi Andrew. I think I read somewhere, that both the 1p and 2p coins were to be abolished, either this year or 2014, which makes this post all the more interesting.

  2. Just for reference, for Excel 2003 and older the MROUND function is only available after loading the Analysis Toolpak add-in.

  3. Many shops in The Netherlands have been rounding to 5 Euro cts for years, many of them immediately after the introduction of the Euro. But some don’t, which leads to useless 1 and 2 Euro ct coins in my wallet.
    I think they can stop coining those 1 and 2 cts, the coins must cost more than their monetary value.

Leave a Reply

Your email address will not be published.

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