Rolling Data Calculation

D

Diane

I have a spreadsheet where I receive stock data through
links. It adds a row to the bottom of my spreadsheet each
day with that day's data. I reference the last 30 lines
of data, sum it and analyze it each day (i.e. for the last
30 days, the average price of stock A is 10. I am trying
to find a way to have it automatically sum the last 30
rows instead of each day changing say =sum(a1:a30)/30 to
=sum(a2:a31)/30 to =sum(a3:a32)/30. I hope I have
explained this well enough. Thank you.

Regards,
Diane
 
F

Frank Kabel

Hi
try the following formula
=AVERAGE(OFFSET($A$1,MIN(COUNTA($A:$A)-1,30),1,-30))

Note: this formulas has to go into a different column
 
Top