Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
kman24
 
Posts: n/a
Default 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

  #2   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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



  #3   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combine candle and line in a single chart Bert Charts and Charting in Excel 4 January 19th 05 08:23 AM
Selecting data from a list based on entered values GrantM Excel Discussion (Misc queries) 1 December 20th 04 11:59 AM
If I have X,Y data how do I sum the Y values using a set of bins based on x values ScottBerger Excel Worksheet Functions 1 November 17th 04 12:48 AM
Returning a Value to a Cell Based on a Range of Uncertain Size amc422 Excel Worksheet Functions 7 November 14th 04 04:03 PM
GradeBook WannaKooky Excel Worksheet Functions 1 November 4th 04 03:23 PM


All times are GMT +1. The time now is 12:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"