averaging N rows in column B which have a certain number in column A
 

I have a spreadsheet like:

0.00 -693.15
0.00 -174.39
0.00 126.87
0.00 -482.67
0.00 648.94
0.00 1798.31
0.00 2718.86
0.00 375.90
0.01 557.35
0.01 642.39
0.01 396.39
0.01 894.31
0.01 -82.87
0.01 -38.57
0.01 -145.30
0.01 778.45
0.01 974.74
0.01 2003.84
0.01 90.46
0.01 60.21
0.01 278.81
0.01 -65.89
0.02 -9.44
0.02 1048.87
0.02 0.66
0.02 1658.53
0.02 354.83
0.02 -9.12
0.02 -67.85
0.02 156.59
0.02 233.67
0.02 -778.79
0.02 264.22
0.02 1920.64
0.02 3513.61
0.02 1694.04
0.03 1339.47
0.03 836.26

and so on

i want to average all rows in column B for which coulmn A has 0.01 in
it and so on

ur help wud be valuable
thanks


--
g s
------------------------------------------------------------------------
g s's Profile: http://www.excelforum.com/member.php...o&userid=24263
View this thread: http://www.excelforum.com/showthread...hreadid=378689

averaging N rows in column B which have a certain number in column A
 

=SUMIF(A1:A50,0.01,B1:B50)/COUNTIF(A1:A50,0.01)

Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378689

averaging N rows in column B which have a certain number in column
 

An alternate approach is to use a Pivot Table which would look like:

Average of y
x Total
0 539.8338
0.01 453.1657
0.02 712.8900
0.03 1087.8650

--
Gary's Student


"g s" wrote:


I have a spreadsheet like:

0.00 -693.15
0.00 -174.39
0.00 126.87
0.00 -482.67
0.00 648.94
0.00 1798.31
0.00 2718.86
0.00 375.90
0.01 557.35
0.01 642.39
0.01 396.39
0.01 894.31
0.01 -82.87
0.01 -38.57
0.01 -145.30
0.01 778.45
0.01 974.74
0.01 2003.84
0.01 90.46
0.01 60.21
0.01 278.81
0.01 -65.89
0.02 -9.44
0.02 1048.87
0.02 0.66
0.02 1658.53
0.02 354.83
0.02 -9.12
0.02 -67.85
0.02 156.59
0.02 233.67
0.02 -778.79
0.02 264.22
0.02 1920.64
0.02 3513.61
0.02 1694.04
0.03 1339.47
0.03 836.26

and so on

i want to average all rows in column B for which coulmn A has 0.01 in
it and so on

ur help wud be valuable
thanks


--
g s
------------------------------------------------------------------------
g s's Profile: http://www.excelforum.com/member.php...o&userid=24263
View this thread: http://www.excelforum.com/showthread...hreadid=378689

averaging N rows in column B which have a certain number in column A
 

I should clarify that 0.01, 0.02 and so on denote 4 second time period
and unfortunately they cycle i.e. after 0.99 comes 1.00 and then agai
0.01. It is a very big data file.
I guess I would have to write a macro to do something like what Olas
did and then save the output in the same or new spreahsheet?

I have to average the entries in Coulmn B and C for a time period sa
0.01 in A and then have to sum up this time averaged B*C values ove
all time periods.
In essence I am consolidating. From second to minute to hour.
Hope I am clear.
I would try out the pivot tables.
Thank u so much. It helps. :

--
g
-----------------------------------------------------------------------
g s's Profile: http://www.excelforum.com/member.php...fo&userid=2426
View this thread: http://www.excelforum.com/showthread.php?threadid=37868