Microsoft Office Tutorials and References

In Depth Information

**Rounding Out Your Knowledge**

placeholders in how they work. For example, =ROUND(B4,-3) tells the

ROUND function to round on the thousandth position. On the other hand,

FLOOR and CEILING can round to whatever specific multiple you set.

The FLOOR function rounds toward 0, returning the closest multiple of the

second argument that is lower than the number itself.

The CEILING function works in the opposite direction. CEILING will round its

first argument, the number to be rounded, to the next multiple of the second

number that is in the direction away from 0.

Certainly, a few examples will make this clear! Table 7-3 shows ways that

FLOOR and CEILING can be used.

Table 7-3 Using FLOOR and CEILING for Sophisticated Rounding

Example of Function

Result

Comment

=FLOOR(30.17,0.05)

30.15

The second argument says to

round to the next 0.05 multiple, in

the direction of 0.

=FLOOR(30.17,0.1)

30.1

The second argument says to

round to the next 0.1 multiple, in

the direction of 0.

–30.1

The second argument says to

round to the next 0.1 multiple, in

the direction of 0.

=FLOOR(-30.17,-0.1)

30.2

The second argument says to

round to the next 0.05 multiple,

away from 0.

=CEILING(30.17,0.05)

30.2

The second argument says to

round to the next 0.1 multiple,

away from 0.

=CEILING(30.17,0.1)

=CEILING(-30.17,-0.1)

–30.2

The second argument says to

round to the next 0.1 multiple,

away from 0.

FLOOR and CEILING can be used to round negative numbers. FLOOR rounds

toward 0, and CEILING rounds away from 0. FLOOR decreases a positive

number as it rounds it toward 0 and also decreases a negative number

toward 0, although in absolute terms, FLOOR actually increases the value of

a negative number. Weird, huh?