Calculating weighted weight with max

I have 18 companies that I want to invest based on their market cap but I have a 10% as maximum constraint in any company. How can I reallocate the excessive weights?
I tried to reallocate the excessive weight by dividing excessive weight but then it triggered other companies to go over 10%. Please see attachment.

Are you over-complicating this? Invest 10% in the 10 companies with the largest market caps.

One way:

	A	B	C	D	E
1				3.016337188
2	Company	Market Cap	Cap Weighted	Allocation
3	A	1,700,000,000	3.15%	9.51%	D3: =MIN(10%, D$1*C3)
4	B	1,800,000,000	3.34%	10.00%
5	C	110,000,000	0.20%	0.62%
6	A	240,000,000	0.45%	1.34%
7	B	1,600,000,000	2.97%	8.95%
8	C	360,000,000	0.67%	2.01%
9	A	2,500,000,000	4.64%	10.00%
10	B	98,000,000	0.18%	0.55%
11	C	4,000,000,000	7.42%	10.00%
12	A	930,000,000	1.73%	5.20%
13	B	4,500,000,000	8.35%	10.00%
14	C	74,000,000	0.14%	0.41%
15	A	250,000,000	0.46%	1.40%
16	B	19,400,000,000	35.98%	10.00%
17	C	9,000,000,000	16.69%	10.00%
18	A	7,350,000,000	13.63%	10.00%
19
20		53,912,000,000	100.00%	100.00%	D20: =SUM(D2:D19)

Use Goal Seek to set D20 = 100% by changing D1.

Reckon it could be solved in closed form also.

Here you go:

	B	C	D	E	F	G	H	I	J
2							Max
3							10%		H3: Input
4							3.01634		H4: =PERCENTILE($E$6:$E$22, PERCENTRANK($G$6:$G$22, 1, 8))
5	Company	Market Cap	Weighted	Scale	Slope	Sum	Allocation
6				0.00%	1.0000	0.00%
7	AAA Corp	19,400,000,000	35.98%	0.2779	0.6402	27.79%	10.00%		E7: =Max/D7
8	BBB Corp	9,000,000,000	16.69%	0.5990	0.4732	48.35%	10.00%		F7: =SUMIF($D$7:$D$22, "<" & D7)
9	CCC Corp	7,350,000,000	13.63%	0.7335	0.3369	54.71%	10.00%		G7: =G6 + (E7 - E6) * F6
10	DDD Corp	4,500,000,000	8.35%	1.1980	0.2534	70.36%	10.00%		H7: =MIN(Max, H$4*$D7)
11	EEE Corp	4,000,000,000	7.42%	1.3478	0.1792	74.16%	10.00%
12	FFF Corp	2,500,000,000	4.64%	2.1565	0.1328	88.65%	10.00%
13	GGG Corp	1,800,000,000	3.34%	2.9951	0.0995	99.79%	10.00%
14	HHH Corp	1,700,000,000	3.15%	3.1713	0.0679	101.54%	9.51%
15	III Corp	1,600,000,000	2.97%	3.3695	0.0382	102.89%	8.95%
16	JJJ Corp	930,000,000	1.73%	5.7970	0.0210	112.17%	5.20%
17	KKK Corp	360,000,000	0.67%	14.9756	0.0143	131.44%	2.01%
18	LLL Corp	250,000,000	0.46%	21.5648	0.0097	140.88%	1.40%
19	MMM Corp	240,000,000	0.45%	22.4633	0.0052	141.75%	1.34%
20	NNN Corp	110,000,000	0.20%	49.0109	0.0032	155.64%	0.62%
21	OOO Corp	98,000,000	0.18%	55.0122	0.0014	157.55%	0.55%
22	PPP Corp	74,000,000	0.14%	72.8541	0.0000	160.00%	0.41%
23
24	Totals	53,912,000,000	100.00%				100.00%

Market caps have to be sorted descending for the formula in H4 to work.

You're welcome.