Can anybody help with this
Products sell at a discount - bigger order = bigger discount
Number Cost
5 £1.50
10 £1.25
15 £1.20
20 £1.00

To buy 12 the breakdown would be:
1st 5 @ £1.50=£7.50
2nd 5 @ £1.25=£6.25
Last 2 @ £1.20= £2.40

What I want to be able to do is type in the number wanted in cell A1 & then
B1 would give me the answer £16.50

I hope this can be sorted with a formula

Thanks
Saintsman

=(MIN(5,A1)*1.5)+(MIN(5,MAX(0,A1-5))*1.25)+(MIN(5,MAX(0,A1-10))*1.2)+(MIN(5,MAX(0,A1-15))*1)

What would happen to more than 20?


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Saintsman" wrote in message
...
Can anybody help with this
Products sell at a discount - bigger order = bigger discount
Number Cost
5 £1.50
10 £1.25
15 £1.20
20 £1.00

To buy 12 the breakdown would be:
1st 5 @ £1.50=£7.50
2nd 5 @ £1.25=£6.25
Last 2 @ £1.20= £2.40

What I want to be able to do is type in the number wanted in cell A1 &
then
B1 would give me the answer £16.50

I hope this can be sorted with a formula

Thanks
Saintsman

If your numbers 5 to 20 run from A3 to A6, and the corresponding costs
(£1.50 to £1.00) from B3 to B6, then use
For D3 =MIN(A3,A$1)
For D4 =MIN(A4-A3,A$1-SUM(D$3:D3)) and copy down into D5 & D6.
For E3 =D3*B3 and copy down into D4 to D6
For B1 =SUM(E3:E6)
which gives £16.15, not your £16.50 (which I assume is a typo?).
--
David Biddulph

"Saintsman" wrote in message
...
Can anybody help with this
Products sell at a discount - bigger order = bigger discount
Number Cost
5 £1.50
10 £1.25
15 £1.20
20 £1.00

To buy 12 the breakdown would be:
1st 5 @ £1.50=£7.50
2nd 5 @ £1.25=£6.25
Last 2 @ £1.20= £2.40

What I want to be able to do is type in the number wanted in cell A1 &
then
B1 would give me the answer £16.50

I hope this can be sorted with a formula

Thanks
Saintsman

Using JE McGimpsey's approach from his site:
http://mcgimpsey.com/excel/variablerate.html

If D1 = 12 and your table is in A1:B4, try:

=(D1*B1)+SUMPRODUCT(--(D1A1:A3),(D1-A1:A3),(B2:B4-B1:B3))

which also gives me £16.15. BTW anything over 20 will be priced at £1.00


"Saintsman" wrote:

Can anybody help with this
Products sell at a discount - bigger order = bigger discount
Number Cost
5 £1.50
10 £1.25
15 £1.20
20 £1.00

To buy 12 the breakdown would be:
1st 5 @ £1.50=£7.50
2nd 5 @ £1.25=£6.25
Last 2 @ £1.20= £2.40

What I want to be able to do is type in the number wanted in cell A1 & then
B1 would give me the answer £16.50

I hope this can be sorted with a formula

Thanks
Saintsman

Hi

One way
=A1*1+MIN(15,A1)*0.2+MIN(10,A1)*0.05+MIN(5,A1)*0.2 5

--
Regards

Roger Govier


"Saintsman" wrote in message
...
Can anybody help with this
Products sell at a discount - bigger order = bigger discount
Number Cost
5 £1.50
10 £1.25
15 £1.20
20 £1.00

To buy 12 the breakdown would be:
1st 5 @ £1.50=£7.50
2nd 5 @ £1.25=£6.25
Last 2 @ £1.20= £2.40

What I want to be able to do is type in the number wanted in cell A1 &
then
B1 would give me the answer £16.50

I hope this can be sorted with a formula

Thanks
Saintsman