Lookup and average data in an unsorted list

J

James A

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.
 
M

Max

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top