Formula for variable-height group average

[Guest]

In column A there is a series of numbers; for each number, in column B is its
associated category. For each grouping of adjacent rows with identical
category, I want a formula in column C that will return the average of the
numbers for that grouping. It could be, say, at the top of each grouping;
for other cells the formula would return a blank. Not all groupings are
equal in height. The same category may appear in more than one grouping; in
this case, the formula will return the average for each separated grouping.

Can I make a formula that will yield this result?

 

[Guest]

try this assuming that you want the whole column, otherwise adjust the
reference:

[c1]=SUMIF($B:$B,B1,$A:$A)/COUNTIF($B:$B,B1)

 

[Guest]

Thanks Martin.

The formula works fine where you need to aveage all the cells in column A
with the same value in their column B. However, I only want the average for
continguous rows with the same value in column B; if the same column B value
appears in a non-contiguous row, it would not be included.

Is there such a formula?

try this assuming that you want the whole column, otherwise adjust the
reference:

[c1]=SUMIF($B:$B,B1,$A:$A)/COUNTIF($B:$B,B1)

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

In column A there is a series of numbers; for each number, in column B is its
associated category. For each grouping of adjacent rows with identical
category, I want a formula in column C that will return the average of the
numbers for that grouping. It could be, say, at the top of each grouping;
for other cells the formula would return a blank. Not all groupings are
equal in height. The same category may appear in more than one grouping; in
this case, the formula will return the average for each separated grouping.

Can I make a formula that will yield this result?

 

[Bob Phillips]

Try this

=AVERAGE(B1:INDEX(B1:$B$20,MIN(IF(A1:$A$20<>A1,ROW(A1:$A$20)-1))))

which is an array formula, so commit with Ctrl-Shift-Enter, not just Enter

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

Thanks Martin.

The formula works fine where you need to aveage all the cells in column A
with the same value in their column B. However, I only want the average
for
continguous rows with the same value in column B; if the same column B
value
appears in a non-contiguous row, it would not be included.

Is there such a formula?

try this assuming that you want the whole column, otherwise adjust the
reference:

[c1]=SUMIF($B:$B,B1,$A:$A)/COUNTIF($B:$B,B1)

--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.

In column A there is a series of numbers; for each number, in column B
is its
associated category. For each grouping of adjacent rows with identical
category, I want a formula in column C that will return the average of
the
numbers for that grouping. It could be, say, at the top of each
grouping;
for other cells the formula would return a blank. Not all groupings
are
equal in height. The same category may appear in more than one
grouping; in
this case, the formula will return the average for each separated
grouping.

Can I make a formula that will yield this result?