| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Formula for Yearly Averages
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 weeks 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 wont work. Are there any formulas I can put in to do this? Thank you!! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Complex Formula for Yearly Averages
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 "Jen" wrote: 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 weeks 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 wont work. Are there any formulas I can put in to do this? Thank you!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Complex Formula Getting Error.. | Excel Discussion (Misc queries) | |||
| Can someone help with this complex formula? | Excel Worksheet Functions | |||
| challenge! ISNA() and complex array formula | Excel Discussion (Misc queries) | |||
| I Need Help with complex formula ? | Excel Worksheet Functions | |||
| averages formula | Excel Worksheet Functions | |||
Linear Mode
