# Check if time value is median for multiple ranges

#### 83JB83

##### New Member
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...

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Is this what you mean?

20 05 07.xlsm
ABCD
15:50:295:50:345:50:31YES
25:58:405:58:45NO
36:16:356:16:40NO
46:19:416:19:46NO
56:22:586:23:03NO
66:29:406:29:45NO
76:36:216:36:26NO
86:42:246:42:29NO
96:47:156:47:20NO
106:49:086:49:13NO
116:53:226:53:27NO
126:59:386:59:43NO
137:06:367:06:41NO
147:13:007:13:05NO
157:16:287:16:33NO
167:19:127:19:17NO
177:27:137:27:18NO
187:29:167:29:21NO
197:36:177:36:22NO
207:42:397:42:44NO
Median
Cell Formulas
RangeFormula
D1:D20D1=IF(C\$1=MEDIAN(A1:B1,C\$1),"YES","NO")

#### 83JB83

##### New Member
Hi Peter,

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

##### MrExcel MVP, Moderator
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

##### New Member

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

##### New Member
 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

##### MrExcel MVP, Moderator

Here is a better example and the excpected results:
Thanks, that is better.

Try this.

20 05 07.xlsm
ABCD
15:50:295:50:345:50:30YES
25:58:405:58:455:58:46NO
36:16:356:16:406:16:39YES
46:19:416:19:465:50:32YES
56:22:586:23:036:22:50NO
66:29:406:29:459:00:00NO
76:36:216:36:266:22:59YES
86:42:246:42:296:42:32NO
Median (2)
Cell Formulas
RangeFormula
D1:D8D1=IF(COUNTIFS(A\$1:A\$8,"<="&C1,B\$1:B\$8,">="&C1),"YES","NO")

#### 83JB83

##### New Member
Perfect
Thank you! Very much appreciated! :D

#### Peter_SSs

##### MrExcel MVP, Moderator
Perfect
Thank you! Very much appreciated! :D
Cheers. Glad it worked for you. Thanks for the follow-up.

Replies
2
Views
91
Replies
1
Views
60
Replies
2
Views
68
Replies
3
Views
131
Replies
1
Views
49