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
|