Microsoft Office Tutorials and References

In Depth Information

=MROUND(A1,1/8)

Rounding currency values

Often, you need to round currency values. For example, you may need to round a dollar amount to the nearest

penny. A calculated price may be something like $45.78923. In such a case, you'll want to round the calculated

price to the nearest penny. This may sound simple, but there are actually three ways to round such a value:

• Round it up to the nearest penny.

• Round it down to the nearest penny.

• Round it to the nearest penny (the rounding may be up or down).

The following formula assumes that 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 re-

turns
$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 con-

tains 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

Working with fractional dollars