Forum Discussion
Help with Tiered Pricing Formula
Hello! I'm looking for assistance with a creating a formula that takes a single value and calculates the cost based on tiered pricing.
Here is the pricing structure:
| Units per month | Price per Unit |
| First 0 - 500 | $0.75 |
| Next 501 - 2,500 | $0.70 |
| Next 2,501 - 10,000 | $0.65 |
| Next 10,001 - 25,000 | $0.60 |
| Next 25,001 - 50,000 | $0.55 |
| Next 50,001 - 100,000 | $0.50 |
| Next 100,001 - 250,000 | $0.45 |
| Next 250,001+ | $0.40 |
If I have 27,000 units, the total price would be $16,748.05.
Any help would be very much appreciated!
4 Replies
Let's add one more to the collection. Using several named ranges, LET and a LAMBDA to spill an array of results in one go. File attached.
A less advanced formula:
A2:A9 has been named Threshold, and C2:C9 has been named Difference.
See the attached workbook.
For such setup
it could be
=LET( units, $I3, n, XMATCH(units, to, 1), costBefore, IFERROR(SUM((TAKE(to,n-1)-TAKE(from,n-1)+1)*TAKE(price,n-1)),0), costRange, (units-INDEX(from,n)+1)*INDEX(price,n), costBefore+costRange )- Can you break down the prices for result $16,748.05?
