How to use multiple discount levels in a formula to calculate total

[hanlon7054]

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%

[#2]

@hanlon7054

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.

[#3]

THANK YOU

[#4]

@hanlon7054

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
 

[#5]

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
Seats Discount % Discounted Seats Applicable Seats Price Total Price
1,000.00 0% 0 1,000.00 $10.00 $10,000.00
3,000.00 5% 150 2,850.00 $10.00 $28,500.00
1,100.00 7% 77 1,023.00 $10.00 $10,230.00
Total $48,730.00














How to use multiple discount levels in a formula to calculate total
























Re: How to use multiple discount levels in a formula to calculate total
























Re: How to use multiple discount levels in a formula to calculate total
























Re: How to use multiple discount levels in a formula to calculate total

[#6]

@hanlon7054 

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.

 

[#7]

You are correct....I was wrong. Thank you