Hello everybody,
I would greatly appreciate the help on this one.
I need to do daily averages on a LONG list of hourly data. There are 56088 rows of hourly temperature readings. It will take me a long, long time for me to manually do "=AVERAGE(A1:A24)" and then "AVERAGE(A25:A48)", and then "AVERAGE(A49:A72)", and so on.
Is there a better way to do this?
My file is below... the blank spots in the excel file are just missing values.
Thank you much
I am positive this isn't the best way to do this (because if you delete rows from the data set it will cause problems... but it is a way none the less...
Try this in cell:
D1: =AVERAGE(A1:A24)
D2: =AVERAGE(INDIRECT("A"&ROWS(A$1:A1)*24+1&":A"&ROWS(A$1:A2)*24))
Fill the above formula in D2 down as far as you need to go.
Once you start seeing #DIV/0, you will have encountered the last 24-cell series (Should be 2337 rows in depth according to your data. Row 2338 returns the first #DIV/0)
- Moo
Last edited by Moo the Dog; 06-14-2013 at 04:18 PM.
If there is a column with times it becomes maybe easier
Willem
English is not my native language sorry for errors
Please correct me if I'm completely wrong
Hey Moo!
Your solution was exactly what I needed. Thank you very much!!
Hey, I just stumbled across this post and I am having a similar issue averaging hourly data, except my data doesn't have a consistent number of hourly readings per day. Some days I only have 5 hourly readings, other days I have 10 hourly readings, other days 20. Is there a way to get excel to average the data based on the date, and not on a predefined interval as in the above problem (ie. every 24 cells)?
Yes but you will need to post your own thread posting a link to this one if it helps.
Dave
You know what I actually got it figured out yesterday! Instead of using formulas I used a Pivot Table and it worked really well.
Thanks anyways!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks