Help-Complex equation / formula required to analyze tiered data

[newbie188]

Need your help and expertise. I’m doing some analysis on some vendor proposals.Here’s the situation. Each vendor has established a tier pricing structure. For example,

Tier 1 = 1 - 500 packets (0.05 cents each)
Tier 2 = 501 –1000 packets (0.06 cents each)
Tier 3= 1001 – more packets (0.07 cents each)

So, if I send 499 packets of data the charge would be
499 * 0.05 = $24.95.

For 1000 packets of data the charge would be $55
1st 500 packet at 0.05 = 500 * 0.05 = $25
2nd 500 packet at 0.06 = 500 * 0.06 = $30
TOTAL=$55

For 10,000 packets of data the charge would be $685
1st 500 packet at 0.05 = 500 * 0.05 = $25
2nd 500 packet at 0.06 = 500 * 0.06 = $30
Remaining packets at 0.07 = 9000 * 0.07 = $630
TOTAL=$685

My question is: how do I set up the formula / equation so that it automatically calculates the total amount as illustrated above? Keeping in mind that in order to analyze these proposals, I need to be able manipulate the number of packets. In other words, I would like to change number of packets without having to redo the formula / equation.

I appreciate all suggestions!

Thanks.

 

[jlsmithhartfiel]

Try this in Excel

      Col A   Col B   Col C   Col D
Row 1 Tier 1	1	500	0.05
Row 2 Tier 2	501	1000	0.06
Row 3 Tier 3	1001		0.07

                           Col C
Row 7 Enter # of Packets:  10000
Row 8 Total Charge:	     685

Formula in C8 is:
=IF(C7<C1,C7*D1,(IF(C7<=C2,C1*D1+((C7-C1)*D2),C1*D1+((C2-C1)*D2)+((C7-C2)*D3))))

HTH,

Jessica

 

[KenWright]

With your data in the following table in the range B8:C11, and your variable input number for your order in say B6


B C
8 1 0.05
9 501 0.06
10 1001 0.07
11 9999999 0.00

In any other cell, put the following formula and array enter it, CTRL+SHIFT+ENTER

=SUM(IF(IF(B$6>=B9:B11,B9:B11-B8:B10,$B$6-B8:B10+1)>0,IF(B$6>=B9:B11,B9:B11-B8:B10,$B$6-B8:B10+1),0)*C8:C10)

This is based on a progressive pricing formula from John Walkenbach.

Regards
Ken................

 

[KenWright]

CORRECTION - This was in fact based on a formula from Chip Pearson, and not John. - My memory is going!!! :-(

Regards
Ken...............