USING THE ROUNDING FUNCTIONS


Excel includes several functions devoted to the task of rounding numbers by a specified amount.

Note: Don’t confuse the rounding functions with fixed formats, such as 0 and 0.00, which are available when you choose Format, Cells and click the Number tab. When you format the contents of a cell to a specified number of decimal places, you change only the display of the number in the cell; you don’t change the value. When performing calculations, Excel always uses the underlying value, not the displayed value. Conversely, the rounding functions change the values of the numbers that they operate on.

  • The ROUND, ROUNDDOWN, and ROUNDUP Functions

    The ROUND function rounds a number to a specified number of decimal places, rounding digits less than 5 down and digits greater than or equal to 5 up. It takes the arguments (number, num_digits). If num_digits is a positive number, then number is rounded to the specified number of decimal points; if num_digits is negative, the function rounds to the left of the decimal point; if num_digits is 0, the function rounds to the nearest integer.

    For example, the formula =ROUND(123.4567, –2) returns 100, and the formula =ROUND(123.4567, 3) returns 123.457. The ROUNDDOWN and ROUNDUP functions take the same form as ROUND. As their names imply, they always round down or up, respectively.

  • The EVEN and ODD Functions

    The EVEN function rounds a number up to the nearest even integer. The ODD function rounds a number up to the nearest odd integer. Negative numbers are correspondingly rounded down. For example, the formula =EVEN(23.4) returns 24, and the formula =ODD(–4) returns –5.

  • The FLOOR and CEILING Functions

    The FLOOR function rounds a number down to the nearest given multiple, and the CEILING function rounds a number up to the nearest given multiple. These functions take the arguments (number, multiple). For example, the formula =FLOOR(23.4, 0.5) returns 23, and the formula =CEILING(5, 1.5) returns 6.

  • The INT Function

    The INT function rounds numbers down to the nearest integer. For example, the formulas

    =INT(100.01)
    =INT(100.99999999)

    both return the value 100, even though the number 100.99999999 is essentially equal to 101. When a number is negative, INT also rounds that number down to the next integer. If each of the numbers in the examples were negative, the resulting value would be –101.

  • The TRUNC Function

    The TRUNC function truncates everything to the right of the decimal point in a number, regardless of its sign. It takes the arguments (number, num_digits). If num_digits isn’t specified, it’s set to 0. Otherwise, TRUNC truncates everything after the specified number of digits to the right of the decimal point.

    For example, the formula =TRUNC(13.978) returns the value 13; the formula =TRUNC(13.978, 1) returns the value 13.9.