Microsoft KB Archive/32378

= XL: Rounding Numbers and Time to Specified Multiples =

Article ID: 32378

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition
 * Microsoft Excel 98 for Macintosh
 * Microsoft Excel 5.0 for Macintosh

-



This article was previously published under Q32378



SUMMARY
The MROUND worksheet function returns a number that is rounded to a specific 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 &quot;x&quot; by using ROUND in Microsoft Excel, use the following syntax:   =ROUND( /x,0)*x For example, to round a number to the nearest multiple of 5, enter the following 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 number 1.58 to the nearest fractional multiple of 0.5:   =MROUND(1.58,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.

Rounding Time
To round time to the nearest multiple of &quot;x&quot; by using ROUND in Microsoft Excel, use the following syntax:   =ROUND( *24/x,0)*x/24 Note that you must multiply the time by 24 to convert the time value to its decimal equivalent. In this example, 8:05 becomes the number 8.08333. It is converted back to a time value by dividing by 24 again at the end of the formula.

For example, the following formula, which uses the ROUND function, rounds the time 8:10 to the nearest quarter of an hour or the nearest 15 minutes:   =ROUND(TIMEVALUE(&quot;8:10&quot;)*24/0.25,0)*0.25/24 The result is 8:15.

NOTE: You can refer to a cell address instead of using TIMEVALUE(&quot;8:05&quot;), 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:05 to the nearest quarter of an hour or to the nearest 15 minutes:   A1: 8:05   B1: =TIMEVALUE(HOUR(A1)&&quot;:&quot;&MROUND(MINUTE(A1),15)) The result is 8:00.

