Lookup & Reference
Group numbers into uneven custom brackets (Deal size & order value cohorts)
Assign nonnegative deal values to custom lower-inclusive, upper-exclusive tiers, then summarize amounts by tier.
Why FLOOR/MROUND only work for equal intervals—and VLOOKUP handles uneven tiers!
Uniform rounding functions suit equal-width groups. For this example use custom tiers [0,5000), [5000,25000), [25000,100000), and [100000,infinity). These business labels are illustrative, not universal customer classifications.
Approximate VLOOKUP requires strictly ascending, unique numeric lower thresholds. This example validates the fixed four-row threshold table. Values exactly at a threshold enter the higher tier; decimal amounts just below it remain in the lower tier. Extend the formula ranges explicitly when adding records or tiers.
| Tier Floor (E) | Assigned Cohort Label (F) | Matching Sample Deals | Cohort ARR Sum |
|---|---|---|---|
| $0 | "1. SMB (<$5k)" | $1,800 | $1,800 |
| $5,000 | "2. Mid-Market [5k,25k)" | $12,000 + $8,000 | $20,000 (2 deals) |
| $25,000 | "3. Commercial [25k,100k)" | $45,000 | $45,000 |
| $100,000 | "4. Enterprise ($100k+)" | $125,000 | $125,000 |
Assign each deal to its uneven bracket cohort
Exact threshold hits (like $25,000) land cleanly in the new higher tier ("3. Commercial").
=IF(OR(NOT(ISNUMBER(B2)),B2<0,COUNT($E$2:$E$5)<>4,$E$2<>0,$E$3<=$E$2,$E$4<=$E$3,$E$5<=$E$4),"Invalid inputs",VLOOKUP(B2, $E$2:$F$5, 2, TRUE))Summarize cohort revenue and deal count
Sum the supplied ARR amounts for a chosen cohort. Validate amounts and the threshold table before using the total; this is a fixed five-record example.
=IF(OR(COUNT(B2:B6)<>5,MIN(B2:B6)<0,COUNTIF(C2:C6,"Invalid inputs")>0),"Invalid inputs",SUMIFS(B2:B6, C2:C6, "2. Mid-Market [5k,25k)"))Try the example
Use the original values or change them to test your understanding.