calculating averages

[keving]

I am sure there is a simple way!
each week I insert a number in a column, thus after 6 weeks I have A1:A6
filled in. Each week I need to take the average of the last 4 numbers. How
can I calculate this automatically without having to change the range each
time?
from Kevin

 

[Peo Sjoblom]

One way


=AVERAGE(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)-3):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))



Regards,

Peo Sjoblom

 

[Bernard Liengme]

I think it can be done more elegantly but this works
=AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-6,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0))

I am assuming you will not go passed A5000

best wishes

 

[Bernard Liengme]

Misread question, I averaged last 6. Change that to
=AVERAGE(OFFSET(A1:A1,COUNT(A1:A5000)-4,0):OFFSET(A1:A1,COUNT(A1:A5000)-1,0))
to average last 4

 

[keving]

oh my! it seems complicated.
the table below shows what I need to do, assuming there is just one column
and I add to it each week.Thus, wk8 I average numbers from B5:B8
A B A B
1 wk1 2 wk1 2
2 wk2 3 wk2 3
3 wk3 4 wk3 4
4 wk4 3 wk4 3
5 wk5 2 wk5 2
6 wk6 1 wk6 1
7 wk7 4


AVERAGE(B3:B6) AVERAGE(B4:B7)

 

[Bernard Liengme]

Have you tried either of the answers sent to you?

 

[keving]

Thank you for the advice. I will give the solutions a go!

 

[keving]

Bernard, I tried your solution - thank you it seems to work, but I have to
admit to not understanding the logic that well!
Any ideas where I might find an explanation of the formula?

 

[CLR]

Try this.........

In C4 put this formula and copy it down column C

=AVERAGE(B1:B4)

Vaya con Dios,
Chuck, CABGx3