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

%3CLINGO-SUB%20id%3D%22lingo-sub-2254825%22%20slang%3D%22en-US%22%3EHow%20to%20use%20multiple%20discount%20levels%20in%20a%20formula%20to%20calculate%20total%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254825%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20formula%20would%20I%20use%20to%20calculate%20the%20Total.%26nbsp%3B%20The%20formula%20has%20to%20apply%20the%20discounts%20levels.%3C%2FP%3E%3CTABLE%20width%3D%22358%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3ESeats%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3EPrice%20per%20seat%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3ETotal%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3E1%2C100%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%2410.00%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3E2%2C400%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%2410.00%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3E5%2C100%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%2410.00%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3EDiscounts%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2229px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2229px%22%3E%23%20of%20seats%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2229px%22%3EDiscount%20%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2256px%22%3EVolume%20Tier%201%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2256px%22%3E%26lt%3B%3D1%2C000%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2256px%22%3E0%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2256px%22%3EVolume%20Tier%202%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2256px%22%3E%26gt%3B1%2C000%26lt%3B%3D3%2C000%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2256px%22%3E5%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2286.4px%22%20height%3D%2256px%22%3EVolume%20Tier%203%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%22164.8px%22%20height%3D%2256px%22%3E%26gt%3B3%2C000%26lt%3B%3D10%2C000%3C%2FTD%3E%3CTD%20width%3D%22105.6px%22%20height%3D%2256px%22%3E7%25%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2254825%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2254879%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20use%20multiple%20discount%20levels%20in%20a%20formula%20to%20calculate%20total%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2254879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1017619%22%20target%3D%22_blank%22%3E%40hanlon7054%3C%2FA%3E%3C%2FP%3E%0A%3CP%3ESee%20screenshot%20below.%20I%20added%20a%20helper%20column%20that%20calculates%20the%20%3CEM%3Eadditional%3C%2FEM%3E%20discount%20in%20each%20tier.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0268.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F269697i2E4D343CCA7FF21A%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0268.png%22%20alt%3D%22S0268.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20C2%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DB2*(A2-SUMPRODUCT((A2%26gt%3B%24B%2410%3A%24B%2412)*(A2-%24B%2410%3A%24B%2412)%2C%24D%2410%3A%24D%2412))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20can%20be%20filled%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

What formula would I use to calculate the Total.  The formula has to apply the discounts levels.

SeatsPrice per seatTotal
1,100$10.00 
2,400$10.00 
5,100$10.00 
   
   
   
Discounts  
 # of seatsDiscount %
Volume Tier 1 <=1,0000%
Volume Tier 2 >1,000<=3,0005%
Volume Tier 3 >3,000<=10,0007%
6 Replies

@hanlon7054

See screenshot below. I added a helper column that calculates the additional discount in each tier.

S0268.png

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.

@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
 

hanlon7054_0-1617710808451.png

 

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













@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.

 

S0269.png

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