Other Rounding Functions in Microsoft Excel:
ROUNDUP, ROUNDDOWN, MROUND, EVEN, ODD, and TRUNC

This Keynote Support tutorial teaches the Microsoft Excel functions of ROUNDUP, ROUNDDOWN, MROUND, EVEN, ODD, and TRUNC. It provides examples that show how these functions can be used in spreadsheets.

Please note that the Excel ROUND function is taught in a separate tutorial, Fabulous Examples of Excel Functions: the ROUND Function.

Author: Keynote Support

For newbies, we recommend the video-based course Excel 2010 Tutorial for Beginners. We have this course (over 9 hours of hands-on lessons) and find it well done and a good value. An advanced course is also available.

Don't forget that every Excel function must begin with an equal (=) sign unless it's imbedded inside another function. Now - click on a link below to go directly to that rounding function.

ROUNDUP EVEN

ROUNDDOWN ODD

MROUND TRUNC

ROUNDUP and ROUNDDOWN

These two functions are similar except ROUNDUP always rounds up a number, while ROUNDDOWN always rounds down a number. The syntax of the ROUNDUP and ROUNDDOWN functions is:

ROUNDUP(number,number-of-digits)

ROUNDDOWN(number,number-of-digits)

… where number is the number to be rounded, and number-of-digits is the number of decimal places or place value that Excel should round to.

To determine the "number-of-digits" properly, it is important to understand decimal places and place value. The image below shows three digits to the left of a decimal point, and three digits to the right. The place value of each position is listed below the numbers. Don't get "tens" confused with "tenths" and "hundreds" confused with "hundredths" - the place values to the right of the decimal point end in "ths."

The numbers shown in the digit boxes are the "number-of-digits" you would specify in the function to have Excel round to that decimal place or place value.

Let's look at 3 pairs of examples where the value of cell A3 is 7283.518:

MROUND

The MROUND returns a number that is rounded to the nearest instance of a specified multiple. A multiple of a number is another number that can be divided into evenly by the multiple. For example, some of the multiples of 10 are: 10, 20, 30, 40, etc., because 10 divides into these numbers evenly. The syntax of the MROUND function is:

MROUND(number,multiple)

… where number is the number to round, and multiple is the multiple to which the number should be rounded.

The key to the MROUND function is that Excel rounds to the nearest multiple. MROUND rounds up the number if, when dividing the number by the multiple, the remainder is greater than or equal to .5. In other words, Excel rounds up if the number is halfway or more to the next multiple. It may be clearer if we look at some examples.

EVEN

The EVEN function rounds UP the specified value to the nearest even integer. This function is useful for processing items that come in pairs or even numbers only. If the value specified in the function is not a number, Excel returns the #VALUE! error. The syntax of the EVEN function is:

EVEN(number)

… where number is the value to be rounded to an even number. An Excel formula or function can be entered for the number argument.

ODD

The ODD function rounds UP the specified value to the nearest odd integer. If the value specified in the function is not a number, Excel returns the #VALUE! error. The syntax of the ODD function is:

ODD(number)

… where number is the value to be rounded to an odd number. An Excel formula or function can be entered for the number argument.

TRUNC

The TRUNC function truncates a number by removing any decimal or fractional portion of the number. The syntax of the TRUNC function is:

TRUNC(number,number-of-digits)

… where number is the value you want truncated, and number-of-digits is the decimal place or place value where the truncation should occur. The default value for number-of-digits is 0 (zero) and can be omitted from the function.

Let's look at a few examples.

In Conclusion

Don't forget that each Excel function begins with an equal = sign unless the function is imbedded inside of another function. In addition, it is advisable to lock any cell containing a formula or function, and then protect the worksheet to prevent accidental erasure. Instructions for locking cells and protecting worksheets are provided in the tutorial Excel Made Easy: A Beginner's Guide.

We hope this article has been helpful. Cheers!

Return to the top of Other Rounding Functions in Microsoft Excel: ROUNDUP, ROUNDDOWN, MROUND, EVEN, ODD, and TRUNC