I'm trying to average a varying number of values based on the information in
cells giving the beginning and ending times (which will not exactly match the
data). The data array contains blank lines and can't be re-sorted.
In the fragment below, starting and stopping times are given in cells B4 and
B5. I need the formula to search column D (i.e. D1:D24) for the times
closest (or the next earlier) to the start and stop times and average the HR
values in column E during those times. (Col D will contain multiple sets of
times which aren't sortable, but only one set will contain times close to the
Start/Stop times.)
I've tried INDEX and MATCH functions but but can't get it to work properly.

A B C D E
1 Elapsed Time HR
2
3 0:09:25 19:35:19 59
4Start 15:42:30 0:09:30 19:35:24 63
5End 15:43:06 0:09:35 19:35:29 69
6HR 0:09:40 19:35:34 69
7 0:09:45 19:35:39 65.5
8
9 Subject: XX
10 Name J
11 Date of Birth : 9/9/1999 Patient ID : X
12
13 Elapsed Time Time HR
14
15 0:00:00 15:42:27 80.5
16 0:00:05 15:42:32 82
17 0:00:10 15:42:37 66.5
18 0:00:15 15:42:42 74.5
19 0:00:20 15:42:47 69
20 0:00:25 15:42:52 70
21 0:00:30 15:42:57 64
22 0:00:35 15:43:02 67
23 0:00:40 15:43:07 78
24 0:00:45 15:43:12 81
25 0:00:50 15:43:17 86


Thank you for any help you can give me.

Try this in B6, array-entered, ie press CTRL+SHIFT+ENTER to confirm the
formula (instead of just pressing ENTER):
=AVERAGE(IF((D1:D25=B4)*(D1:D25<=B5),E1:E25))
where B4 = starttime, B5 = endtime
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---


"James A" wrote:

I'm trying to average a varying number of values based on the information in
cells giving the beginning and ending times (which will not exactly match the
data). The data array contains blank lines and can't be re-sorted.
In the fragment below, starting and stopping times are given in cells B4 and
B5. I need the formula to search column D (i.e. D1:D24) for the times
closest (or the next earlier) to the start and stop times and average the HR
values in column E during those times. (Col D will contain multiple sets of
times which aren't sortable, but only one set will contain times close to the
Start/Stop times.)
I've tried INDEX and MATCH functions but but can't get it to work properly.

A B C D E
1 Elapsed Time HR
2
3 0:09:25 19:35:19 59
4Start 15:42:30 0:09:30 19:35:24 63
5End 15:43:06 0:09:35 19:35:29 69
6HR 0:09:40 19:35:34 69
7 0:09:45 19:35:39 65.5
8
9 Subject: XX
10 Name J
11 Date of Birth : 9/9/1999 Patient ID : X
12
13 Elapsed Time Time HR
14
15 0:00:00 15:42:27 80.5
16 0:00:05 15:42:32 82
17 0:00:10 15:42:37 66.5
18 0:00:15 15:42:42 74.5
19 0:00:20 15:42:47 69
20 0:00:25 15:42:52 70
21 0:00:30 15:42:57 64
22 0:00:35 15:43:02 67
23 0:00:40 15:43:07 78
24 0:00:45 15:43:12 81
25 0:00:50 15:43:17 86


Thank you for any help you can give me.