Returning the middle dates (after already extracting earliest(MIN) and Latest(MAX) dates)

[McKneezy]

Hello,

Can anyone provide me a solution/formula to find date that lie between the MIN and MAX dates based on other criteria (in this case based on the rows with the same ID)? The result should be 2012-04-30.
(note that my list/rows are a lot longer than this below example)

[TABLE="width: 330"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ID[/TD]
[TD]Date[/TD]
[TD]Result[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]282444[/TD]
[TD="align: right"]2011-06-11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]121051[/TD]
[TD="align: right"]2012-02-24[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]1429634[/TD]
[TD="align: right"]2013-04-05[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]148203[/TD]
[TD="align: right"]2009-01-01[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]1534934[/TD]
[TD="align: right"]2010-05-28[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]282444[/TD]
[TD="align: right"]2012-04-30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]282444[/TD]
[TD="align: right"]2018-05-03[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

Hi. Try this entered CTRL-SHIFT-ENTER:

=MEDIAN(IF($B$3:$B$9=B3,$C$3:$C$9))

 

[McKneezy]

Hi. Try this entered CTRL-SHIFT-ENTER:

=MEDIAN(IF($B$3:$B$9=B3,$C$3:$C$9))

Thank you! However it didn't work.

Allow me to explain more clearly. I actually look up the values from another sheet that has a series of dates relating to each ID. I basically want to list these dates in a single row next to the corresponding ID. I am able to get the earliest and latest date, however I can not find a formula to extract the middle date(s)? (Sometimes there is more than one date that lies in-between the two dates)

So how would I retrieve at least any of the two middle/in-between dates of ID# 21716 from the reference table, which should show a result of 2010-11-08 or 2010-10-30? Thanks for your help.


B C D E F
ID Earliest Date Latest Date Middle Date 1 Middle Date 2
2 21716 2010-09-07 2011-01-02 ? ?
3 28246 2009-09-08 2011-01-02
4 12105 2010-08-30 2011-01-03
5 14296 2010-09-07 2011-01-03
6 14820 2010-09-01 2011-01-03
7 15349 2009-09-01 2011-01-03
8 16759 2010-09-07 2011-01-03
9 17058 2010-08-23 2011-01-03
10 18575 2010-10-04 2011-01-03



REFERENCE TABLE (very long list, simplified for demonstration)

G H
ID Date … …
11 12105 2010-08-30
12 21716 2011-01-02
13 21716 2010-09-07
14 21716 2010-11-08
15 21716 2010-10-30
… …

 

[McKneezy]

Sorry, here are clearer tables. I just joined today.


[TABLE="width: 600"]
<colgroup><col><col><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Earliest Date[/TD]
[TD="align: center"]Latest Date[/TD]
[TD="align: center"]Middle Date 1?[/TD]
[TD="align: center"]Middle Date 2?[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]21716[/TD]
[TD="align: center"]2010-09-07[/TD]
[TD="align: center"]2011-01-02[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]28246[/TD]
[TD="align: center"]2009-09-08[/TD]
[TD="align: center"]2011-01-02[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]12105[/TD]
[TD="align: center"]2010-08-30[/TD]
[TD="align: center"]2011-01-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]14296[/TD]
[TD="align: center"]2010-09-07[/TD]
[TD="align: center"]2011-01-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]14820[/TD]
[TD="align: center"]2010-09-01[/TD]
[TD="align: center"]2011-01-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]15349[/TD]
[TD="align: center"]2009-09-01[/TD]
[TD="align: center"]2011-01-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]16759[/TD]
[TD="align: center"]2010-09-07[/TD]
[TD="align: center"]2011-01-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]17058[/TD]
[TD="align: center"]2010-08-23[/TD]
[TD="align: center"]2011-01-03[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]18575[/TD]
[TD="align: center"]2010-10-04[/TD]
[TD="align: center"]2011-01-03[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 500"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]ID[/TD]
[TD="align: center"]Date[/TD]
[/TR]
[TR]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[/TR]
[TR]
[TD="align: center"]100[/TD]
[TD="align: center"]12105[/TD]
[TD="align: center"]2010-08-30[/TD]
[/TR]
[TR]
[TD="align: center"]101[/TD]
[TD="align: center"]21716[/TD]
[TD="align: center"]2011-01-02[/TD]
[/TR]
[TR]
[TD="align: center"]102[/TD]
[TD="align: center"]21716[/TD]
[TD="align: center"]2010-09-07[/TD]
[/TR]
[TR]
[TD="align: center"]103[/TD]
[TD="align: center"]21716[/TD]
[TD="align: center"]2010-11-08[/TD]
[/TR]
[TR]
[TD="align: center"]104[/TD]
[TD="align: center"]21716[/TD]
[TD="align: center"]2010-10-30[/TD]
[/TR]
[TR]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[TD="align: center"]…[/TD]
[/TR]
</tbody>[/TABLE]