Apr 05 2021 02:27 PM
What formula would I use to calculate the Total. The formula has to apply the discounts levels.
Seats | Price per seat | Total |
1,100 | $10.00 | |
2,400 | $10.00 | |
5,100 | $10.00 | |
Discounts | ||
# of seats | Discount % | |
Volume Tier 1 | <=1,000 | 0% |
Volume Tier 2 | >1,000<=3,000 | 5% |
Volume Tier 3 | >3,000<=10,000 | 7% |
Apr 05 2021 02:48 PM
See screenshot below. I added a helper column that calculates the additional discount in each tier.
The formula in C2 is
=B2*(A2-SUMPRODUCT((A2>$B$10:$B$12)*(A2-$B$10:$B$12),$D$10:$D$12))
This can be filled down.
Apr 05 2021 03:20 PM
Apr 06 2021 05:07 AM
Hans,
Using the 5100 seats, I applied your formula and got the same total you show....$48, 530.00
But if I calculate it manually, I get a different total: $48,730.00
Apr 06 2021 05:38 AM
Apr 06 2021 07:00 AM
I interpreted your original description differently. I assumed that you meant:
First 1000 seats no discount, from 1000 to 3000 seats 5% discount, all seats above 3000 seats 7% discount.
Your new description looks like:
First 1000 seats no discount, next 3000 seats (so up to 4000) 5% discount, all seats above 4000 seats 7% discount.
If that is really what you want, simply change the number 3000 in B12 to 4000. The formula can remain the same.
Apr 06 2021 07:39 AM