How to SORT (and AVERAGE) the FILTER output of one column based on data from another column?

[Rnkhch]

Hello,

I need to generate output for drug_level for each subject_ID in the attached example XL2BB, such that the drug_level numbers are sorted based on the increasing number of days from the Day column. Also if there are more than one day with the same number, I want the drug levels to be averaged for that day. Also since the generated outputs will be used for graphing purposes, I want to exclude any blanks form the filtered outputs so that the chart lines don't collapse.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Subject_ID	Day	Drug_level		Example FILTER:	A1	1000	500	654	732	1033	297	345	289	672	453
2	A1	7	1000
3	A1	5	500
4	A2	1	760			day	2	4	5	7	8	9
5	B3	3	892		Intended output:	A1	558.33	289	467.5	1000	693	672
6	A1	8	654				(avg.)		(avg.)		(avg.)
7	A1	8	732
8	B2	4	612				2	3	4	6
9	C1	1	1024		Intended output:	B2	453	1063.5	612	655
10	A1	2	1033					(avg.)
11	A1	2	297
12	A1	2	345
13	A1	4	289
14	D2	7	456
15	A1	3
16	A1	9	672
17	A1	5	453
18	A1	8
19	B2	2	453
20	C4	7	781
21	B2	3	1232
22	B2	3	895
23	C1	9	367
24	B2	6	655
25	B2	3
26	c1	7	652
27
Sheet1

Cell Formulas
Range	Formula
G1:P1	G1	=TRANSPOSE(FILTER(C2:C18,(A2:A18=F1)*(C2:C18<>"")))
Dynamic array formulas.

I have included an example for FILTER for subject A1 in cell G1, but I wasn't sure how to sort the numbers based on increasing days and how to average those days that have more than one number.

Also I have included examples for intended output in G5 and G9. They shows that some days with more than one number have averaged numbers and some days are skipped from output because there are no numbers for it.

Thanks for any input!

 

[lrobbo314]

Could just put it in a pivot table.

But there is also a couple of formulas there that will do what you are asking.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Subject_ID	Day	Drug_level		Subject_ID	Day	Average of Drug_level
2	A1	7	1000		A1		597.50		A1	2	4	5	7	8	9
3	A1	5	500			2	558.33			558.33	289	476.5	1000	693	672
4	A2	1	760			4	289.00
5	B3	3	892			5	476.50
6	A1	8	654			7	1,000.00
7	A1	8	732			8	693.00
8	B2	4	612			9	672.00
9	C1	1	1024		A2		760.00
10	A1	2	1033			1	760.00
11	A1	2	297		B2		769.40
12	A1	2	345			2	453.00
13	A1	4	289			3	1,063.50
14	D2	7	456			4	612.00
15	A1	3				6	655.00
16	A1	9	672		B3		892.00
17	A1	5	453			3	892.00
18	A1	8			C1		681.00
19	B2	2	453			1	1,024.00
20	C4	7	781			7	652.00
21	B2	3	1232			9	367.00
22	B2	3	895		C4		781.00
23	C1	9	367			7	781.00
24	B2	6	655		D2		456.00
25	B2	3				7	456.00
26	c1	7	652		Grand Total		670.64
Sheet2

Cell Formulas
Range	Formula
J2:O2	J2	=TRANSPOSE(SORT(UNIQUE(FILTER(Table4[Day],(Table4[Subject_ID]=I2)*(Table4[Drug_level]>0)))))
J3:O3	J3	=AVERAGEIFS(Table4[Drug_level],Table4[Subject_ID],I2,Table4[Day],J2#)
Dynamic array formulas.

 

[Rnkhch]

This is fabulous, thank you! Just to make sure, how did you generate the second table (columns E,F,G)? I was trying to that and couldn't figure out how to. (But I did press Control+T on my original table which generated your first table).

 

[lrobbo314]

After you've made the table, click on the 'Insert' tab and click on 'PivotTable'. Then drag 'subject_ID' and 'Drug_level' to the 'Rows' area and drag 'Drug_level' to the 'Values' area. After that, click on the arrow of what you just dragged in and select 'Value Field Settings' and select to summarize the field by average instead of sum.

Then you can use that to create a chart.

 

[Rnkhch]

This is so cool. I'm having a little difficulty replicating your table. After I did the steps you mentioned, I get this (only 2 columns; I cannot make the days columns appear no matter what I do):

Book1
	A	B	C
2
3	Row Labels	Average of Drug_level
4	A1	597.5
5	289	289
6	297	297
7	345	345
8	453	453
9	500	500
10	654	654
11	672	672
12	732	732
13	1000	1000
14	1033	1033
15	(blank)
16	A2	760
17	760	760
18	B2	769.4
19	453	453
20	612	612
21	655	655
22	895	895
23	1232	1232
24	(blank)
25	B3	892
26	892	892
27	C1	681
28	367	367
29	652	652
30	1024	1024
31	C4	781
32	781	781
33	D2	456
34	456	456
35	Grand Total	670.6363636
36
Sheet8

Am I doing anything wrong?

 

[lrobbo314]

It looks like you don't have days in there. You should have 2 fields dragged into the rows area.

 

[Rnkhch]

I see. I was going based on your instructions here:

After you've made the table, click on the 'Insert' tab and click on 'PivotTable'. Then drag 'subject_ID' and 'Drug_level' to the 'Rows' area and drag 'Drug_level' to the 'Values' area. After that, click on the arrow of what you just dragged in and select 'Value Field Settings' and select to summarize the field by average instead of sum.

You didn't mention about the "days". I've been trying this multiple times in various ways, but still only get two columns. When I drag "Day" it still becomes a total of two columns and the days go within the subject_ID.

If you could walk me through this one more time, that would be fabulous. I'm probably missing some simple step. Thank you!