Formula Excel : Rounding to the Nearest Penny

In some industries, it is common practice to round a dollar amount to the nearest penny. Image 2-11 demonstrates how rounding a dollar amount up or down to the nearest penny can affect the resulting number.

Image 2-11: Rounding to the nearest penny.

How it works

You can round to the nearest penny by using the CEILING or FLOOR functions.
The CEILING function rounds a number up to the nearest multiple of significance that you pass to it. This utility comes in handy when you need to override the standard rounding protocol 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 entering a .01 as the significance tells the CEILING function to round to the nearest penny.
If you wanted to round to the nearest nickel, you could 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 that it forces a rounding down to the nearest significance. The following example function rounds 123.19 down to the nearest nickel, giving 123.15 as the result:

=FLOOR(123.19,.05)

Categories: Formula