Proportionately increase percentages so that they add up to 100%

[marshak]

I have highlighted in yellow 33 states and their percentages. These percentages add up to less than 100% because not every state is highlighted.

I would like these percentages to add up to 100%, by increasing each of these highlighted states’ percentages proportionately.

So while the Alabama percentage is 1.649% as among all 56 rows, it will be a greater percentage (though still pretty small) as among only the 33 rows that are highlighted.

How can I accomplish this -- Please help!

088888__130119313v1_Janssen agreement - List of states and allocation percentages.XLSX
	A	B	C
1	EXHIBIT F
2
3	List of States and Overall Allocation Percentages
4			$300,000,000.00
5	Alabama	1.649%	$4,947,387.38
6	Alaska	0.000%
7	American Samoa	0.000%
8	Arizona	2.376%	$7,126,784.96
9	Arkansas	0.000%
10	California	9.921%	$29,764,149.21
11	Colorado	1.662%	$4,984,887.37
12	Connecticut	1.340%	$4,019,975.43
13	Delaware	0.495%	$1,485,449.67
14	District of Columbia	0.208%	$623,487.93
15	Florida	0.000%
16	Georgia	2.788%	$8,364,624.03
17	Guam	0.000%
18	Hawaii	0.000%
19	Idaho	0.530%	$1,589,366.73
20	Illinois	0.000%
21	Indiana	2.217%	$6,650,679.92
22	Iowa	0.000%
23	Kansas	0.000%
24	Kentucky	2.105%	$6,314,367.28
25	Louisiana	1.523%	$4,568,936.03
26	Maine	0.565%	$1,695,302.02
27	Maryland	2.111%	$6,331,827.15
28	Massachusetts	2.304%	$6,910,728.32
29	Michigan	3.402%	$10,206,070.50
30	Minnesota	0.000%
31	Mississippi	0.894%	$2,682,647.13
32	Missouri	0.000%
33	Montana	0.000%
34	N. Mariana Islands	0.000%
35	Nebraska	0.000%
36	Nevada	1.255%	$3,764,146.67
37	New Hampshire	0.631%	$1,893,465.21
38	New Jersey	2.755%	$8,265,406.36
39	New Mexico	0.000%
40	New York	5.390%	$16,171,144.02
41	North Carolina	3.250%	$9,750,757.80
42	North Dakota	0.000%
43	Ohio	4.357%	$13,070,115.42
44	Oklahoma	0.000%
45	Oregon	1.431%	$4,292,751.87
46	Pennsylvania	4.588%	$13,764,725.87
47	Puerto Rico	0.000%
48	Rhode Island	0.494%	$1,482,821.13
49	South Carolina	1.591%	$4,771,688.98
50	South Dakota	0.000%
51	Tennessee	2.688%	$8,064,442.49
52	Texas	0.000%
53	Utah	1.195%	$3,583,732.49
54	Vermont	0.288%	$862,815.19
55	Virgin Islands	0.000%
56	Virginia	2.280%	$6,840,345.23
57	Washington	2.319%	$6,956,712.05
58	West Virginia	1.144%	$3,431,635.88
59	Wisconsin	0.000%
60	Wyoming	0.000%
Sheet1

Cell Formulas
Range	Formula
C5	C5	=B5*C4
C8	C8	=B8*C4
C10	C10	=B10*C4
C11	C11	=B11*C4
C12	C12	=B12*C4
C13	C13	=B13*C4
C14	C14	=B14*C4
C16	C16	=B16*C4
C19	C19	=B19*C4
C21	C21	=B21*C4
C24	C24	=B24*C4
C25	C25	=B25*C4
C26	C26	=B26*C4
C27	C27	=B27*C4
C28	C28	=B28*C4
C29	C29	=B29*C4
C31	C31	=B31*C4
C36	C36	=B36*C4
C37	C37	=B37*C4
C38	C38	=B38*C4
C40	C40	=B40*C4
C41	C41	=B41*C4
C43	C43	=B43*C4
C45	C45	=B45*C4
C46	C46	=B46*C4
C48	C48	=B48*C4
C49	C49	=B49*C4
C51	C51	=B51*C4
C53	C53	=B53*C4
C54	C54	=B54*C4
C56	C56	=B56*C4
C57	C57	=B57*C4
C58	C58	=B58*C4

 

[lrobbo314]

Something like this?

Unwind with formulas and VBA.xlsm
	A	B	C	D	E
