Microsoft Office Tutorials and References
In Depth Information
Rounding currency values
Rounding currency values
Often, you need to round currency values. For example, a calculated price might be a number like
$45.78923. In such a case, you want to round the calculated price to the nearest penny. This
process might sound simple, but you can round this type of value in one of three ways:
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 can 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 up the value to the nearest penny, use the CEILING function. The following
formula rounds up the value in cell A1 to the nearest penny (if, for example, cell A1 contains
$12.421, the formula returns $12.43):
=CEILING(A1,0.01)
To round down a dollar value, use the FLOOR function. The following formula, for example,
rounds down the dollar value in cell A1 to the nearest penny (if cell A1 contains $12.421, the
formula returns $12.42):
=FLOOR(A1,0.01)
To round up a dollar value to the nearest nickel, use this formula:
=CEILING(A1,0.05)
Using the INT and TRUNC functions
On the surface, the INT and TRUNC functions seem similar. Both convert a value to an integer.
The TRUNC function simply removes the fractional part of a number. The INT function rounds
down a number to the nearest integer, based on the value of the fractional part of the number.
 
Search JabSto ::




Custom Search