Formula Excel : Basic Rounding of Numbers

Often, your customers want to look at clean, round numbers. Inundating a user with decimal values and unnecessary digits for the sake of precision can actually make your reports harder to read. For this reason, you may want to consider using Excel’s rounding functions.

Image 2-10 illustrates how the number 9.45 is affected by the use of the ROUND, ROUNDUP, and ROUNDDOWN functions.

Image 2-10: Rounding numbers using formulas.

How it works

Excel’s ROUND function is used to round a given number to a specified number of digits. The ROUND function takes two arguments: the original value and the number of digits to round to.

Entering a 0 as the second argument tells Excel to remove all decimal places and round the integer portion of the number based on the first decimal place. For instance, this formula rounds to 94:

=ROUND(94.45,0)

Entering a 1 as the second argument tells Excel to round to one decimal based on the value of the second decimal place. For example, this formula rounds to 94.5:

=ROUND(94.45,1)

You can also enter a negative number as the second argument, telling Excel to round based on values to the left of the decimal point. The following formula, for example, returns 90:

=ROUND(94.45,-1)

You can force rounding in a particular direction using the ROUNDUP or ROUNDDOWN functions.
This ROUNDDOWN formula rounds 94.45 down to 94:

=ROUNDDOWN(94.45,0)

This ROUNDUP formula rounds 94.45 up to 95:

=ROUNDUP(94.45,0)

Categories: Formula