Chapter 14: Using Formulas for Matching and Lookups. . . . . . . . . . . . . . . . . . . . . . . . .
10
Rounding Numbers
Oftentimes, your customers will 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 consider using Excel’s rounding
functions.In this section, you’ll explore some of the techniques that you can leverage to apply round-
ing to your calculations.Rounding numbers using formulas
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 number of digits to round to.Passing 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)Passing 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 pass a negative number to 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
function.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)Rounding to the nearest penny
In some industries, it is common practice to round a dollar amount to the nearest penny.
Figure 10.10 demonstrates how rounding a dollar amount up or down to the nearest penny
can affect the resulting number.