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: