Formula for variable-height group average
 

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?

Formula for variable-height group average
 

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.


"hmm" wrote:

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?

Formula for variable-height group average
 

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?

"Martin Fishlock" wrote:

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.


"hmm" wrote:

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?

Formula for variable-height group average
 

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)


"hmm" wrote in message
...
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?

"Martin Fishlock" wrote:

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.


"hmm" wrote:

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?