Check if time value is median for multiple ranges

83JB83

New Member
Joined
May 7, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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!
 

Some videos you may like

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
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 7, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 7, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 7, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
05:50:2905:50:3405:50:30YES(median of row #1) --> A1 B1 C1
05:58:4005:58:4505:58:46NO
06:16:3506:16:4006:16:39YES(median of row #3) --> A3 B3 C3
06:19:4106:19:4605:50:32YES(median of row #1) --> A1 B1 C4
06:22:5806:23:0306:22:50NO
06:29:4006:29:4509:00:00NO
06:36:2106:36:2606:22:59YES(median of row #5) --> A5 B5 C7
06:42:2406:42:2906:42:32NO
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,502
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 7, 2020
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
Perfect :)
Thank you! Very much appreciated! :D
 

Watch MrExcel Video

Forum statistics

Threads
1,118,962
Messages
5,575,260
Members
412,651
Latest member
caitlincole
Top