ROUNDUP and ROUNDDOWN: rounding in one direction
When the rule is "always up" or "always down", ROUND is the wrong tool.
Task:
The courier bills every started kilogram, and the loyalty scheme gives a point per whole pound spent. For each parcel, put the billable weight in C2:C5 (always rounded up to a whole kilogram) and the points earned in E2:E5 (always rounded down).
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of ROUND
0/4The same formula in the other shapes it takes at work.
- ROUND Function
Round numbers to specific decimal places.
CoreBeginner - ROUND to the nearest thousand
A negative number of digits rounds to the left of the decimal point.
Beginner - ROUNDUP and ROUNDDOWN: rounding in one direction
When the rule is "always up" or "always down", ROUND is the wrong tool.
Intermediate - ROUND inside a calculation
Round each line where it is calculated, so the invoice adds up to the penny.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Parcel | Weight (kg) | Billable kg | Spend | Points |
| 2 | P-301 | 2.1 | 48.95 | ||
| 3 | P-302 | 4 | 112.4 | ||
| 4 | P-303 | 0.35 | 9.99 | ||
| 5 | P-304 | 7.62 | 230.5 |
What this exercise teachesMay contain the answer
ROUND would bill 2.1 kg as 2 and award 49 points for 48.95 — both against the rule. When a policy names a direction, use the function that only goes that way. ROUNDUP moves away from zero and ROUNDDOWN towards it, which only matters once negative numbers appear.