Rolling Data Calculation

  • Thread starter Thread starter Diane
  • Start date Start date
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trailing 30 days sum calculation with excel 1
Calculate rolling average using last 30 values greater than 0. 3
Sum number range ignoring numbers formatted as text 0
Write an excel 'sum' formula for a certain # of columns 2
sum data betwen two blank row 1
Locating Source Data in Master Spreadsheet 3
Help with calculation 1
Average of last 30 days 9

Back
Top