Finding The Closest To A Particular Time

[TGM]

Hello,

I hope someone can help as I haven't a clue about where to start with this one.

I have the following three columns in a worksheet.

I would like to search columns A and B for the cell that is closest in time to 12:00:00.

Once it has found it I would like to have the corresponding ODDS for that time displayed in cell D1.

In this case it would be 3.45 as cell A24 is closest to 12:00:00.

HistoricBetfairDataAnalysis.xls
	A	B	C	D
1	LATEST_TAKEN	FIRST_TAKEN	ODDS
2	03/02/200614:18:11	03/02/200614:16:53	2.72
3	03/02/200614:18:16	03/02/200614:15:29	2.74
4	03/02/200614:18:26	03/02/200614:15:39	2.76
5	03/02/200614:18:28	03/02/200614:14:59	2.78
6	03/02/200614:18:29	03/02/200614:11:50	2.80
7	03/02/200614:18:36	03/02/200614:11:49	2.82
8	03/02/200614:18:40	03/02/200614:11:01	2.84
9	03/02/200614:18:41	03/02/200614:10:59	2.86
10	03/02/200614:18:43	03/02/200614:10:54	2.88
11	03/02/200614:18:47	03/02/200614:10:21	2.90
12	03/02/200614:18:50	02/02/200621:28:18	2.92
13	03/02/200614:18:52	03/02/200614:10:01	2.94
14	03/02/200614:20:29	03/02/200614:09:40	2.96
15	03/02/200614:20:43	02/02/200621:31:28	2.98
16	03/02/200614:20:43	02/02/200621:08:49	3.05
17	03/02/200614:20:09	02/02/200621:31:02	3.10
18	03/02/200614:04:28	03/02/200610:56:20	3.15
19	03/02/200613:52:10	02/02/200621:03:14	3.20
20	03/02/200613:32:46	02/02/200621:48:51	3.25
21	03/02/200613:08:08	02/02/200620:35:01	3.30
22	03/02/200612:22:50	02/02/200622:26:09	3.35
23	03/02/200612:15:57	02/02/200621:48:51	3.40
24	03/02/200612:14:21	02/02/200621:48:51	3.45
25	03/02/200610:09:58	02/02/200622:07:10	3.50
26	03/02/200610:08:37	02/02/200622:22:37	3.55
27	03/02/200610:11:41	02/02/200622:41:59	3.60
28	03/02/200610:09:09	02/02/200622:41:59	3.65
29	03/02/200609:19:14	02/02/200622:41:59	3.70
30	02/02/200622:42:16	02/02/200622:42:07	3.75
Sheet1

Many thanks for any help,

Scott

 

[TGM]

Can anyone point me in the right direction please?

 

[Domenic]

Since your data spans over more than one day, do you want the search to be specific to a particular day or are you looking for an overall closest time? If the latter, what happens if the closest time occurs on more than one day? Do you want the first or last occurance? Or do you want each occurance?

 

[TGM]

Cheers for the reply,

I am looking for the latter.

The times on the the previous day will never be near enough to be of concern.

Many thanks,

Scott

 

[Domenic]

Try the following formula which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=IF(N(E2),INDEX(C2:C30,MIN(IF(ABS((A2:B30-INT(A2:B30))-E2)=MIN(ABS((A2:B30-INT(A2:B30))-E2)),ROW(A2:B30)-ROW(A2)+1))),"")

...where E2 contains the target time, such as 12:00:00.

Hope this helps!

 

[TGM]

Thanks once more Greg.

It seems to work for me. I haven't a clue as to how you've done it though.

I will look at it tonight to see if I can unravel it.

Thanks again,
Scott

 

[TGM]

Thanks Domenic,

I'll have to look at that one as well.

Cheers,

Scott

 

[Domenic]

Did I misunderstand? I thought you wanted to search both Columns A and B for the closest match. Or are you searching one column at a time?

 

[TGM]

No you didn't misunderstand, I do want to search both columns for the closest match.

 

[Domenic]

Does Greg's formula in fact return the desired result? If so, can you post it. I may have over-thought my formula...