Check if time value is median for multiple ranges

[83JB83]

Hello,

i want to check if a time value is the median for for multiple time values.

e.g. I have the time 05:50:31 (In C1) and want to check if this time value is the median of multiple time values.

A B C
05:50:29 05:50:34 05:50:31
05:58:40 05:58:45
06:16:35 06:16:40
06:19:41 06:19:46
06:22:58 06:23:03
06:29:40 06:29:45
06:36:21 06:36:26
06:42:24 06:42:29
06:47:15 06:47:20
06:49:08 06:49:13
06:53:22 06:53:27
06:59:38 06:59:43
07:06:36 07:06:41
07:13:00 07:13:05
07:16:28 07:16:33
07:19:12 07:19:17
07:27:13 07:27:18
07:29:16 07:29:21
07:36:17 07:36:22
07:42:39 07:42:44

I have used the this formula =IF(C1=MEDIAN(A1;B1;C1);"YES";"NO"). Works fine for line numer one, but I also want to check if the value in C1 is median between time values on all the other lines as well...

Thanks for your kind help!

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this what you mean?

20 05 07.xlsm
	A	B	C	D
1	5:50:29	5:50:34	5:50:31	YES
2	5:58:40	5:58:45		NO
3	6:16:35	6:16:40		NO
4	6:19:41	6:19:46		NO
5	6:22:58	6:23:03		NO
6	6:29:40	6:29:45		NO
7	6:36:21	6:36:26		NO
8	6:42:24	6:42:29		NO
9	6:47:15	6:47:20		NO
10	6:49:08	6:49:13		NO
11	6:53:22	6:53:27		NO
12	6:59:38	6:59:43		NO
13	7:06:36	7:06:41		NO
14	7:13:00	7:13:05		NO
15	7:16:28	7:16:33		NO
16	7:19:12	7:19:17		NO
17	7:27:13	7:27:18		NO
18	7:29:16	7:29:21		NO
19	7:36:17	7:36:22		NO
20	7:42:39	7:42:44		NO
Median

Cell Formulas
Range	Formula
D1:D20	D1	=IF(C$1=MEDIAN(A1:B1,C$1),"YES","NO")

 

[83JB83]

Hi Peter,

Thanks for your answer.

no, this is not what I look for.
Sorry, I think my explanation var a bit unclear.

My dataset looks like this:

05:50:29 05:50:34 05:50:31
05:58:40 05:58:45 05:58:40
06:16:35 06:16:40 06:16:35
06:19:41 06:19:46 06:19:42
06:22:58 06:23:03 06:22:58
06:29:40 06:29:45 06:29:40
06:36:21 06:36:26 06:36:21
06:42:24 06:42:29 06:42:25
06:47:15 06:47:20 06:47:16
06:49:08 06:49:13 06:49:08
06:53:22 06:53:27 06:53:24
06:59:38 06:59:43 06:59:38
07:06:36 07:06:41 07:06:38
07:13:00 07:13:05 07:13:01
07:16:28 07:16:33 07:16:28
07:19:12 07:19:17 07:19:14
07:27:13 07:27:18 07:27:13
07:29:16 07:29:21 07:29:16
07:36:17 07:36:22 07:36:17
07:42:39 07:42:44 07:42:39
07:46:00 07:46:05 07:46:01
07:49:56 07:50:01 07:49:57

First I want to check if C1 is median of A1 B1 C1, then A2, B2, C1... A3, B3, C1.... and so on.
Then next time calue in C column. Check if C2 is median of A1, B1, C2, then A2, B2, C2 ... A3, B3, C2...

If the value in C-column is median of one of the data sets it should be stated "YES".

Hope this made it more clear

BR
Jon

 

[Peter_SSs]

If the value in C-column is median of one of the data sets it should be stated "YES".

Then have you given us a fair set of sample data? In every single row of that sample the third value is the median of the 3 values on that row, let alone any other row, so they would all be "YES". Is that correct? If not please give the expected results for that sample data and explain why they are the expected results.

 

[83JB83]

Hi Peter,

yes, you are right; all would be YES in my data set, so it was not an good example.

Here is a better example and the excpected results:

05:50:29​	05:50:34​	05:50:30​	YES	(median of row #1)
05:58:40​	05:58:45​	05:58:46​	NO
06:16:35​	06:16:40​	06:16:39​	YES	(median of row #3)
06:19:41​	06:19:46​	05:50:32​	YES	(median of row #1)
06:22:58​	06:23:03​	06:22:50​	NO
06:29:40​	06:29:45​	09:00:00​	NO
06:36:21​	06:36:26​	06:22:59​	YES	(median of row #5)
06:42:24​	06:42:29​	06:42:32​	NO

 

[83JB83]

05:50:29	05:50:34	05:50:30	YES	(median of row #1) --> A1 B1 C1
05:58:40	05:58:45	05:58:46	NO
06:16:35	06:16:40	06:16:39	YES	(median of row #3) --> A3 B3 C3
06:19:41	06:19:46	05:50:32	YES	(median of row #1) --> A1 B1 C4
06:22:58	06:23:03	06:22:50	NO
06:29:40	06:29:45	09:00:00	NO
06:36:21	06:36:26	06:22:59	YES	(median of row #5) --> A5 B5 C7
06:42:24	06:42:29	06:42:32	NO

 

[Peter_SSs]

Here is a better example and the excpected results:

Thanks, that is better.

Try this.

20 05 07.xlsm
	A	B	C	D
1	5:50:29	5:50:34	5:50:30	YES
2	5:58:40	5:58:45	5:58:46	NO
3	6:16:35	6:16:40	6:16:39	YES
4	6:19:41	6:19:46	5:50:32	YES
5	6:22:58	6:23:03	6:22:50	NO
6	6:29:40	6:29:45	9:00:00	NO
7	6:36:21	6:36:26	6:22:59	YES
8	6:42:24	6:42:29	6:42:32	NO
Median (2)

Cell Formulas
Range	Formula
D1:D8	D1	=IF(COUNTIFS(A$1:A$8,"<="&C1,B$1:B$8,">="&C1),"YES","NO")

 

[83JB83]

Perfect
Thank you! Very much appreciated! :D

 

[Peter_SSs]

Perfect
Thank you! Very much appreciated! :D

Cheers. Glad it worked for you. Thanks for the follow-up.