Math formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
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?).
 
Back
Top