1	EXHIBIT F
2
3	List of States and Overall Allocation Percentages
4			Include	Increase	300000000
5	Alabama	1.65%	1	2.30%	4,947,387.38
6	Alaska	0.00%
7	American Samoa	0.00%
8	Arizona	2.38%	1	3.31%	7,126,784.96
9	Arkansas	0.00%
10	California	9.92%	1	13.83%	29,764,149.21
11	Colorado	1.66%	1	2.32%	4,984,887.37
12	Connecticut	1.34%	1	1.87%	4,019,975.43
13	Delaware	0.50%	1	0.69%	1,485,449.67
14	District of Columbia	0.21%	1	0.29%	623,487.93
15	Florida	0.00%
16	Georgia	2.79%	1	3.89%	8,364,624.03
17	Guam	0.00%
18	Hawaii	0.00%
19	Idaho	0.53%	1	0.74%	1,589,366.73
20	Illinois	0.00%
21	Indiana	2.22%	1	3.09%	6,650,679.92
22	Iowa	0.00%
23	Kansas	0.00%
24	Kentucky	2.10%	1	2.93%	6,314,367.28
25	Louisiana	1.52%	1	2.12%	4,568,936.03
26	Maine	0.57%	1	0.79%	1,695,302.02
27	Maryland	2.11%	1	2.94%	6,331,827.15
28	Massachusetts	2.30%	1	3.21%	6,910,728.32
29	Michigan	3.40%	1	4.74%	10,206,070.50
30	Minnesota	0.00%
31	Mississippi	0.89%	1	1.25%	2,682,647.13
32	Missouri	0.00%
33	Montana	0.00%
34	N. Mariana Islands	0.00%
35	Nebraska	0.00%
36	Nevada	1.25%	1	1.75%	3,764,146.67
37	New Hampshire	0.63%	1	0.88%	1,893,465.21
38	New Jersey	2.76%	1	3.84%	8,265,406.36
39	New Mexico	0.00%
40	New York	5.39%	1	7.51%	16,171,144.02
41	North Carolina	3.25%	1	4.53%	9,750,757.80
42	North Dakota	0.00%
43	Ohio	4.36%	1	6.07%	13,070,115.42
44	Oklahoma	0.00%
45	Oregon	1.43%	1	1.99%	4,292,751.87
46	Pennsylvania	4.59%	1	6.40%	13,764,725.87
47	Puerto Rico	0.00%
48	Rhode Island	0.49%	1	0.69%	1,482,821.13
49	South Carolina	1.59%	1	2.22%	4,771,688.98
50	South Dakota	0.00%
51	Tennessee	2.69%	1	3.75%	8,064,442.49
52	Texas	0.00%
53	Utah	1.19%	1	1.67%	3,583,732.49
54	Vermont	0.29%	1	0.40%	862,815.19
55	Virgin Islands	0.00%
56	Virginia	2.28%	1	3.18%	6,840,345.23
57	Washington	2.32%	1	3.23%	6,956,712.05
58	West Virginia	1.14%	1	1.59%	3,431,635.88
59	Wisconsin	0.00%
60	Wyoming	0.00%
61			Total	100.00%
Sheet6

Cell Formulas
Range	Formula
E5	E5	=B5*E4
E8	E8	=B8*E4
E10	E10	=B10*E4
E11	E11	=B11*E4
E12	E12	=B12*E4
E13	E13	=B13*E4
E14	E14	=B14*E4
E16	E16	=B16*E4
E19	E19	=B19*E4
E21	E21	=B21*E4
E24	E24	=B24*E4
E25	E25	=B25*E4
E26	E26	=B26*E4
E27	E27	=B27*E4
E28	E28	=B28*E4
E29	E29	=B29*E4
E31	E31	=B31*E4
E36	E36	=B36*E4
E37	E37	=B37*E4
E38	E38	=B38*E4
E40	E40	=B40*E4
E41	E41	=B41*E4
E43	E43	=B43*E4
E45	E45	=B45*E4
E46	E46	=B46*E4
E48	E48	=B48*E4
E49	E49	=B49*E4
E51	E51	=B51*E4
E53	E53	=B53*E4
E54	E54	=B54*E4
E56	E56	=B56*E4
E57	E57	=B57*E4
E58	E58	=B58*E4
D5:D60	D5	=IF(C5=1,B5/SUMIF($C$5:$C$60,1,$B$5:$B$60),"")
D61	D61	=SUM(D5:D60)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:D60	Expression	=$C5=1	text	NO

 

[marshak]

Yes, perfect!! Thanks so much