XL: Rounding Numbers and Time to Specified Multiples

ID: Q32378

The information in this article applies to:

Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95, version 7.0
Microsoft Excel for Windows, version 5.0
Microsoft Excel 98 Macintosh Edition
Microsoft Excel for the Macintosh, version 5.0

SUMMARY

The MROUND worksheet function returns a number that is rounded to a specific multiple. However, MROUND requires that 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.

MORE INFORMATION

Rounding a Number

To round a number to the nearest multiple of "x" by using ROUND in Microsoft 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 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 "x" by using ROUND in Microsoft Excel, 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 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("8:10")*24/0.25,0)*0.25/24

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:05 to the nearest quarter of an hour or to the nearest 15 minutes:


   A1: 8:05   B1: =TIMEVALUE(HOUR(A1)&":"&MROUND(MINUTE(A1),15))

The result is 8:00.

REFERENCES

For more information about the ROUND function, click the Index tab in Microsoft Excel Help, type the following text


   round worksheet function

and then double-click the selected text to go to the "ROUND" topic.

Additional query words: XL97 nth


Keywords          : kbdta xlformulas 
Version           : 
Platform          : 
Issue type        : kbhowto

Last Reviewed: March 13, 1999