Hi

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

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.

Hi,

Am Mon, 9 Sep 2013 05:59:05 -0700 (PDT) schrieb :

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

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$2B1: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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Monday, 9 September 2013 13:59:05 UTC+1, wrote:
Hi



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



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.


Fantastic Claus - thank you so much :-)

On Mon, 9 Sep 2013 05:59:05 -0700 (PDT), wrote:

Hi

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

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.

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)

On Monday, 9 September 2013 13:59:05 UTC+1, wrote:
Hi



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



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.

Thanks Ron - both work perfectly :-)