I'm trying to find out if there is a formula to calculate the monthly billing fee based on the below table.
I receive monthly reports from a customer. The below table shows the billing rate per product at each threshold level.
Below the table is an example of how it is supposed to be calculated.
Starting with 9000 units of Product A and 1000 units from Product B bill at their respective first threshold rates, and then so on with Product C, D,..H.
I'm familiar with using Sumproducts to discount from the total at different levels, but that would only work on one product. I don't know how I would go about writing a formula for these many products.
I would very much appreciate help with this problem, I would waste too much time at work figuring it out on my own and this is the only customer that has their payment like this.
I receive monthly reports from a customer. The below table shows the billing rate per product at each threshold level.
Below the table is an example of how it is supposed to be calculated.
Starting with 9000 units of Product A and 1000 units from Product B bill at their respective first threshold rates, and then so on with Product C, D,..H.
I'm familiar with using Sumproducts to discount from the total at different levels, but that would only work on one product. I don't know how I would go about writing a formula for these many products.
I would very much appreciate help with this problem, I would waste too much time at work figuring it out on my own and this is the only customer that has their payment like this.
| Book1 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | |||
| 4 | Thresholds | Product A | Product B | Product C | Product D | Product E | Product F | Product G | Product H | ||
| 5 | 1 to 10,000 | $1.35 | $1.21 | $1.18 | $1.09 | $1.02 | $0.87 | $0.82 | $0.71 | ||
| 6 | 10,001 to 50,000 | $1.28 | $1.15 | $1.12 | $1.03 | $0.97 | $0.83 | $0.81 | $0.67 | ||
| 7 | 50,001 to 100,000 | $1.22 | $1.09 | $1.07 | $0.98 | $0.92 | $0.79 | $0.77 | $0.64 | ||
| 8 | 100,001 to 250,000 | $1.16 | $1.03 | $1.01 | $0.93 | $0.87 | $0.75 | $0.73 | $0.61 | ||
| 9 | 250,001 to 500,000 | $1.10 | $0.98 | $0.96 | $0.89 | $0.83 | $0.71 | $0.69 | $0.58 | ||
| 10 | 500,001 to 750,000 | $1.40 | $0.93 | $0.92 | $0.84 | $0.79 | $0.68 | $0.66 | $0.55 | ||
| 11 | 750,001 to 1,000,000 | $0.99 | $0.89 | $0.87 | $0.80 | $0.75 | $0.64 | $0.62 | $0.52 | ||
| 12 | 1,000,001 to 3,000,000 | $0.94 | $0.84 | $0.82 | $0.76 | $0.71 | $0.61 | $0.59 | $0.50 | ||
| 13 | |||||||||||
| 14 | Sample Fee Calculation: | ||||||||||
| 15 | Product A – 9000 units | ||||||||||
| 16 | Product B – 12000 units | ||||||||||
| 17 | Product C – 5000 units | ||||||||||
| 18 | Product D – 10000 units | ||||||||||
| 19 | Product E – 2000 units | ||||||||||
| 20 | Product F – 7000 units | ||||||||||
| 21 | Product G – 1000 units | ||||||||||
| 22 | Product H – 25000 units | ||||||||||
| 23 | |||||||||||
| 24 | Fee Calculation = (1.35×9000(A)) + (1.21×1000(B)) + (1.15×11000(B)) + (1.12×5000(C)) + (1.03×10000(D)) + (.97×2000(E)) + (.83×7000(F)) + (.81×1000(G)) + (.67×4000(H)) + (.64×21000(H)) = $66,590.00 | ||||||||||
Sheet1 | |||||||||||
