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:

➤ Round it up to the nearest penny.

➤ Round it down to the nearest penny.

➤ 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.MATH 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.MATH(A1,0.01)

To round down a dollar value, use the FLOOR.MATH 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.MATH(A1,0.01)

To round up a dollar value to the nearest nickel, use this formula:

=CEILING.MATH(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.