When we do calculations in Excel, there is often a need to round up the result.
Let’s say you are working on multiple construction projects, and you want to estimate the total number of cement bags that you need to order for each project.
Since the cement bags come in a fixed size (2 KG or 5 KG), you will have to round up the cement requirements to calculate the total number of bags that should be ordered.
Let me show you some simple formulas you can use to quickly round up to the nearest whole numbers in Excel.
Round Up to the Nearest Whole Number
Below I have a data set that shows cement requirements for each project.
As we are buying cement in 1 Kg bags, we need to round the numbers up to the nearest whole numbers.
We can apply the ROUNDUP function for this.
Below is the formula that will round up the cement requirement to the nearest whole number.
=ROUNDUP(A2,0)
The syntax of the ROUNDUP function is ROUNDUP(Number, Number of digits).
According to this syntax, the number is the number that we need to round up.
In this case, the respective cement requirement in column A is the number for the function. The number of digits means the required number of decimal places in the rounded-up value.
As we want to round up to the nearest whole number with 0 digits. So, In this case, the number of digits is 0.
In this example, we have used the ROUNDUP function as we want to know how many bags of cement we should order without falling short of our demand. So even if the demand is 125.1 kg, we will need to order 126 bags. In case you have the flexibility where you want to order fewer bags as well, you can use the ROUND function, which will round up to the next whole number if the decimal portion is more than or equal to 0.5, and round down to the previous whole number in case the decimal value is less than 0.5
Also read: How to Round Numbers without using Formulas?
Round Up to the Nearest Whole Number (Multiple of 3 or 5)
Let’s imagine we need to find how many kg of cement to order with 5 Kg bags for the below data set.
At this point, we need to round the number up to the closest significant whole number – 5.
Then we can use the CEILING.MATH function to find a solution.
Below is the formula that will round up the cement requirement to the nearest 5 KG.
=CEILING.MATH(A2,5)
The syntax of the CEILING.MATH function is CEILING.MATH(Number, Significance, Mode).
According to this syntax, the number is the number that we need to round up. In this scenario, cement requirements are the numbers. The significance is the multiple to which we need to round up the number.
We have entered 5 as the significance because we have to round up to the nearest multiple of 5.
When we use CEILING.MATH function with a positive value as the first integer, it is going to round up the value to the next multiple of the second argument (which was 5 in the above example).
However, when we use this function with a negative value as the first argument, it will move towards 0 and round up the number to the multiple of the second argument.
For example, if we use =CEILING.MATH(125.1,5). it will give us 130, and if we use =CEILING.MATH(-125.1,5), it will give us -125.
Also read: Round to the Nearest Tenth in Excel
Round Up to the Nearest Even Whole Number
Let’s imagine we need to estimate how many kg of cement to order with 2 Kg bags for the below data set.
Because 2 is an even number, we can use the EVEN function to round it up.
Enter the below formula that will round up the cement requirement to the nearest 2 KG in cell B3 and copy it to the below cells.
=EVEN(A2)
The syntax of the EVEN function is EVEN(Number).
According to this syntax, the number is the number that we need to round up to the nearest even number.
So, in this case, we use the cement requirement as the number for the EVEN function.
A few things to note when using the EVEN function:
- There will be no rounding up if the value is already an even number.
- The EVEN function ignores the number’s sign. Consideration is given only to the number’s absolute value. Assume we use the EVEN function on -125.67. Then our answer will be -126. There is actually a round down because -126 is less than -125.67. Therefore, it is not possible to round negative values up to the next whole even number.
Round Up to the Nearest Odd Whole Number
Let’s imagine we can buy cement in 2 Kg bags, and we have to buy an additional 1 Kg cement bag.
Now, we need to find how many kg of cement to order with 2 Kg bags + 1 Kg bag for the below data set.
In this case, we must round the number up to the next odd number.
To do this, we can use the ODD function to round up the number.
Below is the formula that will round up the cement requirement to the nearest odd number.
=ODD(B3)
The syntax of the ODD function is ODD(Number).
According to this syntax, the number is the number that you need to round up to the nearest odd number.
This function will round up the value to the nearest multiple of odd integers.
Note that if the number is already an odd number, there will be no rounding up.
The ODD function, like the EVEN function, ignores the sign of the number. Consideration is given only to the number’s absolute value.
For example, using the ODD function for the value -125.67 would answer -127.
This is not a round-up, but a round-down, as -127 is less than -125.67.
This means negative values cannot be rounded up to the next odd whole number.
In this article, I showed you how to use a couple of simple Excel functions to round up to the next whole number in Excel quickly.
If you want to round to the next positive whole number, you can use the ROUNDUP function, where the second argument needs to be 0.
I’ve also covered how you can around to the next number when it’s a multiple of three or five or any other number using the CEILING.MATH function.
And in case you want to round up to the next whole number, which should either be an even number or an odd number, then you can use the EVEN or ODD functions in Excel.
Other Excel articles you may also like:
