calculating average with moving startpoint

[broer konijn]

Consider the attached issue.

I calculate average values for a list of units. How would you advise to
calculate the averages, without having to manually move the starting
point, every time when a new unit is listed?

Thus how does =AVERAGE($B$2:B7) move to =AVERAGE($B$8:B8)?




+-------------------------------------------------------------------+
|Filename: Book1.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4925 |
+-------------------------------------------------------------------+

 

[Guest]

Did you mean =AVERAGE($B$2:B8) i.e. average all entries in column B?

If so, you could use =AVERAGE(B:B)

Blanks are ignored but 0s are included in calculation

HTH

 

[Guest]

Soryy ... re-read your mail and my answer is wrong! What decides the new
starting point i.e your example, is it always 6 cells?

 

[Ardus Petus]

=AVERAGE(INDIRECT("B$"&MATCH(A2,A$2:A$16,0)+ROW(A$1)&":B"&ROW()))

HTH

 

[broer konijn]

Ardus,
Briljant! This does the trick!

=AVERAGE(INDIRECT("B$"&MATCH(A2,A$2:A$16,0)+ROW(A$1)&":B"&ROW()))

HTH
--
AP

"Toppers" <[email protected]> a écrit dans le message
de