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!
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire
Veuillez sélectionner un problème
Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
JB
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
Hello!
=AVERAGE(OFFSET(B1,COUNTA(B:B),0,-21))
JB
On 4 avr, 21:29, michael.ju...@gmail.com 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!
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
Hi Michael,
Perhaps : =MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1)) Validation : Ctrl+Shift+Enter si j'ai bien compris ;-)
Serge
a écrit dans le message de news:
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
Hi Michael,
Perhaps :
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1))
Validation : Ctrl+Shift+Enter
si j'ai bien compris ;-)
Serge
<michael.juska@gmail.com> a écrit dans le message de news: 1175714954.843290.100420@b75g2000hsg.googlegroups.com...
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!
Perhaps : =MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1)) Validation : Ctrl+Shift+Enter si j'ai bien compris ;-)
Serge
a écrit dans le message de news:
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
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1)) Validation : Enter
Serge
"garnote" a écrit dans le message de news:
Hi Michael,
Perhaps : =MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1)) Validation : Ctrl+Shift+Enter si j'ai bien compris ;-)
Serge
a écrit dans le message de news:
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
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1))
Validation : Enter
Serge
"garnote" <garnote3@ENLEVER.videotron.ca> a écrit dans le message de news: u5demFvdHHA.5056@TK2MSFTNGP02.phx.gbl...
Hi Michael,
Perhaps :
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1))
Validation : Ctrl+Shift+Enter
si j'ai bien compris ;-)
Serge
<michael.juska@gmail.com> a écrit dans le message de news: 1175714954.843290.100420@b75g2000hsg.googlegroups.com...
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!
=MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1)) Validation : Enter
Serge
"garnote" a écrit dans le message de news:
Hi Michael,
Perhaps : =MOYENNE(DECALER($B$1;NBVAL(B:B)-20;0;20;1)) Validation : Ctrl+Shift+Enter si j'ai bien compris ;-)
Serge
a écrit dans le message de news:
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!
