Excel has many great functions to allow you to round numbers but FLOOR and CEILING are two pretty cool ones.
While most functions allow you to round to a certain decimal place, these functions allow you to pick what you want to round to. If you need to round to large numbers like the nearest 100 or nearest 500, these functions can do it.
Let’s say we have these numbers below. We want to round the numbers in Column A to the nearest number in column B.
In row 2, we want to round 111 to the nearest 10. What the FLOOR function will do is round it DOWN to the nearest 10. The CEILING functions will round it UP to the nearest 10.
So the function would be:
We could also manually type the numbers in and use =FLOOR(111,10) and get the same result. But references work just as well.
See how Excel takes 111 and rounds it down to the nearest 10 which is 110.
For the CEILING function we would use:
This works similar as FLOOR, except it rounds the number UP to the nearest 10 in this case. So we get 120.
This will work to round for any instance. You can round the the nearest 50, or 100 or 372. It is up to you! See below for our final results: