Tiered Pricing Formulae - is this possible?

B

bryanbarnard2

Hi

I'm trying to locate a formula that can calculate what I have to charge based on the below tier structure:

Items Cost
1-1250 £1.50
1251-1500 £1.25
1501-1750 £1.00
1750+ £0.75

If a figure of 1550 was placed into a cell then the price I have to charge based on the above tiered structure should be £2237.50

i.e.

First 1250 items equals £1875.00
Next 250 items equals 312.50
Next 50 items equals £50.00

does such formula exist?

Thanks, in advance, for any assistance that can be provided.
 
C

Claus Busch

Hi,

Am Mon, 9 Sep 2013 05:59:05 -0700 (PDT) schrieb (e-mail address removed):
Items Cost
1-1250 £1.50
1251-1500 £1.25
1501-1750 £1.00
1750+ £0.75
Click to expand...

write in A2:B5:
1.5 1250
1.25 1500
1 1750
0.75 10000

and in C2 your items e.g. 1550
Then:
=SUMPRODUCT(((B2:B5)-(B1:B4))*(C$2>B1:B4),A2:A5)-(MIN(IF(B2:B5>=C2,B2:B5))-C2)*INDEX(A2:A5,MATCH(MIN(IF(B2:B5>=C2,B2:B5)),B2:B5,0))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
 
B

bryanbarnard2

Hi



I'm trying to locate a formula that can calculate what I have to charge based on the below tier structure:



Items Cost

1-1250 £1.50

1251-1500 £1.25

1501-1750 £1.00

1750+ £0.75



If a figure of 1550 was placed into a cell then the price I have to charge based on the above tiered structure should be £2237.50



i.e.



First 1250 items equals £1875.00

Next 250 items equals 312.50

Next 50 items equals £50.00



does such formula exist?



Thanks, in advance, for any assistance that can be provided.
Click to expand...


Fantastic Claus - thank you so much :)
 
R

Ron Rosenfeld

Hi

I'm trying to locate a formula that can calculate what I have to charge based on the below tier structure:

Items Cost
1-1250 £1.50
1251-1500 £1.25
1501-1750 £1.00
1750+ £0.75

If a figure of 1550 was placed into a cell then the price I have to charge based on the above tiered structure should be £2237.50

i.e.

First 1250 items equals £1875.00
Next 250 items equals 312.50
Next 50 items equals £50.00

does such formula exist?

Thanks, in advance, for any assistance that can be provided.
Click to expand...

Here's another method, using a lookup table which can be easily modified or extended

Set up a table as follows I used H1:J4

0 0 1.50
1250 1875.00 1.25
1500 2187.50 1.00
1750 2437.50 0.75

The values in the first and third columns come from your tier.
The values in the second column are calculated

I2: =I1+(H2-H1)*J1
and fill down

I NAME'd the table: Tbl (refers to: =$H$1:$J$4

With your number of items in A1, the price is given by:

=VLOOKUP(A1,Tbl,2)+(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP(A1,Tbl,3)
 
B

bryanbarnard2

Hi



I'm trying to locate a formula that can calculate what I have to charge based on the below tier structure:



Items Cost

1-1250 £1.50

1251-1500 £1.25

1501-1750 £1.00

1750+ £0.75



If a figure of 1550 was placed into a cell then the price I have to charge based on the above tiered structure should be £2237.50



i.e.



First 1250 items equals £1875.00

Next 250 items equals 312.50

Next 50 items equals £50.00



does such formula exist?



Thanks, in advance, for any assistance that can be provided.
Click to expand...

Thanks Ron - both work perfectly :)
 
Top