Dancin' on the Ceiling - Round to the Multiple of a Specific Number

Microsoft Excel - 2003, 2007, 2010

Using the ROUND, ROUNDDOWN, or ROUNDUP functions, you can round to a specific decimal or number place. In other words, you can round to pennies or hundreds, but not something like quarters or fives. The CEILING and FLOOR functions in Excel allow you to round a number to the nearest multiple of a specified number, such as quarters or fives. The only limitation is that you must choose a direction; CEILING always rounds up, and FLOOR always rounds down. Try this example:

1. Type the following in your spreadsheet:

A
1 5.03
2 3.33
3 2.99
4 4.00

2. Click in cell B1 and type: =CEILING(A1,.25)
3. Copy the formula down to B2 through B4. You should see the numbers rounded to the quarter greater than the numbers, like this:

A B
1 5.03 5.25
2 3.33 3.50
3 2.99 3.00
4 4.00 4.00

Note: If you do not see it quite like this, you may need to adjust the decimal places displayed. (Select the cells, click the Format menu, choose Cells, click the Number tab, select the Number category, select 2 decimal places, and then click OK.)

4. Now click in cell C1 and type: =FLOOR(A1,.05)
5. Copy the formula down to C2 through C4. You should see the numbers rounded to the nickel below like this:

A B C
1 5.03 5.25 5.00
2 3.33 3.50 3.30
3 2.99 3.00 2.95
4 4.00 4.00 4.00

Note: You may need to format your numbers again to see them exactly this way.

To use FLOOR or CEILING to round to a specified number to the left of the decimal place (such as rounding to the nearest 5 or 50), just use that whole number for the second part of the formula.
6. Change your numbers in Column A to look like the numbers below, change the formula in B1 to read: =CEILING(A1,25), and copy it down. Change the formula in C1 to read: =FLOOR(A1,5), copy it down, and you should see the results below.

A B C
1 503 525 500
2 333 350 330
3 299 300 295
4 400 400 400

Using these functions, you can round to a multiple of any number. One limitation is that if you have any negative numbers that you are rounding, the second argument of the function must be negative. Your formulas should look like this: =CEILING(A1,-25) and =FLOOR(A1.-5). The result is this:

A B C
1 -503 -525 -500

(Note: CEILING rounds away from zero and FLOOR rounds toward zero.)

Visual tips
Watch a video on this tip for Excel 2003

Watch a video on this tip for Excel 2007

Related tips:
Round and Round We Go (Excel) - Round a Number in Excel

Don't Turn A-Round - Rounding Numbers in a Specific Direction

Play a Round with MROUND - One Last Function for Rounding