Help with ticket sales with different commission levels.

[MichiganEliteFightLeague]

Hello, We run a MMA company out of Michigan and I had a question on how to set up a spreadsheet to help with ticket figuring. Here is the scenario I need help with. We sell the tickets for $20. Tickets are signed out to fighters and they sell them to make a cut. If they sell 1 ticket through 20 tickets they receive a $5 kickback and we receive $15. We have different commission points. 1-20 is $5, 21-30 is $6, #31-40 is $7, 41-50 is $8 and 50 tickets plus is $9 back to the fighter. This is where I get lost. The commission varies on the quantity of tickets sold and needs to adjust as needed. Example: When a fighter sells 33 tickets they would get a $7 ticket commission on ticket 31,32,and 33. Then drop to $6 a ticket for 21-30, and then on ticket 1-20 go to $5.
My biggest concern is I need something for speed as we collect money and leftover tickets the day before. I would like something that for example a fighter signs out 20 tickets at $20 a piece. He sells 5 tickets, he keeps a $25 cut and we get $75. For the sake of speed I would need something where I type in how many they signed out and how many tickets they returned. Which in turn tells me they sold 5 tickets, owe $75 and they get a $25 cut. Are ticket prices are $20 for general admission and $30 for VIP. The same cut is for both ticket styles. Any help would be greatly appreciated.

 

[StephenCrump]

Welcome to the Forum!

Here are two ways you could calculate the commission:

B3: =5*NoOfTickets+MAX(0,NoOfTickets-20)+MAX(0,NoOfTickets-30)+MAX(0,NoOfTickets-40)+MAX(0,NoOfTickets-50)
B4: =SUMPRODUCT(--(NoOfTickets>D2:D6),NoOfTickets-D2:D6,E2:E6)

Excel 2010

	A	B	C	D	E
1	NoOfTickets	35		No	Marginal $
2				0	5
3	Commission	$195		20	1
4		$195		30	1
5				40	1
6				50	1

<tbody>
</tbody>

Sheet1​

 

[MichiganEliteFightLeague]

Seems pretty confusing right now. I'll probably do something with just the 1-20 on tickets because most ask for 20 tickets or less. So it would be the $5 a ticket kickback. I need something set for tomorrow to make it easy when things get turned back in.

 

[Excelot]

Hi MichiganEliteFightLeague

I can confirm that StephenCrump's example does work.

If it helps, here's my take on the problem. Below shows the formulas, then below that the values seen in the workbook (apologies, I can't load a workbook for you).

The formulas

	A	B	C	D	E	F	G	H	I	J	K
1					Commission Details					Commission
2						Quantity	Fighter Cut	House Cut		Fighter Comm	House Comm
3					=	0	$0.00	$0.00		0	0
4	Ticket Price	$ 20.00			<=	20	$5.00	=TICKET_PRICE-G4		=IF(TICKETS_SOLD>=F3+1,(MIN(TICKETS_SOLD,F4)-F3)*G4,0)	=IF(TICKETS_SOLD>=F3+1,(MIN(TICKETS_SOLD,F4)-F3)*H4,0)
5	Tickets Sold	35			<=	30	$6.00	=TICKET_PRICE-G5		=IF(TICKETS_SOLD>=F4+1,(MIN(TICKETS_SOLD,F5)-F4)*G5,0)	=IF(TICKETS_SOLD>=F4+1,(MIN(TICKETS_SOLD,F5)-F4)*H5,0)
6					<=	40	$7.00	=TICKET_PRICE-G6		=IF(TICKETS_SOLD>=F5+1,(MIN(TICKETS_SOLD,F6)-F5)*G6,0)	=IF(TICKETS_SOLD>=F5+1,(MIN(TICKETS_SOLD,F6)-F5)*H6,0)
7					<=	50	$8.00	=TICKET_PRICE-G7		=IF(TICKETS_SOLD>=F6+1,(MIN(TICKETS_SOLD,F7)-F6)*G7,0)	=IF(TICKETS_SOLD>=F6+1,(MIN(TICKETS_SOLD,F7)-F6)*H7,0)
8	Total Sale	=B9+B10			>	50	$9.00	=TICKET_PRICE-G8		=IF(TICKETS_SOLD>50,(TICKETS_SOLD-50)*G8,0)	=IF(TICKETS_SOLD>50,(TICKETS_SOLD-50)*H8,0)
9	Fighter Commission	=FIGHTER_COMM								=SUM(J3:J8)	=SUM(K3:K8)
10	House Commission	=HOUSE_COMM

The Values

	A	B	C	D	E	F	G	H	I	J	K
1					Commission Details					Commission
2						Quantity	Fighter Cut	House Cut		Fighter Comm	House Comm
3					=	0	$0.00	$0.00		0	0
4	Ticket Price	$ 20.00			<=	20	$5.00	$15.00		100	300
5	Tickets Sold	35			<=	30	$6.00	$14.00		60	140
6					<=	40	$7.00	$13.00		35	65
7					<=	50	$8.00	$12.00		0	0
8	Total Sale	$ 700.00			>	50	$9.00	$11.00		0	0
9	Fighter Commission	$ 195.00								195	505
10	House Commission	$ 505.00

Named Ranges used

Name	Cell Reference
FIGHTER_COMM	J9
HOUSE_COMM	K9
TICKET_PRICE	B4
TICKETS_SOLD	B5

Hope this helps.