Complex Formula for Yearly Averages

J

Jen

I have a worksheet that has 8+ years worth of weekly rates (which are added
to each week). I am trying to calculate the 1 year, 3 year and 5 year
averages.
I need my spreadsheet to automatically calculate the average of 52 cells so
each time I add a new week’s rate, it will change the source to read the most
current 52 weeks.
Also – my rates are in reverse order (oldest on top), so locking the cells
won’t work.
Are there any formulas I can put in to do this?

Thank you!!
 
T

Toppers

Assuming no embedded blank cells, try this for last (latest) 52 week average:

=AVERAGE(INDIRECT("A" & COUNTA(A:A) & ":A" & COUNTA(A:A)-51))

To get 3 and 5 year average change 51 to 103 and 259 respectively

HTH
 

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

min,average>0 if 52 cells read zero 2
Averaging Data across multiple sheets 0
Calculate Average for same period last year 3
Running Average Excel 2007 5
min & average 52 work sheets all read zero 1
Want to keep 'historical' data using formulas 2
Help! How do I ignore blanks cells in an array? 3
Average Calculations 11

Top