Challenge Scenario
In warehouse distribution centers and freight storage facilities, keeping storage racks safely within their weight and box volume limits is essential for workplace safety and smooth logistics operations.
When storage bins become overloaded, warehouse supervisors must immediately flag the racks for redistribution and calculate occupancy percentages across all warehouse aisles.
As a Warehouse Logistics Assistant, you are auditing four storage racks:
- Occupancy % (Column D): Calculate the percentage capacity filled by dividing Current Boxes by Max Capacity (
Current Boxes / Max Capacity).
- Alert Status (Column E): If Current Boxes exceeds Max Capacity (
Current Boxes > Max Capacity), label as "OVERLOAD"; otherwise label as "SAFE".
- Total Boxes Stored (Cell B7): Sum all Current Boxes stored across all racks using
SUM(B2:B5).
- Overloaded Racks Count (Cell E8): Count how many racks have the
"OVERLOAD" status using COUNTIF(E2:E5, "OVERLOAD").
Your Sheet Layout
Understanding how the warehouse rack storage audit sheet is organized:
- Shelf Bins (Columns A to C): Shows Shelf Bin identifier (
Rack-A1 to Rack-B2), current box count stored, and the safe maximum box capacity.
- Occupancy & Safety Alerts (Columns D & E): Calculates capacity ratio (
Boxes / Capacity) and displays automated OVERLOAD / SAFE indicators.
- Warehouse Facility Summary (Rows 7 & 8): Aggregates total physical boxes stored (222) and counts overloaded storage bins (2).
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Shelf Bin, Current Boxes, Max Capacity |
Storage Data |
Rack IDs, current load, and limits |
Read-only warehouse capacity records. |
D2:D5 |
Occupancy % |
Calculation |
=B2 / C2 |
Calculates the ratio of boxes stored to max capacity. |
E2:E5 |
Alert Status |
Safety Alert |
=IF(B2 > C2, "OVERLOAD", "SAFE") |
Flags bins that exceed safe capacity limits. |
B7 |
TOTAL BOXES STORED |
Facility Summary |
=SUM(B2:B5) |
Calculates total boxes stored across all racks (222). |
E8 |
OVERLOADED RACKS |
Facility Summary |
=COUNTIF(E2:E5, "OVERLOAD") |
Counts overloaded storage bins (2). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In Column D (D2:D5), calculate Occupancy % by dividing Current Boxes by Max Capacity (=B2/C2).
2
In Column E (E2:E5), assign Alert Status using =IF(B2>C2, "OVERLOAD", "SAFE").
3
In cell B7, calculate total boxes stored using SUM(B2:B5).
4
In cell E8, count total overloaded racks using COUNTIF(E2:E5, "OVERLOAD").
Solution & Formula Breakdown
Let's examine how each calculation and safety flag works step by step:
1. Calculating Occupancy Ratio (D2:D5)
=B2 / C2
Divide Current Boxes by Max Capacity:
- Row 2 (Rack-A1):
45 / 50 = 0.90 → 90.0% occupancy.
- Row 3 (Rack-A2):
62 / 60 = 1.033 → 103.3% occupancy (exceeded limit).
- Row 4 (Rack-B1):
30 / 40 = 0.75 → 75.0% occupancy.
- Row 5 (Rack-B2):
85 / 80 = 1.0625 → 106.25% occupancy (exceeded limit).
2. Flagging Safety Overload with IF (E2:E5)
=IF(B2 > C2, "OVERLOAD", "SAFE")
The IF function tests if Current Boxes exceeds Max Capacity:
- If
B2 > C2 is TRUE (e.g. Rack-A2 with 62 > 60), output "OVERLOAD".
- If FALSE (e.g. Rack-A1 with 45 ≤ 50), output
"SAFE".
3. Facility Summary Rollups (B7 & E8)
- Total Boxes Stored (Cell B7):
=SUM(B2:B5) adds 45 + 62 + 30 + 85 = 222 boxes.
- Overloaded Racks (Cell E8):
=COUNTIF(E2:E5, "OVERLOAD") counts rows marked as "OVERLOAD", returning 2.
You can also check the occupancy ratio directly: =IF(D2 > 1, "OVERLOAD", "SAFE"). Any ratio greater than 1.0 (100%) represents an overloaded rack!