In the previous excel tutorial, we discussed how to round decimal numbers. Well, now we will discuss about how to round numbers to certain multiples in Microsoft Excel. For example rounding numbers to the nearest tens, rounding numbers to the nearest hundreds, or rounding to the nearest thousand in excel.
For the need for rounding numbers like this, there are actually many ways that we can do it with Microsoft Excel. One of them is by utilizing the basic functions provided by excel to be compiled into a specific excel formula according to our needs.
Among the many excel functions, excel functions that are specifically for rounding numbers to certain multiple values are the MROUND function, CEILING function, and the FLOOR function.
Then how to use the three excel functions in Microsoft Excel formulas?
This time the excel class will invite you to learn about how to use the MROUND, CEILING and FLOOR functions in Microsoft Excel formulas.
Excel’s MROUND function
In Microsoft Excel formulas, the MRound function is an excel function that is used to round numbers or numbers to the nearest value of a multiple of a certain number.
How to Use Excel’s MROUND Function
Here’s how to write or use the MRound function in excel formulas:
Code:
MROUND(Number; Multiple)
The “Number” argument in the Mround formula is filled with a number value that we will round, it can be a specific number value or a cell reference in an excel worksheet that contains a certain number value.
While “Multiple” we fill with numbers that are multiples of these numbers will be used as a rounding reference.
Example of Excel’s MROUND Function
For an example of using the Mround function in an excel formula, please see the following image.
In the first example above the excel formula used is
Code:
=MROUND(B2;10)
The formula above will round the number to the nearest tens. Similarly, the examples in numbers 2 and 3. Each will round the specified number to the nearest multiple of 10.
To round a number to the nearest hundreds, replace the number 10 or 500 in the excel formulas with the number 100. If you want to round to the nearest thousand, fill in the multiple arguments in the MRound function with the number 1000.
It should be noted that in the MRound function the rounding of multiples can be up or down. This will be determined from the residual value of the result of dividing the number by the desired multiple values.
If the residual value is less than half the expected multiple then MRound will round the number down. And vice versa if the residual value is more than or equal to half the multiple values it will be rounded up.
In example number 5 the MRound formula used is:
Code:
=MROUND(B6;500)
Cell B6 which has a value of 4100 when divided by 500 will leave the number 100. Half of the divisor (500) is 250. Since 100 is less than 250, the number 4100 will be rounded to the nearest multiple below 4100, which is 4000.
Code:
=MROUND(B8;500)
Whereas in example number 7 the residual value is 400. So the number 4400 will be rounded up to the nearest multiple of 500, which is 4500. because the value of 400 is greater than half the value of the divisor (500).
When using the MRound function, make sure that the number symbol (positive/negative) in the number value to be used is the same as the multiple values. Otherwise, the MRound function will return the #NUM class value.
Consider the following example of the MROUND formula in numbers 5 and 6:
Excel CEILING Function
In excel formulas, the Ceiling function is an excel function that rounds a number to certain multiple values. The difference is, if MRound rounds up or down according to the remainder of the division, the Ceiling function in excel will always round the value up from the specified multiple values.
How to Use Excel’s CEILING Function
In the excel formula, the rules for writing the Ceiling function follow the following syntax:
Code:
CEILING(Numbers; Multiples)
“Number” is the value of the number that we will round, while “Multiple” is the value of the closest multiple that will be used in rounding.
If the number to be rounded is the same as the intended multiple, then Ceiling will not do the rounding, but will produce the value of the number (fixed).
Excel CEILING Function Example
An example of using the Ceiling function in an excel formula is shown in the following image.
The first formula used in the example is:
Code:
=CEILING(B2;10)
The excel formula above will round up the numbers in cell B2 and above to the nearest tens or multiples of 10.
In example number 4:
Code:
=CEILING(B5;10)
The Ceiling function does not round numbers to the nearest ten because the value of B5, which is the number 30, is one of the multiples of 10.
Excel FLOOR function
If the Ceiling function in excel will always round the value up from the specified multiple values, the FLOOR function will always round down the value from the specified multiple values.
How to Use Excel’s FLOOR Function
The method of writing the Floor function in the excel formula follows the following rules or syntax:
Code:
FLOOR(Number; Multiple)
“Number” is the value of the number that we will round, while “Multiple” is the value of the closest multiple that will be used in rounding.
Just like the Ceiling function, if the number to be rounded is the same as the intended multiple, the Floor function will not round and will only produce those numbers.
Example of Excel’s FLOOR Function
Consider the following example of using the Floor formula:
If you pay attention to the excel formulas in the example, the excel formula above rounds numbers to the nearest hundreds. But the value is always below that number. Except for example number 4 which uses the following Floor formula:
Code:
=FLOOR(B5;100)
In example number 4, the Floor formula does not round numbers, but only produces numbers to be rounded off because the number value in cell B5 is 300, which is a multiple of hundreds or a multiple of 100.
If you want a multiple of thousands, you just need to change the number in the multiples argument with the number 1000.
So easy isn’t it?
If the MRound function, as explained above, there should be no difference in number symbols, what about the Ceiling and Floor functions? Will it also return a #NUM error value?
Before answering the question above, if you want to remove the numbers behind the comma in decimal numbers, the excel formula tutorial is on the following page:
Excel formula to remove numbers after commas
Differences in Number Symbols in Excel’s MROUND-CEILING-FLOOR function
As explained earlier, when using the MROUND function, the symbols for numbers and multiples must be the same. Both positive or equally negative.
Otherwise, the MRound function returns the #NUM error value. See examples of numbers 3 and 4 below:
In the Ceiling and Floor functions, the number symbol can be different as long as the rounded number is negative. If the number value is positive while the multiple values are negative, then the same as the MRound, Ceiling, and Floor functions will produce the #NUM error message. See examples of numbers 7 and 11.
Other things to note on the Ceiling function :
- If the number is negative, and the significance is negative, the value is rounded down away from zero (Example number 6).
- If the number is negative, and the significance is positive, the value is rounded up to zero (Example number 8).
While the Floor function :
- If the number is negative, and the significance is negative, the value is rounded up to zero (Example number 10).
- If the number is negative, and the significance is positive, the value is rounded down away from zero (Example number 12).
For the case of differences in symbols above, in the latest version of excel, excel adds the functions FLOOR.MATH and CEILING.MATH.
For now, I think the discussion about the formula for rounding numbers to certain multiples is enough, next time if needed we will discuss these two functions.
You can use the above functions to round a number value to multiples of tens, hundreds, or thousands, either rounding up or down as needed.
Hope it is useful. Happy Learning Excel.
