Excel: Round to the Nearest $0.05 with MROUND

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


Problem: I know I can use the ROUND function to round to the nearest dollar or penny. How do I round to the nearest nickel or quarter?

Strategy: You can use the MROUND function. This function will round a number to the nearest multiple of the second argument. To round to the nearest nickel, use =MROUND(B2,0.05). To round to the nearest quarter, you use =MROUND(B2,0.25).

  1. Round to the nearest 0.05.

    Gotcha: Both arguments in the MROUND function must have the same sign. This can be difficult when you have a mixture of positive and negative values. The SIGN function will return either a 1 or -1, based on the sign of a number. If there is a possibility that the first argument might be negative, you can multiply the second argument by SIGN of the first argument. =MROUND(B2,0.05*SIGN(B2))