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

 

[Greg Truby]

I went ahead and deleted my post; my formula wasn't quite right. Try Dom's. I didn't test his, but based on his track record I'm guessing it'll be good.

 

[TGM]

Cheers Domenic,

Works fine, I realise why you asked the last question now.

Many thanks,

Scott

 

[TGM]

Thanks anyway Greg.

 

[TGM]

Hello again,

I think I have got my head around the formula now. Cheers once again.

I would like to know though, how would that formula be written in pure VBA?

Many thanks,
Scott

 

[Greg Truby]

Not 100% clear on what you're lookin' for...<ul>[*]You want VBA to put that array formula into a particular cell?[*]Or you want a UDF that will accomplish what that formula does?[/list]

 

[TGM]

Hi Greg,

I would like VBA to put the either the array formula or the results of the array formula into a specific cell.

Cheers,
Scott