Formula for total price less bulk discount

[Sean15]

Hi:

I have been struggling for a solution and I need some help.

Expected values are in column H to J

In H2, I am looking for a formula for total cash due:

= look up value in A2, return ticket price x tickets sold, less cash discount (per discount band) x ticket sold

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	No of tickets sold							Total cash due
2	Month-30	9	15	220				405.00	611.25	8,085.00
3	Daily	0	8	45				-	76.00	303.75
4	Days-45		9	50				-	495.00	1,787.50
5
6
7			Discount	Cash	Ticket
8		Ticket	Band	Discount	Price
9		Month-30	0	0	$ 45.00
10		Month-30	10	4.25	$ 45.00
11		Month-30	51	6.25	$ 45.00
12		Month-30	200	8.25	$ 45.00
13		Days-45	0	0	$ 55.00
14		Days-45	10	19.25	$ 55.00
15		Daily	0	0	$ 9.50
16		Daily	10	2.75	$ 9.50
Sheet1 (2)

Cell Formulas
Range	Formula
H2	=(45*B2)-(B2*0)
H3	=(9.5*B3)-(B3*0)
H4	=(55*B4)-(B4*0)
I2	=(45*C2)-(C2*4.25)
I3	=(9.5*C3)-(C3*0)
I4	=(55*C4)-(C4*0)
J2	=(45*D2)-(D2*8.25)
J3	=(9.5*D3)-(D3*2.75)
J4	=(55*D4)-(D4*19.25)

Could you help please?

Thanks

Sean

 

[Bo_Ry]

Re: Help with help with formula for total price less bulk discount

Please try at H2
=LOOKUP(B2,$C$9:$C$16/($B$9:$B$16=$A2),$E$9:$E$16-$D$9:$D$16)*B2

Drag across and down

 

[Sean15]

Re: Help with help with formula for total price less bulk discount

Awesome.

Thank you so much.


Regards,

Sean