Microsoft Office Tutorials and References
In Depth Information
Rounding Numbers
h Round it up to the nearest penny.
h Round it down to the nearest penny.
h Round it to the nearest penny (the rounding may be up or down).
The following formula assumes a dollar and cents value is in cell A1. The formula rounds the value
to the nearest penny. For example, if cell A1 contains \$12.421, the formula returns \$12.42.
=ROUND(A1,2)
If you need to round the value up to the nearest penny, use the CEILING function. The following
formula rounds the value in cell A1 up to the nearest penny. For example, if cell A1 contains
\$12.421, the formula returns \$12.43.
=CEILING(A1,0.01)
To round a dollar value down, use the FLOOR function. The following formula, for example,
rounds the dollar value in cell A1 down to the nearest penny. If cell A1 contains \$12.421, the
formula returns \$12.42.
=FLOOR(A1,0.01)
To round a dollar value up to the nearest nickel, use this formula:
=CEILING(A1,0.05)
You’ve probably noticed that many retail prices end in \$0.99. If you have an even dollar price and
you want it to end in \$0.99, just subtract .01 from the price. Some higher-ticket items are always
priced to end with \$9.99. To round a price to the nearest \$9.99, first round it to the nearest
\$10.00 and then subtract a penny. If cell A1 contains a price, use a formula like this to convert it
to a price that ends in \$9.99:
=ROUND(A1/10,0)*10–0.01
For example, if cell A1 contains \$345.78, the formula returns \$349.99.
A simpler approach uses the MROUND function:
=MROUND(A1,10)–0.01

Search JabSto ::

Custom Search