Charge a Fee until condition met

[treaves04]

Hopefully this is an easy question and I just don't know the proper term for what I am trying to do. I am in real estate and pay a percentage of the money (6%) I earn to my brokerage up to a certain amount Each year ($3,000). I have multiple rows listing the date I get paid, the amount I will get paid, and the brokerage's split. How do I sum the total paid to the brokerage and stop it when it reaches a set amount?

 

[Fluff]

Maybe something like
=MIN(SUM(J2:J10),3000)

 

[treaves04]

Thank you for the quick reply. The problem I seem to run into is the brokerage takes a maximum of 6% from any one transaction, so no one transaction reaches the $3,000 limit. I keep getting circular references when I try to sum the transactions.

 

[Fluff]

In that case I suggest you use the XL2BB add-in to post some sample data, along with the expected results & clearly explain what you are trying to do.
Also please update your account details to show which version of Excel you are using, as that affects which functions you can use.

 

[treaves04]

Updated Profile but to keep it simple, Office 365.

commisions.xlsx
	C	D	E	F	G	H	I	J	K
1	Sales Price	Rate	Total Commision	Split With	Split %	Split Amt	After Split	***. Royal	$ 3,000.00
2	$ 135,000.00	3%	$ 4,050.00	None	$ -	$ -	$ 4,050.00	6%	$ 243.00
3	$ 135,000.00	3%	$ 4,050.00	None	$ -	$ -	$ 4,050.00	6%	$ 243.00
4	$ 125,000.00	3%	$ 3,750.00	None	$ -	$ -	$ 3,750.00	6%	$ 225.00
5	$ 135,000.00	3%	$ 4,050.00	None	$ -	$ -	$ 4,050.00	6%	$ 243.00
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=E2*G2
I2:I5	I2	=E2-H2
K2,E2:E5	K2	=I2*J2
J3:J5	J3	=IF(K2>3000,0,0.06)
K3:K5	K3	=E3*J3

 

[treaves04]

There are 38 more rows after these, at K14 I will have reach the total $3000 cap. Everything after that either needs to calculate Column J at 0, or just show 0 in K. I could do it manually but I would prefer excel to calculate it so I can use this for other agents who work with me.

 

[Fluff]

Ok, thanks for that.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1			Sales Price	Rate	Total Commision	Split With	Split %	Split Amt	After Split	***. Royal	3000
2			135000	0.03	4050	None	0	0	4050	0.06	243
3			135000	0.03	4050	None	0	0	4050	0.06	243
4			125000	0.03	3750	None	0	0	3750	0.06	225
5			135000	0.03	4050	None	0	0	4050	0.06	243
6			135000	0.03	4050	None	1	4050	0	0.06	243
7			135000	0.03	4050	None	2	8100	-4050	0.06	243
8			135000	0.03	4050	None	3	12150	-8100	0.06	243
9			135000	0.03	4050	None	4	16200	-12150	0.06	243
10			135000	0.03	4050	None	5	20250	-16200	0.06	243
11			135000	0.03	4050	None	6	24300	-20250	0.06	243
12			135000	0.03	4050	None	7	28350	-24300	0.06	243
13			135000	0.03	4050	None	8	32400	-28350	0.06	243
14			135000	0.03	4050	None	9	36450	-32400	0.06	141
15			135000	0.03	4050	None	10	40500	-36450	0.06	0
16			135000	0.03	4050	None	11	44550	-40500	0.06	0
17			135000	0.03	4050	None	12	48600	-44550	0.06	0
18			135000	0.03	4050	None	13	52650	-48600	0.06	0
19			135000	0.03	4050	None	14	56700	-52650	0.06	0
20			135000	0.03	4050	None	15	60750	-56700	0.06	0
21			135000	0.03	4050	None	16	64800	-60750	0.06	0
22			135000	0.03	4050	None	17	68850	-64800	0.06	0
Master

Cell Formulas
Range	Formula
H2:H22	H2	=E2*G2
I2:I22	I2	=E2-H2
K2,E2:E22	K2	=I2*J2
J3:J22	J3	=IF(K2>3000,0,0.06)
K3:K22	K3	=IF(SUM(K$2:K2)>=K$1,0,IF(SUM(K$2:K2,E3*J3)>K$1,SUM(K$2:K2,E3*J3)-K$1,E3*J3))

 

[treaves04]

Genius! I didn't think to use the $ to keep a rolling total and make the formula add the previous cells. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.