Round a number down to nearest multiple
Excel allows you to round any number down to the nearest multiple. One only has to be specific with the multiple they want to be rounded down.
There are several functions by which we can round numbers to their next multiple. They include the FLOOR, MROUND, and CEILING functions.
Here are the formulas for each function. You can use any of them for your
= FLOOR (number, multiple)
= MROUND (number, multiple)
= CEILING (number, multiple)
Should you want to round a number down to the nearest multiple, the FLOOR function is the ideal function to use in Excel. The MROUND function also rounds a number. However, this is where the CEILING function differs from the other two, in the fact that it rounds up, instead of rounding down. Hence, it’s named, “ceiling”.
In the example we have chosen, we would be rounding the same number to it’s nearest multiples in different forms. See the list of numbers in the age below.
Apart from the column of Numbers, we’ve also got the column for the nearest multiples we’re calling, as well as the result for each action.
Figure 1. Excel spreadsheet showing the numbers to be rounded to the nearest multiple.
Our first multiple to be returned is “10.00”. The FLOOR function asks Excel to take our neutral number, 500.48 and round it down to the nearest multiple of 10.
As shown in the image below, the function returns 500 as the nearest multiple of 10, before 500.48.
Our formula for this function based on our example “=FLOOR(B3, C3)”
Figure 2. Excel returns the result of the FLOOR function for the multiple of 10.
We’d take a look at four more multiples and see their results from the FLOOR function.
To the nearest 0.1 multiple
We use the same FLOOR function to round the number down to the nearest 0.1 multiple.
Figure 3. Excel rounds the number down to the nearest 0.1 multiple.
To the nearest 0.25 multiple.
Again, we use the FLOOR function to round 500.48 down to the nearest multiple. Only that this time, our multiple is 0.25.
The result is 500.25.
Figure 4. Excel rounds the number down to the nearest 0.25 multiple.
To the nearest 20.00 multiple.
The result after rounding our figure, 500.48, down to the nearest multiple of 20.00 gives us 500. This, as you may have noticed, is the same value as the multiple of 10. It is so because when rounding the figure down, Excel only takes the nearest multiple of a particular number.
Figure 5. Excel rounds the number down to the nearest multiple of 20.00
- We mentioned earlier that we can use three different functions to round numbers in Excel. Here’s a clearer recap of what each function does exactly.
- FLOOR – rounds a number down to the nearest multiple.
- MROUND – rounds a number to the nearest multiple.
- CEILING function – rounds a number up to the nearest multiple.