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