Median for 255+ non-contiguous cell ranges

[eelisabeth0910]

Apologies if this is explained poorly. I have a data set of about 1100 rows each representing a unique individual, and 40 columns. The first 20 or so columns contain raw data of the date and time of day (d/mm/yyyy hh:mm) when certain tasks were completed with column header as task type) and the last 20 or so columns are the results of various calculations among various columns (i.e., column Z = column U-column D, column AB = column F-column E, etc.) in hours:minutes. I set this spreadsheet up way before I realized how extensive the data would become so it's probably not most conducive to what I need but I'm stuck with it for the moment.

I need the medians of these column values based on whether the values fall in a certain range (<12:00, 12:01-24:00, 24:01-36:00, 36:01-48:00, or >48:00). Up until recently I've just used the median formula on hand-selected non-contiguous cell ranges (painstaking but effective) but now my dataset is so large that the formula would contain more than 255 cell ranges and no longer works.

Is there any way to calculate these median values for non-contiguous cells? Ideally without having to sort entire columns so that the cell ranges are contiguous? Apologies for my lack of sophistication here.

 

[StephenCrump]

Welcome to the Forum!

Could you use something like this?

	A	B	C	D	E
1			Median		Check
2			35.5		35.5
3
4		Value	Time		12-24h?
5		26	20:48		26
6		26	35:50
7		16	35:05
8		38	12:57		38
9		38	32:32
10		49	2:04
11		49	16:28		49
12		46	18:04		46
13		27	26:03
14		34	20:01		34
15		45	16:59		45
16		49	5:37
17		35	29:53
18		49	31:52
19		50	25:32
20		12	18:31		12
21		26	36:06
22		18	25:44
23		47	31:25
24		24	30:25
25		23	37:43
26		15	42:58
27		46	44:55
28		29	14:41		29
29		37	12:48		37
30		37	24:05
31		28	6:20
32		43	1:09
33		15	38:44
34		34	47:04
35		27	14:31		27
36		10	37:27
37
Sheet1

Cell Formulas
Range	Formula
C2	C2	=MEDIAN(IF((C5:C36>0.5)*(C5:C36<=1),B5:B36,""))
E2	E2	=MEDIAN(E5:E36)
E5:E36	E5	=IF(AND(C5>0.5,C5<=1),B5,"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[eelisabeth0910]

Possibly... Although I need the value of cells in the time column itself (the values are actually custom formatted as [h]:mm;@). Could that format be one of the hindrances? Thank you for your input! I am trying out these ideas now.

 

[eelisabeth0910]

Using your examples it looks like I need to write the logic range as numeric instead of in the custom format -- thanks! This solves a piece of the puzzle.

 

[eelisabeth0910]

How would I write the logic if I want cells with a value of 0:00 to be skipped?

 

[StephenCrump]

Like this?

	A	B	C	D
1		Median		Check
2		25:32		25:32
3
4		Time		Non-zero
5		20:48		20:48
6		35:50		35:50
7		0:00
8		12:57		12:57
9		0:00
10		0:00
11		0:00
12		0:00
13		0:00
14		0:00
15		0:00
16		0:00
17		0:00
18		31:52		31:52
19		25:32		25:32
20		18:31		18:31
21		0:00
22		0:00
23		31:25		31:25
24		0:00
25		37:43		37:43
26		0:00
27		44:55		44:55
28		0:00
29		0:00
30		0:00
31		6:20		6:20
32		1:09		1:09
33		0:00
34		0:00
35		14:31		14:31
36		37:27		37:27
37
Sheet1

Cell Formulas
Range	Formula
B2	B2	=MEDIAN(IF((B5:B36<>0),B5:B36,""))
D2	D2	=MEDIAN(D5:D36)
D5:D36	D5	=IF(B5<>0,B5,"")
Press CTRL+SHIFT+ENTER to enter array formulas.