Tiered Pricing: Logic & Arrays
I cannot figure out the logic nor the arrays necessary to calculate the tiered pricing based on the table below in a single cell. I swear it should be possible with SUMPRODUCT and some logic, but it's beyond my Excel knowledge at this time.
| Pricing Tier | Tier Min $ | Tier Max $ | Tier Fee |
|---|---|---|---|
| I | >$0 | $500,000 | 0.80% |
| II | >$500,000 | $1,000,000 | 0.60% |
| III | >$1,000,000 | $6,000,000 | 0.40% |
| IV | >$6,000,000 | $15,000,000 | 0.20% |
Thus, if a hypothetical Unit was $1,600,000, it should be priced at [$500,000 - $0] x 0.80% + [$1,000,000 - $500,000] * 0.60% + [$1,600,000 - $1,000,000] * 0.40%, or $4,000 + $3,000 + $2,400, for a total of $9,400, or 0.5875%.
I'd like to know how to calculate the pricing in a single cell both for the total $ amount and as a percentage of the Unit. This is doable, right?
Posted Feb 16, 2017, 11:23 PM UTC
28