Rounding to the Nearest in Microsoft Excel
By Decimal Using ROUND
ROUNDUP and ROUNDDOWN work exactly
the same way as ROUND, except of course, they force the up or down part.
You can round any value to the nearest decimal place using the ROUND
function. The following graphic demonstrates the use of the ROUND function to
1 digit. The 1 indicates how many digits to the right of the decimal point
should be used in the result. In this example, the result is 123456.8 because
it rounds the .789 to .8.
The following demonstrates how to round to the nearest multiple
of 10. We tell it to be -1 digits from the decimal point, which means how many
digits to the left of the decimal point. This results in a value of 123460.
If you want to round to the nearest whole number, then you
simply put a 0 as the number of digits, as shown:
To the Nearest Nth Using MROUND
How do you round to the nearest nickel? What about in
increments of 25? Use MROUND. This function requires that you turn on use of
the Analysis Toolpak. Go to the Tools menu, and choose Addins. Then check the
box next to Analysis Toolpak. You only need to check this option once, but if
you send your workbook out to others, you may need to tell them to turn on the
Analysis Toolpak, or they'll get a #NAME? error in the cell.
Below, we demonstrate how to round the value to the nearest
quarter or .25. If you like all your prices in your pricelist to be in 25-cent
increments, this is how you'd do it.
Guarantee an Ending Value
Now what if you wanted all your prices to be X dollars and 95
cents. So your prices would be 2.95, 3.95, 4.95, etc. This is accomplished by
rounding to the nearest dollar and subtracting 5 cents. As shown:
OfficeArtilces.com debuted on May 26, 2005.
MrExcel.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.
Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered
trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.
All contents © 1998-2014 by MrExcel Consulting | All rights reserved