Assume column "A" is a series of 1,500 dates (by day).
Assume Column "B" contains data for each day.
How could I create additional columns to show weekly and monthly averages (or sums) from the data in column "B"?
Thanks!
Assume column "A" is a series of 1,500 dates (by day).
Assume Column "B" contains data for each day.
How could I create additional columns to show weekly and monthly averages (or sums) from the data in column "B"?
Thanks!
Last edited by jrtaylor; 05-03-2017 at 04:20 PM.
Hi -
I would use a pivot table and then group the data by months.
____________________________________________
If this has solved your problem, please edit the thread title to add the word [SOLVED] at the beginning. You can do this by
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved
If I have been particularly helpful, please "bump" my reputation by pressing the small star in the lower left corner of my post.
Any other solutions besides pivot tables? I failed to mention that I'm constantly modifying the formulas in the workbook, and I've never quite figured out how to use pivot tables on a complex work in progress.
one way
=SUMIFS(B:B,A:A,">=" & D1,A:A,"<= &EOMONTH(D1,0))
D1 contains the start date of the month you want to SUM e.g. 01/05/2017 for MAY (dd/mm/yy)
Similar formula for AVERAGEIFS
For Weekly, you could use the the WEEKNUM function to determine the week number then
=SUMIF(C:C,2,A:A) for Week 2
where C has the week number
Or SUMIFS with week start/week end dates similar to the SUMIFS above.
Thanks! These work. I very much appreciate your help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks