Round to Nearest Multiple (Up or Down or Both)

Welcome to our blog post on rounding to the nearest multiple in Excel. In this tutorial, we will explore three different functions that can assist you in rounding numbers up or down to the nearest multiple: FLOOR, CEILING, and MROUND. Whether you need to calculate the number of product bundles or adjust sales targets, these functions will come in handy. So let’s dive right in and learn how to use them!

Video

Walkthrough

In this tutorial, we will learn how to:

1. Use the FLOOR function to calculate the number of product bundles needed based on order quantities.
2. Utilize the CEILING function to determine the number of bundles required to cover an entire order.
3. Apply the MROUND function to round sales targets up or down to the nearest five.

Let’s take it one exercise at a time.

Exercise 1: Rounding Down to the Nearest Multiple

Let’s begin by working on the task of calculating the number of product bundles needed based on order quantities. For this example, let’s assume that products are only sold (a) individually or (b) in discounted five-pack bundles. We have received a bunch of orders. The order quantities are shown below:

Since the FLOOR functions rounds down to the nearest multiple, we can use it to round the order quantity down to the nearest multiple of 5. Since we sell 5-pack bundles, this will tell us how many items we can deliver with 5-pack bundles.

=FLOOR(B13,5)

• B13 is the number to round
• 5 is the significance (multiple)
• Note: Depending on your Excel version, you may have FLOOR or FLOOR.MATH. Use FLOOR.MATH if available, otherwise use FLOOR.

Drag the formula down the column to apply it to all order quantities.

Now we see the order quantities that can be filled with the 5-pack bundles. Computing the number of singles needed to fulfill the order is straightforward. We use a subtraction formula like this:

=B13-C13

We fill that formula down:

Finally, we need to convert the FLOOR column quantites to the actual number of 5-packs. To do so, we can use a basic division formula like this:

=C13/5

Fill the formula down, and bam:

Now we can see how many bundles and how many singles are needed to fulfill the order quantity!

But, what if we only sold 5-pack bundles? How many bundles would be needed to fill the entire order? Well, that leads us to our next exercise.

Excel Exercise 2: Rounding Up to the Nearest Multiple

For this exercise, let’s say we only sell the product in five-pack bundles, and we need to calculate the number of bundles required to cover the entire order quantity.

Same order quantities as before:

This time, we’ll use the CEILING function to round the order quantity up to the nearest 5. This will tell us the quantity needed to cover the order. So, we write the following:

=CEILING(B13, 5)

• B13 is the quantity
• 5 is the significance (the multiple)
• Note: if you have a version of Excel with the CEILING.MATH function, use it; otherwise, use the legacy CEILING function.

Now, we just need to convert the CEILING quantity to the actual number of 5-pack bundles. We can do that with the following:

=C13/5

Fill it down, and got it:

So, we know how to force Excel to round up with CEILING, and we know how to force it to round down with FLOOR, but what if we wanted it to round up or down as needed? Well, that takes us to the next exercise.

Exercise 3: Rounding to the Nearest Multiple with MROUND

In this exercise, we want to round sales targets to the nearest multiple of five, based on a percent increase.

The prior quarter sales and growth target % are provided:

First, let’s compute the new sales target with the following formula:

=B12*(1+C12)

This formula multiplies the sales q by 110% and the results are shown below:

But, rather than use this result as the target, let’s clean it up by having Excel round it up or down to the nearest multiple of 5. For this, we can use the MROUND function as follows:

=MROUND(D12,5)

• D12 is the number to round
• 5 is the multiple

Fill it down, and got it:

Yay … we did it!

Conclusion

Congratulations! You have learned how to round numbers to the nearest multiple in Excel by utilizing the FLOOR, CEILING, and MROUND functions. Whether you need to calculate product bundles or adjust sales targets, these functions provide a simple and efficient solution. If you have any improvements, questions, suggestions, or alternatives … please post a comment!

Sample file

FAQs

Q: What if I have an older version of Excel that does not have the newer functions (CEILING.MATH or FLOOR.MATH)?

A: Don’t worry! You can still use the Legacy CEILING and FLOOR functions, which serve the same purpose.

Q: Can I change the multiple values in the exercise examples?

A: Absolutely! You can adjust the multiple values to match your specific requirements. For example, instead of rounding to the nearest five, you can round to the nearest ten or any other desired multiple.

Q: How can I round to a specific decimal place instead of a whole number multiple?

A: To round to a specific decimal place, you can use the ROUNDUP, ROUNDDOWN, or ROUND functions instead of CEILING, FLOOR, or MROUND. Simply adjust the formula to include the desired number of decimal places.