Excel track · Exercise 31 of 86 Paid
ROUNDUP and ROUNDDOWN: one direction only
Choose the direction on purpose, so the count follows the job.
Intermediate
Three items and a crate size
Read-only data · screen 1 of 2 · answer in B6
In B6, work out the crates needed: twelve units to a crate, and a part crate still ships.
| A | B | |
|---|---|---|
| 1 | Item | Units |
| 2 | Bolts | 250 |
| 3 | Nuts | 130 |
| 4 | Washers | 96 |
| 5 | Total units | 476 |
| 6 | Crates needed |
Now in B6, count only the crates that are completely full.
| A | B | |
|---|---|---|
| 1 | Item | Units |
| 2 | Bolts | 250 |
| 3 | Nuts | 130 |
| 4 | Washers | 96 |
| 5 | Total units | 476 |
| 6 | Full crates |
Reference answer
What it does
ROUNDUP and ROUNDDOWN take the same two arguments as ROUND. The difference is the direction. ROUNDUP moves away from zero and ROUNDDOWN moves toward it, so at 0 decimals the answer is the next whole crate up or the last whole crate down.
Here 476 units at twelve to a crate is 39.67. ROUNDUP(B5/12,0) answers 40, because the part crate still ships. ROUNDDOWN answers 39 and leaves 8 units behind.
ROUND would answer 40 on this sheet and 39 as soon as the total fell below 474, so the same formula changes direction when the data moves. Pick the function from what the number is for: crates to order, or crates that are full.
Syntax
=ROUNDUP(number, digits) and =ROUNDDOWN(number, digits)Worked example
On Three items and a crate size, =ROUNDUP(B5/12,0) returns 40.
=ROUNDUP(B5/12,0)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The digits argument is text, or a range was passed where a single number belongs. |
#NAME? | The name is typed ROUND-UP or ROUND_UP, and the function is one word. |
Wrong direction | ROUNDDOWN returns 39 and leaves 8 units unshipped, where the order needs 40 crates. |
At work
A parts order packed in crates of twelve, where a part crate still costs a full one.