3 Replies Latest reply on Jun 29, 2018 5:34 PM by Don Wise

# Tiered Volume Based Discounting

Hi all,

I am trying to create some tiered volume based discounting. What do I mean by that? Based on the below two images customers XXX's cost should be \$50/unit for the first ten units and then \$48/unit for the 11th unit (\$548 total). But what if I wanted to see customer XXX's cost under pricing option B? Their total should be \$1,100. I'd ideally like to have a toggle that changes the Customer's cost depending on whether I select A or B (maybe a filter)?

Any idea on how to accomplish this? I have the tiered pricing options as a separate sheet.

• ###### 1. Re: Tiered Volume Based Discounting

Hello Tarang,

See if the attached works for you?  If so, please mark this response as correct. Thanks Don

• ###### 2. Re: Tiered Volume Based Discounting

Hi Don,

Thanks for your help. This was pretty close, but it should be incremental. So, for Option A, the first ten are priced at \$50/unit and the 11th is priced at \$48/unit.

• ###### 3. Re: Tiered Volume Based Discounting

Hello Tarang,

I understand now what you're trying to achieve and I gave it a go with use of the Modulo function.  Essentially, it evaluates the number of units and determines whether it can divide it to 0.  If not, meaning there's a remainder, then the value falls into the next bucket (range of discounts). Then they're added together (the value that was able to be divided by 0 plus the value of the remainder) for your final discount value.

Unfortunately, I've hit a wall with this calculation. So hopefully someone else will pick up the flag and carry it forward for you. Below is the calculation I worked up, but I'm hitting a "can't compare boolean and integer value" error:

IIF(INT([Units]>0 % 0==0 AND INT([Units]<10 %10 == 0,.50

+ IIF(INT([Units]>11 % 11==0 AND INT([Units]<20 %20 == 0,.48

+ IIF(INT([Units]>21 % 21==0 AND INT([Units]<50 %50 == 0,.46

+ IIF(INT([Units]>51 % 51==0 AND INT([Units]<100 %100 == 0,.44

+ IIF(INT([Units]>100 % 100==0 AND INT([Units]<200 %200 == 0,.42

+ IIF(INT([Units]>200 % 200==0,.44)))))))))))))))))

Maybe we'll both learn something!  Thx, Don