Excel offers a multitude of ways to round numbers up or down based on quantities other than a base 10 number. Here we’ll use the CEILING, FLOOR, MOD, MROUND and IF functions to come up with various ways to achieve our goals. Let’s take a look!
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Our scenario is that we have 8 products that we use or purchase or package in quantities of 72 (or whatever number you may be constrained by). We need to determine if we have to purchase more to increase our quantities so they are evenly divided by our number of significance, which is 72:
The CEILING function rounds a number up to the nearest multiple of significance.
The syntax is: =CEILING(number,significance)
In cell C2, I’ll enter the following formula: =CEILING(B2,72), then copy it down. When I compare it to our original values in B2:B9, you can see the amount each increased:
Conversely, if we didn’t want to add any inventory, and just wanted to reduce the quantities DOWN to a multiple of 72, we can use the FLOOR function. The FLOOR function rounds a number down to the nearest multiple of significance.
The syntax is: =FLOOR(number,significance)
In cell E2, I’ll enter the following formula: =FLOOR(B2,72), then copy it down. When I compare it to our original values in B2:B9, you can see the amount each decreased:
An alternative to the FLOOR function is to use the MOD function. The MOD function returns the remainder after a number is divided by a divisor.
The syntax is: =MOD(number, divisor)
In cell G2, Ill enter the following formula: =MOD(B2,72), then copy it down. When I compare it to our original values in B2:B9, you can see the amount remaining. In essence it is the absolute value of what the FLOOR function difference produced:
IF, MOD, CEILING and FLOOR Functions:
Now, let’s say we wanted to write a formula that would increase the amount if the requirement was equal to or less than half of our number of significance, but decrease it if it required us to obtain more than half of that number. We could use the IF function along with the three we just used to create that scenario. Our formula would be:
And the results would be as follows:
You can see that when the amount to be added was less than or equal to 36, we increased the amount, but if it was more, it was decreased.
An alternative to creating that formula is the MROUND Function. The MROUND Function returns a number rounded to the desired multiple. When the amount to be rounded up is less than or equal to half of the number, it will round it up, otherwise it will round down.
The syntax is: =MROUND(number, multiple)
Our formula will be: =MROUND(B2,72)
So when we enter that formula is J2, and copy it down, it returns the exact same number as with our previous IF formula. Column K compares those results, and column L takes those results and divides it by 72 to confirm there is no remainder left:
And there you have it – several ways you can use the functions in Excel to work with various multiples that are not a base 10.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!