Hi - I am trying to calculate the overall price based on a tiers of discounts:
<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>
This would be a tiered discount model - so the first 7,500 are sold at list price, the 7501st unit up to 15,000 are at 3% discount etc. It's not the case that buying 14,000 units would see all 14,000 at a 3% discount, the first 7500 would still be at 0% discount.
I've tried many times to resolve this using SUMPRODUCT from other examples and just can't get it to work! Anyone that can help would be much appreciated.
Thanks
L
Lower Band | Upper Band | List Price | Discount% | Discounted Price |
0 | 7,500 | £10.00 | 0% | £10.00 |
7,501 | 15,000 | £10.00 | 3% | £9.70 |
15,001 | 25,000 | £10.00 | 6% | £9.40 |
25,001 | 35,000 | £10.00 | 9% | £9.10 |
35,001 | 40,000 | £10.00 | 12% | £8.80 |
40,001 | 50000+ | £10.00 | 15% | £8.50 |
<colgroup><col><col><col span="2"><col></colgroup><tbody>
</tbody>
This would be a tiered discount model - so the first 7,500 are sold at list price, the 7501st unit up to 15,000 are at 3% discount etc. It's not the case that buying 14,000 units would see all 14,000 at a 3% discount, the first 7500 would still be at 0% discount.
I've tried many times to resolve this using SUMPRODUCT from other examples and just can't get it to work! Anyone that can help would be much appreciated.
Thanks
L