Excel track · Exercise 32 of 86 Paid
MROUND and FLOOR: round to a multiple
Round to a pack size or a price step instead of to a number of decimals.
Intermediate
Pallet counts and order lines packed in sixes
Read-only data · screen 1 of 2 · answer in C2
Round each pallet's unit count to the nearest five and put the first one in C2.
| A | B | C | |
|---|---|---|---|
| 1 | Pallet | Units | Nearest 5 |
| 2 | P-01 | 47 | |
| 3 | P-02 | 92 |
Only whole packs ship, so put the units that fit the full packs of the first order into D2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Units | Pack | Shipped |
| 2 | A-10 | 47 | 6 | |
| 3 | A-11 | 92 | 6 |
Reference answer
What it does
MROUND takes a number and the multiple to land on, so =MROUND(B2,5) asks which multiple of five is nearest to 47. The answer is 45, because 45 is two away and 50 is three away. FLOOR takes the same second argument and always moves down.
The second screen packs in sixes and ships only full packs. =FLOOR(B2,C2) returns 42, which is seven packs, and the five units left over do not fill another. MROUND in that column would return 48 and bill for a pack that was never filled.
Keep the pack size in a cell, not in the formula. A supplier moving from sixes to eights changes one value, where a typed 6 has to be found in every row. MROUND also needs the number and the multiple to share a sign, or it returns #NUM!.
Syntax
=MROUND(number, multiple) and =FLOOR(number, significance)Worked example
On Pallet counts and order lines packed in sixes, =MROUND(B2,5) returns 45.
=MROUND(B2,5)Common errors
| Error | What it means and how to fix it |
|---|---|
#DIV/0! | The multiple is zero, and zero gives MROUND no grid of multiples to land on. |
#NUM! | The number and the multiple have different signs, so MROUND cannot choose a multiple between them. |
Wrong direction | MROUND where FLOOR is needed returns 48 and promises a pack nobody filled. |
At work
A warehouse that ships whole packs, and a price list quoted in five-pence steps, where the multiple is part of the calculation.