Part II: Working with Formulas and Functions
FIGURE 10.10
Rounding to the nearest penny
You can round to the nearest penny by using the CEILING or FLOOR function.
The CEILING function will round a number up to the nearest multiple of significance that
you pass to it. This comes in handy when you need to override the standard rounding pro-
tocol with your own business rules. For instance, you can force Excel to round 123.222 to
124 by using the CEILING function with a significance of 1.
=CEILING(123.222,1)
So, passing a .01 as the significance tells the CEILING function to round up to the nearest
penny.
If you wanted to round up to the nearest nickel, you can use .05 as the significance. For
instance, the following formula returns 123.15:
=CEILING(123.11,.05)
The FLOOR function works the same way except it forces a rounding down to the nearest
significance. The following example function rounds 123.19 down to the nearest nickel,
giving you 123.15 as the result:
=FLOOR(123.19,.05)
Rounding to significant digits
In some financial reports, figures are presented in significant digits. The idea is that when
you’re dealing with numbers in the millions, there is no need to inundate a report with
superfluous numbers for the sake of showing precision down to the tens, hundreds, and
thousands place.
For instance, instead of showing the number 883,788, you could choose to round the num-
ber to one significant digit. This would mean displaying the same number as 900,000.
Rounding 883,788 to two significant digits would show the number as 880,000.
In essence, you’re deeming that a particular number’s place is significant enough to show.
The rest of the number can be replaced with zeros. This may feel like it could introduce
problems, but when dealing with large enough numbers, any number below a certain sig-
nificance would be inconsequential.
