Can you average data in 1 column based on a range of values in another?
 

Hi all,

I'd be very grateful indeed for any help with this problem!

I have a range of times in column A and corresponding data in column B.
Several data points in column B relate to a given time in column A, and
the number of data points varies for each time (see below):

A B
0:00:01 67
0:00:01 65
0:00:01 56
0:00:01 87
0:00:02 56
0:00:02 78
0:00:03 78
0:00:04 98
0:00:04 65
0:00:05 102

I'm desparately trying to average the data in column B based on a range
defined by column A (e.g. 0:00:02 to 0:00:03 = 70.67). Is there a
function which could do this?

I have thousands of data points so it is a nightmare to select each
range manually!

Thanks very much


--
kman24
------------------------------------------------------------------------
kman24's Profile: http://www.excelforum.com/member.php...o&userid=16568
View this thread: http://www.excelforum.com/showthread...hreadid=314274

Hi

=SUMPRODUCT(--($A$1:$A$10=TIME(0,0,2)),--($A$1:$A$10<=TIME(0,0,3),$B$1:$B$1
0)/SUMPRODUCT(--($A$1:$A$10=TIME(0,0,2)),--($A$1:$A$10<=TIME(0,0,3))

PS. There is no need for data points to be ordered.

--
Arvi Laanemets
(When sending e-mail, use address arvil<Attarkon.ee)


"kman24" wrote in message
...

Hi all,

I'd be very grateful indeed for any help with this problem!

I have a range of times in column A and corresponding data in column B.
Several data points in column B relate to a given time in column A, and
the number of data points varies for each time (see below):

A B
0:00:01 67
0:00:01 65
0:00:01 56
0:00:01 87
0:00:02 56
0:00:02 78
0:00:03 78
0:00:04 98
0:00:04 65
0:00:05 102

I'm desparately trying to average the data in column B based on a range
defined by column A (e.g. 0:00:02 to 0:00:03 = 70.67). Is there a
function which could do this?

I have thousands of data points so it is a nightmare to select each
range manually!

Thanks very much


--
kman24
------------------------------------------------------------------------
kman24's Profile:
http://www.excelforum.com/member.php...o&userid=16568
View this thread: http://www.excelforum.com/showthread...hreadid=314274

insert headers first row viz time and data
so database is A1 to B11 including headers

in cells G1 to G3 type===== time,0:00:02 and 0:00:03---this is critera

G1--time
G2--0:00:02
G3--0:00:03

in any other cell type

=DAVERAGE(A1:B11,"data",G1:G3)

see help daverage,
mine excel 2000

kman24 wrote in message
...

Hi all,

I'd be very grateful indeed for any help with this problem!

I have a range of times in column A and corresponding data in column B.
Several data points in column B relate to a given time in column A, and
the number of data points varies for each time (see below):

A B
0:00:01 67
0:00:01 65
0:00:01 56
0:00:01 87
0:00:02 56
0:00:02 78
0:00:03 78
0:00:04 98
0:00:04 65
0:00:05 102

I'm desparately trying to average the data in column B based on a range
defined by column A (e.g. 0:00:02 to 0:00:03 = 70.67). Is there a
function which could do this?

I have thousands of data points so it is a nightmare to select each
range manually!

Thanks very much


--
kman24
------------------------------------------------------------------------
kman24's Profile:
http://www.excelforum.com/member.php...o&userid=16568
View this thread: http://www.excelforum.com/showthread...hreadid=314274