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.