Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A specific method for conditional averaging?
Hi,
I have a 3 columns of of data, such as the following: Col A Col B Col C .. .. .. 4 0.2 3 5 0.3 4 6 0.2 5 7 0.5 6 8 0.3 4 9 0.7 8 10 0.1 9 I want to average valus from Col B but in a specific conditional way. Col A is like a unique identity # for each row. Col C is a reference to a different identity # to which the given row is connected to. If you picture drawing a line by connecting the dots, Identity 6 is connected to 5. Note that Identity 4 has two point connected to it. Keeping w/ the connecting the dots analogy, I want to average each line segment, where a segment is defined as a consecutive rows without a break. In the example above, Rows 4 through 7 are one segment and Rows 8 through 10 are another. The problem is that segment vary greatly in # rows, and the total # segments can reach the 1000s, which is why I need an automated method. Any ideas? Thanks, K |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A specific method for conditional averaging?
Kerry,
What is the basis for "consecutive rows without a break" ? HTH, Bernie MS Excel MVP "Kerry" wrote in message ... Hi, I have a 3 columns of of data, such as the following: Col A Col B Col C . . . 4 0.2 3 5 0.3 4 6 0.2 5 7 0.5 6 8 0.3 4 9 0.7 8 10 0.1 9 I want to average valus from Col B but in a specific conditional way. Col A is like a unique identity # for each row. Col C is a reference to a different identity # to which the given row is connected to. If you picture drawing a line by connecting the dots, Identity 6 is connected to 5. Note that Identity 4 has two point connected to it. Keeping w/ the connecting the dots analogy, I want to average each line segment, where a segment is defined as a consecutive rows without a break. In the example above, Rows 4 through 7 are one segment and Rows 8 through 10 are another. The problem is that segment vary greatly in # rows, and the total # segments can reach the 1000s, which is why I need an automated method. Any ideas? Thanks, K |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A specific method for conditional averaging?
Hi Kerry,
Enter into D4: =IF(AND(C3="",C4<""),ROW(),IF(C3C4,ROW(),"")) the above formula determines whether subsequent cells are decreasing, if so then display the row number. This also checks to see if there is a progression from blank to non-blank cells - non-blank cells are not included in the average. Enter into E4: =IFERROR(IF(D4="","",AVERAGE(INDIRECT("$B$"&D4&":$ B$"&MIN(D5:D65000)-1))),"") This formula is using the row numbers from column D to determine where to begin and end a range to be averaged. Copy these formulas down for their respective columns. The result for your example were 2 numbers: .3 and .3667. Hope this helps, if so please click Yes. --------------- Peggy Shepard "Kerry" wrote: Hi, I have a 3 columns of of data, such as the following: Col A Col B Col C .. .. .. 4 0.2 3 5 0.3 4 6 0.2 5 7 0.5 6 8 0.3 4 9 0.7 8 10 0.1 9 I want to average valus from Col B but in a specific conditional way. Col A is like a unique identity # for each row. Col C is a reference to a different identity # to which the given row is connected to. If you picture drawing a line by connecting the dots, Identity 6 is connected to 5. Note that Identity 4 has two point connected to it. Keeping w/ the connecting the dots analogy, I want to average each line segment, where a segment is defined as a consecutive rows without a break. In the example above, Rows 4 through 7 are one segment and Rows 8 through 10 are another. The problem is that segment vary greatly in # rows, and the total # segments can reach the 1000s, which is why I need an automated method. Any ideas? Thanks, K |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional averaging gives wrong result | Excel Worksheet Functions | |||
Multiple Conditional @IF Statement Method | Excel Worksheet Functions | |||
Conditional Averaging - 2 Conditions | Excel Discussion (Misc queries) | |||
averaging specific rows in multiple arrays | Excel Worksheet Functions | |||
Conditional Formulas For Averaging | Excel Discussion (Misc queries) |