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.