Pricing Table Predicament Please Help:

[bobby1987]

Hi Guys,

I am new to both this forum and excel and in need of some help. I have created a pricing table (Please see below and you are charged per unit in a tier structure.

For example if I purchased 13,500 units, I would be charged 0.21 per unit for the first 10,000 units and for the other 3,500 i will be charged at 0.19.

[TABLE="width: 453"]
<colgroup><col><col span="4"></colgroup><tbody>[TR]
[TD]FIRST 0-10,000[/TD]
[TD="align: right"]0.21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]NEXT 10,001-20,000[/TD]
[TD="align: right"]0.19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]NEXT 20,001-40,000[/TD]
[TD="align: right"]0.16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]THEN 40,001+[/TD]
[TD="align: right"]0.12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

My question is can I put a number of any amount of units below this table and excel can provide me with that exact price?

Seems very complicated but if you can help that would be great.

Many thanks,

Bobby1987

 

[justwitt]

Assuming your first column are in cells A1 through A4 and the next is B1 through B4. Put the number of units into Cell A6 and put the following formula into Cell B6
=If(A6<10000,A6*B1,if(A6<20000, 10000*B1+(A6-10000)*B2,if(A6<40000, 10000*B1+10000*B2+(A6-20000)*B3,10000*B1+10000*B2+20000*B3+(A6-40000)*B4)))

There might be a better way to do it, but that should work

 

[pjmorris]

Hi Bobby,

In cells as follows:
A1=10000, B1=0.21
A2=20000, B2=0.19
A3=40000, B3=0.16
A4=">40000", B4=0.12

Assuming your amount of units is in A5 then use this formula: =IF(A5<=A1,A5*B1,(A1*B1)+IF(A5<=A2,(A5-A1)*B2,(A2-A1)*B2+IF(A5<A3,(A5-A2)*B3,(A3-A2)*B3+(A5-A3)*B4)))

It certainly isn't any easier to understand than Justwitt's, but it does allow you to change the cut-offs between the bands. And like Justwitt I think there is a better solution, probably based on SUMPRODUCT, but I couldn't make it work!

Not sure my solution is significantly better than Justwitt's but having worked on it thought I share it.

Regards to all

 

[shg]

Another way:

      --A-- --B-- --C-- ---D---
  1    Qty  Price  Qty   Price 
  2               10000 2100.00
  3       0  0.21 10001 2100.19
  4   10000  0.19              
  5   20000  0.16              
  6   40000  0.12

In D2 and copied down,

=SUMPRODUCT((C2>$A$3:$A$6) *(C2-$A$3:$A$6) * ($B$3:$B$6-$B$2:$B$5))

 

[pjmorris]

Morning shg,

I knew there was a way to use sumproduct, but I dont think I'd have worked it out anytime soon!

Many thanks, much appreciated.

Regards