Rounding to a step with FLOOR and CEILING

1 / 5

For the sale, every price is cut down to a multiple of £5: £47 becomes £45, and £50 stays £50.

keyboard Typing formulas is easiest on a computer with a keyboard.

A1
ABCD
1ItemPriceSale price
2Gaiters11
3Rucksack33
4Head torch92
5Tent72
6Water filter54
7Walking boots60
8Sleeping bag94
9
10
11
12
price
sale price
Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.
Study notes

How FLOOR and CEILING and MROUND works

  • What do FLOOR and CEILING do?

    They round to a multiple of a number you choose, instead of to decimal places:

    • =CEILING(43,10) rounds up to a multiple of 10: 50.
    • =FLOOR(47,5) rounds down to a multiple of 5: 45.

    A number that is already a multiple stays as it is.

  • When are they useful?

    Whenever things come in fixed sizes: chairs in stacks of 10, eggs in boxes of 6, prices that end in 0 or 5, meetings booked in 15-minute slots. The step can be a decimal too: =CEILING(B2,0.05) rounds a price up to the next 5p.

  • How are they different from ROUNDUP?

    ROUNDUP and ROUNDDOWN work in decimal places: ROUNDUP(43,-1) gives 50, but there is no way to say "multiples of 6". CEILING and FLOOR take any step.

  • What about the nearest multiple?

    MROUND goes to whichever multiple is closest: =MROUND(43,5) is 45, =MROUND(42,5) is 40.

Know someone fighting with a spreadsheet? Send them this page.