"Resetting" Average in Excel

Question

Please pardon my poor explanation of my problem, any insight would be greatly appreciated.

I am using an average in excel that begins at a fixed cell and fills down. For example: column B contains values, and column C contains the formula =AVERAGE($B$1:B1) filled down, so at row 10 the formula is =AVERAGE($B$1:B10)

I am wondering if there is a way to calculate this average that will "reset" when the value in the column A changes. Getting a formula for whether column A changes is easy using an IF function but I don't know how to change the reference cell when the average "resets." I attempted to attach an image with the formulas but I do not have 10 reputation so here is a link to a short example sheet:

https://drive.google.com/file/d/0BymZUcneHsYgUnRoYkRkdnJYbmM/view?usp=sharing

I would prefer do accomplish this without VBA if possible.

Thank you very much.

EDIT: would it be possible to have a count function next to the data column that starts over when column A switches from "A" to "B" (or "B" to "A"), then I could simply use an average offset with the resulting value to get the desired average?

Answer 1

You should use the =AVERAGEIF function.

=AVERAGEIF($A$1:A1,A1,$B$1:B1)

Answer 2

Here's how I ended up solving this:

I used =ROW()-MAX(INDEX((A$1:A2<>A2)*ROW(A$1:A2),0 to count down until "A" changed to "B" or vice versa, then used =ROW()-MAX(INDEX((A$1:A2<>A2)*ROW(A$1:A2),0 to average the data with the offset calculated in the previous formula.

Note that the data was still in column B, with "A" or "B" in column A, and these two formulas were placed in I and J, respectively

Thank you very much for all your help

Answer 3

You can use Indirect to resolve a custom range. Since values in "A" can come multiple time in distinct block, you will have to add an extra column to keep track of the starting row of each range :

(starting from row 2, [C1] = 1)
=IF(A2<>A1,ROW(A2),C1)

the result will be in column "D" :

=AVERAGE(INDIRECT("B"& C1 &":B"&ROW(A1)))