Choose a rate from quantity bands
Your goal: Find the rate attached to the largest threshold at or below each quantity, with explicit rules for missing bands and negative inputs.
Full task requirements
Fill E2:E4 with the rate for each quantity in D2:D4. Choose the largest minimum quantity less than or equal to the query, regardless of table order. Return Below minimum when no band qualifies and Review quantity for negative inputs. This selects one flat rate, not a progressive charge.
Standard quantity bandsChoose the band for D2
- Quantity
- 12 (D2)
| # | ||
|---|---|---|
| 1 | ||
| 2 | ||
| 3 | ||
| 4 | ||
| 5 | ||
| 6 |
Step 1 of 3
Choose the band for D2
How the Excel rule works
Choose the single largest minimum at or below the quantity. Table order does not decide the band. Negative quantities require review; a nonnegative query below every minimum has no band. This is one flat rate.
Inspect selected records
No records selected yet.
First selected value