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
Search JabSto ::




Custom Search