Problem with formula: need rolling average of 10 months

C

Claire G

Months are listed in ColumnA A2-A22
Data is listed in ColumnB B2-B22

Which formula would provide a running average of the past 10 months
(Nov-Aug)B13-B22. I would want this to update automatically next month
after adding a row for Dec-Sept B14-B23

Please help.
 
T

Tom Ogilvy

in C11 put in the formula

=if(B11<>"",Average(B2:B11),"")

then drag fill that down the column
 
C

Charlie III

I do the same at work but I use a weighted average because of the seasonality
of my job. Try this.

{=sum(((B2:B22)/sum(B2:B22))*B2:B22)}

Type this in without the end brackets then hit control-shift-enter. This is
an array and works fine. As the months pass drag down the function.
 
C

Charlie III

I do the same at work but I use a weighted average because of the seasonality
of my job. Try this. In column C type-

{=sum(((B2:B22)/sum(B2:B22))*B2:B22)}

Type this in without the end brackets then hit control-shift-enter. This is
an array and works fine. As the months pass drag down the function.
 
Top