Thread: [Solved] formula for discounts
View Single Post
 
Old 03-24-2015, 02:26 PM
Abarxax Abarxax is offline Windows 7 64bit Office 2010 64bit
Novice
  
Join Date: Dec 2013
Posts: 8
Abarxax is on a distinguished road
Default
Quote:
Originally Posted by Snakehips View Post
Abarxax,

Structure it as below example starting with the low volume discount and then progress through the larger discounts.
Eg 1 -1000 0%, 1001 - 5000 5%, 5001 - 10000, 10%, 10000 + 15%

Assuming Qty in B2 and Unit Price in C2 then
=B2*C2*(1-IF(B2<1001,0%,IF(B2<5001,5%,IF(B2<10001,10%,15%))) )

Alternatively you could use a lookup table approach.

Hope that helps.
That does a lot however is there a way to get it to do this for between two numbers like if it is between say 1000 & 5000 give 0%, if it is greater than 5000 but less that 10000 give 5% discount and so on?
Reply With Quote