Rolling Average

Le
michael.juska
I am trying to calculate a rolling average in excel. How can I have
cell a1 display an average of the last 20 data values entered in
column b. So the next time i put a data value at the bottom of cell
b, the average updates to reflect not only the new data value, but the
new set of 20, dropping out the 21st oldest value? Is there a way to
do this? I appreciate the help!

Thanks,

Michael
Vidéos High-Tech et Jeu Vidéo
Téléchargements
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
JB
Le #4273951
Hello!

=AVERAGE(OFFSET(B1,COUNTA(B:B),0,-21))

JB


On 4 avr, 21:29, wrote:
I am trying to calculate a rolling average in excel. How can I have
cell a1 display an average of the last 20 data values entered in
column b. So the next time i put a data value at the bottom of cell
b, the average updates to reflect not only the new data value, but the
new set of 20, dropping out the 21st oldest value? Is there a way to
do this? I appreciate the help!

Thanks,

Michael


garnote
Le #4273931
Hi Michael,

Perhaps :
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1))
Validation : Ctrl+Shift+Enter
si j'ai bien compris ;-)

Serge


I am trying to calculate a rolling average in excel. How can I have
cell a1 display an average of the last 20 data values entered in
column b. So the next time i put a data value at the bottom of cell
b, the average updates to reflect not only the new data value, but the
new set of 20, dropping out the 21st oldest value? Is there a way to
do this? I appreciate the help!

Thanks,

Michael



garnote
Le #4273891
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1))
Validation : Enter

Serge


"garnote"
Hi Michael,

Perhaps :
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1))
Validation : Ctrl+Shift+Enter
si j'ai bien compris ;-)

Serge


I am trying to calculate a rolling average in excel. How can I have
cell a1 display an average of the last 20 data values entered in
column b. So the next time i put a data value at the bottom of cell
b, the average updates to reflect not only the new data value, but the
new set of 20, dropping out the 21st oldest value? Is there a way to
do this? I appreciate the help!

Thanks,

Michael







Publicité
Poster une réponse
Anonyme