How to get a percentage of multiple samples in different sized groups.

[Zaner0445]

Example Problem_1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Groups	BL		isotopeLabel	compound		Groups	BL
2		4332.1026		C12 PARENT	1-Methyladenosine		%C12	89.10%
3		247.619056		C13-label-1	1-Methyladenosine		%C13-1	5.09%
4		282.373093		C13-label-1	2-Aminoadipate		%C13-2	5.81%
5		86606.4224		C12 PARENT	2-Dehydro-D-gluconate		%C12	85.13%
6		5594.67302		C13-label-1	2-Dehydro-D-gluconate		%C13-1	5.50%
7		374.129334		C13-label-2	2-Dehydro-D-gluconate		%C13-2	0.37%
8		736.817081		C13-label-3	2-Dehydro-D-gluconate		%C13-3	0.72%
9		286.033555		C13-label-4	2-Dehydro-D-gluconate		%C13-4	0.28%
10		650.682913		C13-label-5	2-Dehydro-D-gluconate		%C13-5	0.64%
11		7480.36311		C13-label-6	2-Dehydro-D-gluconate		%C13-6	7.35%
12		101338.69		C12 PARENT	2-Hydroxy-2-methylsuccinate		%C12	95.02%
13		4671.57155		C13-label-1	2-Hydroxy-2-methylsuccinate		%C13-1	4.38%
14		365.32775		C13-label-2	2-Hydroxy-2-methylsuccinate		%C13-2	0.34%
15		271.325041		C13-label-3	2-Hydroxy-2-methylsuccinate		%C13-3	0.25%
16		101342.959		C12 PARENT	2-hydroxyglutaric acid		%C12	95.02%
17		4678.6522		C13-label-1	2-hydroxyglutaric acid		%C13-1	4.39%
18		365.32775		C13-label-2	2-hydroxyglutaric acid		%C13-2	0.34%
19		271.325041		C13-label-3	2-hydroxyglutaric acid		%C13-3	0.25%
20		50318.3534		C12 PARENT	3-Phosphoglycerate		%C12	97.13%
21		1489.11048		C13-label-1	3-Phosphoglycerate		%C13-1	2.87%
22
23		For example, I am taking J2, and then dividing it by Sum(J2:J4). I am trying to figure out a formula that will know to only do it for each group. The new group starts at the green highlight.
Sheet1

Cell Formulas
Range	Formula
H2:H4	H2	=B2/SUM(B$2:B$4)
H5:H11	H5	=B5/SUM(B$5:B$11)
H12:H15	H12	=B12/SUM(B$12:B$15)
H16:H19	H16	=B16/SUM(B$16:B$19)
H20:H21	H20	=B20/SUM(B$20:B$21)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1:G21	Cell Value	="%C12 "	text	NO
G1:G21	Cell Value	="%C12"	text	NO
G1:G21	Cell Value	="%C12"	text	NO
G2:G21	Cell Value	="%C12"	text	NO
G1:G21	Cell Value	="%C12"	text	NO

 

[Zaner0445]

Hello,

I am trying to figure out a way to get a percent of one of these labels over the entire group. I can do it individually, but am having problems making Excel do the function for me for the large data set I have. I am taking mass % of one sample over the entire samples summed together. The problem is that each group has different amounts of samples. One group might just have two samples, while another has seven. I am attaching a picture, hopefully somebody can make sense of it.

 

[joeu2004]

It seems that a "group" is the set of cells starting with a "parent" cell in column D.

It might help to add the formulas in column F, so that each group has a unique number.

Then the formulas in your column H can be replaced with the formulas in my column I.

Book1
	B	C	D	E	F	G	H	I
1	BL		isotopeLabel	compound	Group#	Groups	BL (orig)	BL (new)
2	4332.102601000000		C12 PARENT	1-Methyladenosine	1	%C12	89.0995100602%	89.0995100602%
3	247.619056200000		C13-label-1	1-Methyladenosine	1	%C13-1	5.0928471971%	5.0928471971%
4	282.373092900000		C13-label-1	2-Aminoadipate	1	%C13-2	5.8076427428%	5.8076427428%
5	86606.422350000000		C12 PARENT	2-Dehydro-D-gluconate	2	%C12	85.1343461827%	85.1343461827%
6	5594.673016000000		C13-label-1	2-Dehydro-D-gluconate	2	%C13-1	5.4995786271%	5.4995786271%
7	374.129333900000		C13-label-2	2-Dehydro-D-gluconate	2	%C13-2	0.3677701418%	0.3677701418%
8	736.817080700000		C13-label-3	2-Dehydro-D-gluconate	2	%C13-3	0.7242931727%	0.7242931727%
9	286.033555300000		C13-label-4	2-Dehydro-D-gluconate	2	%C13-4	0.2811717544%	0.2811717544%
10	650.682913399999		C13-label-5	2-Dehydro-D-gluconate	2	%C13-5	0.6396230545%	0.6396230545%
11	7480.363114000000		C13-label-6	2-Dehydro-D-gluconate	2	%C13-6	7.3532170668%	7.3532170668%
12	101338.689700000000		C12 PARENT	2-Hydroxy-2-methylsuccinate	3	%C12	95.0226179630%	95.0226179630%
13	4671.571551000000		C13-label-1	2-Hydroxy-2-methylsuccinate	3	%C13-1	4.3804094970%	4.3804094970%
14	365.327750400000		C13-label-2	2-Hydroxy-2-methylsuccinate	3	%C13-2	0.3425582012%	0.3425582012%
15	271.325041200000		C13-label-3	2-Hydroxy-2-methylsuccinate	3	%C13-3	0.2544143388%	0.2544143388%
16	101342.958900000000		C12 PARENT	2-hydroxyglutaric acid	4	%C12	95.0165089881%	95.0165089881%
17	4678.652203000000		C13-label-1	2-hydroxyglutaric acid	4	%C13-1	4.3865819976%	4.3865819976%
18	365.327750400000		C13-label-2	2-hydroxyglutaric acid	4	%C13-2	0.3425217485%	0.3425217485%
19	271.325041200000		C13-label-3	2-hydroxyglutaric acid	4	%C13-3	0.2543872657%	0.2543872657%
20	50318.353440000000		C12 PARENT	3-Phosphoglycerate	5	%C12	97.1256835165%	97.1256835165%
21	1489.110475000000		C13-label-1	3-Phosphoglycerate	5	%C13-1	2.8743164835%	2.8743164835%
Sheet1

