MROUND

Returns a number rounded to the nearest multiple of another number.

Syntax:

MROUND(number, mult)

Returns number rounded to the nearest multiple of mult, that is to mult times an integer. An alternative implementation would be mult * ROUND(number/mult).

Example:

MROUND(15.5, 3)

returns 15, as 15.5 is closer to 15 ( = 3*5) than to 18 ( = 3*6).

MROUND(1.4, 0.5)

returns 1.5 ( = 0.5*3).

Application:

Inventory Management

Imagine you are a store manager who needs to order new inventory. Your supplier only sells items in cases, and each case contains a specific number of items. You have a list of your current stock and need to determine how many items to order to get as close as possible to your desired stock level, but in multiples of the case size.

Goal: Round the number of items needed to the nearest multiple of the case size.

Scenario:

Current Stock: The number of items you currently have on hand.
Desired Stock: The target number of items you want to have after the order.
Items to Order (Calculated): Desired Stock - Current Stock.
Case Size: The number of items in a single case from the supplier.
Items to Order (Rounded): The number of items you will actually order, rounded to the nearest multiple of the Case Size.

Here is a table illustrating this example:

	Item	Current Stock	Desired Stock	Items to Order (Calculated)	Case Size	Items to Order (Rounded)
	A	B	C	D	E	F
1	T-Shirts	150	220	70	12	72
2	Jeans	85	130	45	6	48
3	Socks (pairs)	215	300	85	24	96
4	Hats	45	60	15	5	15
5	Sweatshirts	60	100	40	8	40

Explanation of the MROUND Function in this Example:

T-Shirts:
- Items to order (calculated): 220 - 150 = 70.
- Case size: 12.
- The MROUND function rounds 70 to the nearest multiple of 12. The multiples of 12 are 12, 24, 36, 48, 60, 72, etc. 70 is closer to 72 than 60.
- Formula in cell E1 (assuming the table starts at A1): MROUND(D1, E1) which is =MROUND(70, 12) resulting in 72.
Jeans:
- Items to order (calculated): 130 - 85 = 45.
- Case size: 6.
- MROUND(45, 6). The multiples of 6 are 36, 42, 48. 45 is closer to 48.
- Result: 48.
Socks:
- Items to order (calculated): 300 - 215 = 85.
- Case size: 24.
- MROUND(85, 24). The multiples of 24 are 72, 96. 85 is closer to 96.
- Result: 96.

This example demonstrates how the MROUND function ensures that the final order quantity is always a valid number of full cases, preventing you from ordering a partial case from your supplier.