Multiple Discounts for a Value

[mrfrogger]

Hi Folks,

I am wondering if there a pre-existing excel formula to subtract multiple percentages for a certain value.
For example, imagine you are offering a product which has several discounts depending on the quantity purchased. So, if you buy 100 of them, you will have an additional discount of 33%, if you buy 200, you will obtain a discount of the initial 33%+10% (that would be UNIT VALUE=-33%-10%=FINAL VALUE). It's possibly that I should add more values if purchase quantity increases.

Hope I pictured good enough the situation.

Thanks in advance!
Mr Frogger

 

[Joe4]

Welcome to the Board!

It all depends by what you mean.
So, if they buy 300, do they get 43% off of all 300? Or nothing off the first 100, 33% of the second 100, and then 43% off the last 100?
Or something else?

Please list all your different discount levels.

 

[mrfrogger]

Hi Joe4,

Good you mention this.
Nope, it's not proportional. These discounts are static. Maybe in the previous description, I wanted to be more illustrative and made it wrong.
For instance, what I may need is a formula to discount several percentages from value B. For example, A=B(((-33%)-5%)-10%). Take value B as the price list and value A as the final cost that implies to buy a product for me, based on a determined supplier I buy it from. So, this formula would allow me to apply different discounts from different suppliers I have.

Hope it's clearer now :D

Thanks!

 

[mrfrogger]

A literal example, here.

Let's suppose the product price list is $2500. I get an offer for a certain purchase I make, independently to the formula. This offer allows to me obtain the price list product with a discount of -33%-10%-8%.

2500-33%=1675
1675-10%=1507.5
1507.5-8%=1386.9

My final product cost price should be 1386.9

 

[Joe4]

Basically, the formula would then be this:

Price = Quantity * (1 - (DP1 + DP2 + ...))

where
P = Price
Q= Quantity
DP1 = Discount Percent 1
DP2 = Discount Percent 2
etc.

For each discount percent, that can be a formula, i.e.
DP1 might be (for 33% discount if quantity is over 100):

IF(Q>=100,33%,0)

and DP2 might be (for an extra 10% discount is quantity is over 200):

IF(Q>=200,10%,0)

 

[Joe4]

2500-33%=1675
1675-10%=1507.5
1507.5-8%=1386.9

My final product cost price should be 1386.9

In this example, your additional discounts are on the previous discounted price, and not off the original price. Is that really what you intended?

Then what you have is:

=((2500*(100%-33%))*(100%-10%))*(100%-8%)

 

[Marcelo Branco]

Not sure i understand what you are trying to do - maybe something like this

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Qty​

[/td][td]

Discount​

[/td][td][/td][td]

OriginalPrice​

[/td][td]

Qty Purchased​

[/td][td]

NewPrice​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

100​

[/td][td]

33%​

[/td][td][/td][td]

2500​

[/td][td]

400​

[/td][td]

1386,9​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

200​

[/td][td]

10%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

300​

[/td][td]

8%​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Array formula in F2
=D2*PRODUCT(IF(E2>=A2:A4,1-B2:B4,1))
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[mrfrogger]

Nailed it. I owe a beer!

Thanks!

 

[mrfrogger]

Nailed it!

I owe another beer also.

Thanks!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.