Rolling Average in Excel

[Brian Hazzard]

I hope someone can help me out here.

I have a worksheet with a list of numerical data (B3:B23)
I want to have two averages for this, the average of all
data in the column and the average of just the last 5
entries so that as I add another number to the list it'll
use that number and the previous 4 and give an average
value and so on.

Thanks in anticipation

Brian

 

[Jason Morin]

=AVERAGE(OFFSET(B3,COUNT(B3:B23)-1,,-5))

HTH
Jason
Atlanta, GA

 

[Anon]

I hope someone can help me out here.

I have a worksheet with a list of numerical data (B3:B23)
I want to have two averages for this, the average of all
data in the column and the average of just the last 5
entries so that as I add another number to the list it'll
use that number and the previous 4 and give an average
value and so on.

Thanks in anticipation

Brian

The average of all numbers in the list is simply
=AVERAGE(B3:B23)
as blank cells are ignored.

Will you always fill the cells in order from B3 downwards (leaving no
embedded blanks), and will there be a minimum of 5 entries? If the answer to
both of these is "Yes", the average of the last 5 is
=AVERAGE(OFFSET(B3,COUNT(B3:B23)-5,0,5,1))

Or did you mean that the list is now B3:B23 but you will add to B24, B25
etc.? If so, and assuming no numeric data in B1 or B2, you can use
=AVERAGE(B:B)
and
=AVERAGE(OFFSET(B3,COUNT(B:B)-5,0,5,1))

 

[douglasglp]

rolling average

I see that the questions about rolling averages and the formulas given are for data in a column my question is if you want to do that in a row across the worksheet what would the formula be then? Thanks for your help in advance.

 

[strugglinginsuffolk]

Hi I want to do almost exactly the same thing for the last 5 and last 20 enteries in a column, however there are blank cells in mine where no data is added for a certain day. I've tried using the formulas above but they include blanks.
Any help greatly appreciated