This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Round to the Nearest $0.05 with MROUND
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).
Figure 351 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))
MrExcel.com & related websites debuted on November 21, 1998.
ExcelArticles.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only,
without warranty either expressed or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures
on this web site are provided "as is" and we do not guarantee that they can be used in all situations.
This site contains affiliate links. Any affiliate commissions that we earn when you click a link to Amazon
or other sites is reinvested in keeping MrExcel.com and ExcelArticles.com running. You can earn a commission for
Excel ® is a registered trademark of the Microsoft Corporation.
MrExcel ® is a registered trademark of Tickling Keys, Inc.