Formula Watch: Control rounding with CEILING and FLOOR

If you round decimal places away and want full control over which way they go (up or down), you should use the CEILING and FLOOR functions.

Here you can see the first number 38.1 is rounded down to 38 while the second percentage 42.70% is rounded up to 43%:

If you always want to round a number up, use CEILING:

=CEILING(B2,1)

This rounds 38.1 to 39.

The ",1" is the significance, which means how many steps you round - typically this would be 1, but if you have, say, a margin of error that's +5, you might want to set it to 5 to show the maximum value

Likewise, if you want to always round down, you could use FLOOR:

=FLOOR(B2,1)

This rounds 38.1 to 38.

Now if you're using CEILING or FLOOR functions with percentages, remember that these need to be multiplied by 100 (even if you use the handy Percentage formatting to convert 0.427 into 42.7%). In this case, if you want to round up you should use:

=CEILING(B2*100,1)

This rounds 42.7% to 43.

Suzanne