This article was previously published under Q32378
This article has been archived. It 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: