Reporting & Aggregations
Percent Sold, Remaining Allocation & Portfolio Weighted Completion Formula
Calculate SKU or event-tier percent sold (Sold / Allocation) with zero-allocation protection, oversold waitlist detection, and true volume-weighted portfolio percent sold.
Guarding zero-capacity holdbacks and avoiding unweighted averages of percentages
At the row level, dividing Units_Sold by Allocated_Units (C2 / B2) throws #DIV/0! whenever an unreleased holdback tier currently has 0 allocated capacity, and exceeds 1.00 (100%) when waitlist bookings exceed initial stock.
At the summary level, taking AVERAGE(D2:D5) across tier percentages weights a 100-seat Early Bird tier identically to a 500-seat General Admission tier. True portfolio percent sold must always divide SUM(Units_Sold) by SUM(Allocated_Units).
| Tier | Allocated | Sold | Pct Sold & Status |
|---|---|---|---|
| VIP Pass | 200 | 170 | 0.8500 (85.0% -> SELLING) |
| General Tier | 500 | 500 | 1.0000 (100.0% -> SOLD_OUT) |
| Early Bird | 100 | 112 | 1.1200 (112.0% -> OVERSOLD_WAITLIST) |
| Holdback Lot | 0 | 0 | 0.0000 (UNRELEASED — guarded) |
Zero-Safe Row Percent Sold & Inventory Status Formula
Guard B2 <= 0 before dividing C2 / B2, and distinguish UNRELEASED, SELLING, SOLD_OUT, and OVERSOLD_WAITLIST.
=IF(B2 <= 0, 0, ROUND(C2 / B2, 4))True Volume-Weighted Portfolio Percent Sold
Divide total units sold SUM(C2:C5) by total allocated capacity SUM(B2:B5) rather than averaging row percentages.
=IF(SUM(B2:B5) <= 0, 0, ROUND(SUM(C2:C5) / SUM(B2:B5), 4))Try the example
Use the original values or change them to test your understanding.