This article was previously published under Q32378
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
The MROUND worksheet function returns a number that is rounded to aspecific multiple. However, MROUND requires that the Analysis ToolPak add-in be installed before the function is available. If you do not want to install the Analysis ToolPak, you can use the ROUND worksheet function instead.
Rounding a Number
To round a number to the nearest multiple of "x" by using ROUND inMicrosoft Excel, use the following syntax:
=ROUND(<value or reference>/x,0)*x
For example, to round a number to the nearest multiple of 5, enter thefollowing into a worksheet:
A1: 17 B1: =ROUND(A1/5,0)*5
The result is 15.
The following formula, which uses the MROUND function, rounds the number1.58 to the nearest fractional multiple of 0.5:
The result is 1.50.
NOTE: The MROUND function is installed with the Analysis Toolpak add-in. To load this add-in after you install it, click Add-ins on the Tools menu. In the Add-ins dialog box, click to select the Analysis ToolPak check box.
To round time to the nearest multiple of "x" by using ROUND in MicrosoftExcel, use the following syntax:
=ROUND(<value or reference>*24/x,0)*x/24
Note that you must multiply the time by 24 to convert the time value to itsdecimal equivalent. In this example, 8:05 becomes the number 8.08333. It isconverted back to a time value by dividing by 24 again at the end of theformula.
For example, the following formula, which uses the ROUND function, roundsthe time 8:10 to the nearest quarter of an hour or the nearest 15 minutes:
The result is 8:15.
NOTE: You can refer to a cell address instead of using TIMEVALUE("8:05"), as in the following formula:
A1: 8:10 B1: =ROUND(A1*24/0.25,0)*0.25/24
The following formula, which uses the MROUND function, rounds the time 8:05to the nearest quarter of an hour or to the nearest 15 minutes: