A specific method for conditional averaging?

[Kerry]

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

 

[Bernie Deitrick]

Kerry,

What is the basis for "consecutive rows without a break" ?

HTH,
Bernie
MS Excel MVP

 

[pshepard]

Hi Kerry,

Enter into D4:
=IF(AND(C3="",C4<>""),ROW(),IF(C3>C4,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(D565000)-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.