Challenge Scenario
In logistics and courier operations, parcel delivery pricing includes a flat base charge ($10.00 for packages up to 5 kg) plus an extra Heavy Weight Surcharge of $2.50 per kg for every kilogram exceeding the 5 kg limit.
As a Freight Logistics Coordinator, you are rating four outgoing customer shipments:
- Heavy Weight Surcharge (Column E): Calculate surcharge on excess kilograms above 5 kg using
=MAX(0, (C2-5)*2.5).
- Total Shipping Charge (Column F): Add base fee and surcharge:
=D2+E2.
- Average Package Weight (Cell C7): Compute average parcel weight using
AVERAGE(C2:C5).
- Total Shipping Revenue Collected (Cell F7): Sum total delivery charges using
SUM(F2:F5).
Your Sheet Layout
Understanding the parcel freight rating table:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:D5 |
Tracking, City, Weight, Base Fee |
Shipment Manifest |
Package weight and flat standard fee ($10) |
Read-only parcel manifest. |
E2:E5 |
Surcharge ($) |
Heavy Surcharge |
=MAX(0, (C2-5)*2.5) |
Charges $2.50/kg for weight above 5kg. |
F2:F5 |
Total Charge ($) |
Final Invoice |
=D2+E2 |
Calculates final payable delivery fee. |
C7 |
Average Weight |
Summary |
=AVERAGE(C2:C5) |
Average parcel weight (7.25 kg). |
F7 |
Total Shipping Revenue |
Summary |
=SUM(F2:F5) |
Total freight billings ($65.00). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells E2:E5, calculate heavy weight surcharge (= MAX(0, (Weight - 5) * 2.5)).
2
In cells F2:F5, calculate total shipping charge (= Base Fee + Surcharge).
3
In cell C7, calculate average package weight using AVERAGE.
4
In cell F7, sum total freight shipping revenue using SUM.
Solution & Formula Breakdown
Let's review the parcel freight rating logic:
1. Heavy Weight Surcharge with MAX (E2:E5)
=MAX(0, (C2-5)*2.5)
- PKG-701 (4 kg):
MAX(0, (4-5)*2.5) = MAX(0, -2.5) = $0.00.
- PKG-702 (8 kg):
MAX(0, (8-5)*2.5) = 3 kg × $2.50 = $7.50.
- PKG-703 (12 kg):
MAX(0, (12-5)*2.5) = 7 kg × $2.50 = $17.50.
- PKG-704 (5 kg):
MAX(0, (5-5)*2.5) = 0 kg × $2.50 = $0.00.
2. Total Shipping Charge (F2:F5)
=D2+E2
- PKG-701: $10.00 base + $0.00 = $10.00.
- PKG-702: $10.00 base + $7.50 = $17.50.
- PKG-703: $10.00 base + $17.50 = $27.50.
- PKG-704: $10.00 base + $0.00 = $10.00.
3. Summary Totals (C7 & F7)
- Cell C7 (Average Weight):
=AVERAGE(C2:C5) → 7.25 kg.
- Cell F7 (Total Freight Revenue):
=SUM(F2:F5) → $65.00.
Using MAX(0, ...) ensures that packages weighing less than 5 kg never produce a negative surcharge that reduces the base delivery fee!