Advanced Tiered Pricing

I've been banging my head on the wall with the best way to accomplish a tiered pricing model in Excel, but I need a slight variation.

I can use the SUMPRODUCT function to create the calculation, as explained here: http://mcgimpsey.com/excel/variablerate.html

1-20 units = 12$
21-40 units = 10$
41-60 units = 7.50$

Value in A1 = 32

1-20 = 20 units @ 12$
21-40 = 12 units @10$
41-60 = 0 units @ 7.50

This method works great! ... HOWEVER... I want to be able to put in another value besides the total units that represent how many units in to BEGIN at. (B1) Example:

Value in A1 = 32
Value in B1 = 10

1-20 = 10 units @ 12$
21-40 = 20 units @ 10$
41-60 = 2 units @ 7.50$

My end goal is to create a sheet for helping customers with pricing, when they already have credit for previous purchases.

Any ideas?
Thanks!

I guess you could calc the combined and then subsequently remove the value of B1 ?

=SUMPRODUCT(--(SUM(A1:B1)>{0,20,40}),SUM(A1:B1)-{0,20,40},{12,-2,-2.5})-SUMPRODUCT(--(B1>{0,20,40}),B1-{0,20,40},{12,-2,-2.5})

Here's one approach:

=SUMPRODUCT((A1>IF(B1,B1*{0;1;1}+{0;0;20},{0;20;40}))*(A1-IF(B1,B1*{0;1;1}+
{0;0;20},{0;20;40}))*{12;-2;-2.5})

Does that help?

Welcome to the forum.
You should start a thread of your own stating the problem clearly. What you have done is known as Hijacking a thread and this is a very old thread anyway and is against the rules of the forum specifically rule #2.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.