Formulas:
F2: =IF(RIGHT(D2,6)="parent", N(F1)+1, F1)
I2: =B2 / SUMIFS($B$2:$B$21, $F$2:$F$21, F2)

Select or hover the cursor over each cell to see formulas.

 

[Zaner0445]

It seems that a "group" is the set of cells starting with a "parent" cell in column D.

It might help to add the formulas in column F, so that each group has a unique number.

Then the formulas in your column H can be replaced with the formulas in my column I.

Book1
	B	C	D	E	F	G	H	I
1	BL		isotopeLabel	compound	Group#	Groups	BL (orig)	BL (new)
2	4332.102601000000		C12 PARENT	1-Methyladenosine	1	%C12	89.0995100602%	89.0995100602%
3	247.619056200000		C13-label-1	1-Methyladenosine	1	%C13-1	5.0928471971%	5.0928471971%
4	282.373092900000		C13-label-1	2-Aminoadipate	1	%C13-2	5.8076427428%	5.8076427428%
5	86606.422350000000		C12 PARENT	2-Dehydro-D-gluconate	2	%C12	85.1343461827%	85.1343461827%
6	5594.673016000000		C13-label-1	2-Dehydro-D-gluconate	2	%C13-1	5.4995786271%	5.4995786271%
7	374.129333900000		C13-label-2	2-Dehydro-D-gluconate	2	%C13-2	0.3677701418%	0.3677701418%
8	736.817080700000		C13-label-3	2-Dehydro-D-gluconate	2	%C13-3	0.7242931727%	0.7242931727%
9	286.033555300000		C13-label-4	2-Dehydro-D-gluconate	2	%C13-4	0.2811717544%	0.2811717544%
10	650.682913399999		C13-label-5	2-Dehydro-D-gluconate	2	%C13-5	0.6396230545%	0.6396230545%
11	7480.363114000000		C13-label-6	2-Dehydro-D-gluconate	2	%C13-6	7.3532170668%	7.3532170668%
12	101338.689700000000		C12 PARENT	2-Hydroxy-2-methylsuccinate	3	%C12	95.0226179630%	95.0226179630%
13	4671.571551000000		C13-label-1	2-Hydroxy-2-methylsuccinate	3	%C13-1	4.3804094970%	4.3804094970%
14	365.327750400000		C13-label-2	2-Hydroxy-2-methylsuccinate	3	%C13-2	0.3425582012%	0.3425582012%
15	271.325041200000		C13-label-3	2-Hydroxy-2-methylsuccinate	3	%C13-3	0.2544143388%	0.2544143388%
16	101342.958900000000		C12 PARENT	2-hydroxyglutaric acid	4	%C12	95.0165089881%	95.0165089881%
17	4678.652203000000		C13-label-1	2-hydroxyglutaric acid	4	%C13-1	4.3865819976%	4.3865819976%
18	365.327750400000		C13-label-2	2-hydroxyglutaric acid	4	%C13-2	0.3425217485%	0.3425217485%
19	271.325041200000		C13-label-3	2-hydroxyglutaric acid	4	%C13-3	0.2543872657%	0.2543872657%
20	50318.353440000000		C12 PARENT	3-Phosphoglycerate	5	%C12	97.1256835165%	97.1256835165%
21	1489.110475000000		C13-label-1	3-Phosphoglycerate	5	%C13-1	2.8743164835%	2.8743164835%
Sheet1

Formulas:
F2: =IF(RIGHT(D2,6)="parent", N(F1)+1, F1)
I2: =B2 / SUMIFS($B$2:$B$21, $F$2:$F$21, F2)

Select or hover the cursor over each cell to see formulas.

Thank you so very much! You have no idea how much time that will save me. I have thousands of samples to process. You rock!!