Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to limit number of rows and column in worksheet? | Excel Discussion (Misc queries) | |||
repeat a formula same column same number of rows apart | Excel Discussion (Misc queries) | |||
HOW DO I RE-NUMBERED MY NUMBER COLUMN AFTER I ADDED ROWS? | Excel Discussion (Misc queries) | |||
Add total number of rows (text) in a column | Excel Worksheet Functions | |||
Data from a number of rows and columns to one column | Excel Discussion (Misc queries) |