Calculate Average by month for mass data

Mjolnir07

New Member
Hello everyone, I used to be an intermediate bordering on advanced excel user about ten years ago, but I haven't had to use my hard earned knowledge
since then and POOF! It's like it was never there. I know there's a simple solution to this that doesn't involve dragging columns but I'm drawing a blank

So I have about three hundred databases set up like the example below, with the date on the left and that day's value to the right. Three cells over somewhere
on the sheet are monthly average data. The problem I have is that these databases are each 11 years worth of cells in this form, and along the way the various
users who have been calculating the averages have messed up the ranges here in there, either by assuming that simply autofilling to the next row will tell
excel what it needs to know to calculate the next month's average, or by simply selecting the wrong ranges.

What I'm asking is, is there a faster way to gather the averages for each month then by individually typing out or drag-selecting the range for each month?
At this point I'm having to scroll past a thousand cells to locate the first date of each month and the last date, then manually enter the range for each average
and double check each cell with an average for accuracy one at a time.

The best thing I've come up with so far has been to create a table that calculates the cell ranges based upon the first cell selected using the number of days in each month
(e.g., January has 31 days, so add 31 to cell x and input to cell y, then populate cell numbers) and still manually entering the averages using those calculated numbers.

I don't know if that's a clear enough explanation of the problem.

TLDR is there a faster way to have cell lookup ranges in huge databases and outputting their averages to a designated cell, than actually having to go find those ranges and select
or enther them each by hand?

Thanks for any input!!!.

 6/1/19​ 7​ 6/2/19​ 6​ 6/3/19​ 3​ June 19 Average 3​ 6/4/19​ 4​ July 19 Average 77.80645​ 6/5/19​ 5​ Aug 19 Average 70.12903​ 6/6/19​ 6​ 6/7/19​ 7​ 6/8/19​ 4​ 6/9/19​ 9​ 6/10/19​ 1​ 6/11/19​ 11​ 6/12/19​ 3​ 6/13/19​ 13​ 6/14/19​ 34​ 6/15/19​ 3​ 6/16/19​ 3​ 6/17/19​ 17​ 6/18/19​ 3​ 6/19/19​ 4​ 6/20/19​ 20​ 6/21/19​ 3​ 6/22/19​ 22​ 6/23/19​ 43​ 6/24/19​ 35​ 6/25/19​ 36​ 6/26/19​ 26​ 6/27/19​ 22​ 6/28/19​ 44​ 6/29/19​ 29​ 6/30/19​ 4​ 7/1/19​ 31​ 7/2/19​ 33​ 7/3/19​ 4​ 7/4/19​ 34​ 7/5/19​ 4​ 7/6/19​ 5​ 7/7/19​ 37​ 7/8/19​ 38​ 7/9/19​ 5​ 7/10/19​ 40​ 7/11/19​ 41​ 7/12/19​ 22​ 7/13/19​ 41​ 7/14/19​ 44​ 7/15/19​ 45​ 7/16/19​ 234​ 7/17/19​ 47​ 7/18/19​ 48​ 7/19/19​ 49​ 7/20/19​ 344​ 7/21/19​ 51​ 7/22/19​ 52​ 7/23/19​ 42​ 7/24/19​ 546​ 7/25/19​ 55​ 7/26/19​ 56​ 7/27/19​ 57​ 7/28/19​ 243​ 7/29/19​ 59​ 7/30/19​ 44​ 7/31/19​ 61​ 8/1/19​ 4​ 8/2/19​ 63​ 8/3/19​ 64​ 8/4/19​ 44​ 8/5/19​ 66​ 8/6/19​ 4​ 8/7/19​ 4​ 8/8/19​ 69​ 8/9/19​ 4​ 8/10/19​ 56​ 8/11/19​ 72​ 8/12/19​ 2​ 8/13/19​ 74​ 8/14/19​ 43​ 8/15/19​ 76​ 8/16/19​ 77​ 8/17/19​ 346​ 8/18/19​ 79​ 8/19/19​ 79​ 8/20/19​ 79​ 8/21/19​ 79​ 8/22/19​ 79​ 8/23/19​ 79​ 8/24/19​ 79​ 8/25/19​ 79​ 8/26/19​ 79​ 8/27/19​ 79​ 8/28/19​ 79​ 8/29/19​ 79​ 8/30/19​ 79​ 8/31/19​ 79​

sandy666

Banned - Rules violations
want to know more about PQ?

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

sandy666

Banned - Rules violations
the same workbook, different sheets (each for one year) with tables (the same structure)
 Month 2019 2018 2020 or Month Year Average or Year June July August August 70.12903226 14.09677419 26.5483871 June 2019 14.23333333 2018 13.5 13.67741935 14.09677419 July 77.80645161 13.67741935 25 July 2019 77.80645161 2019 14.23333333 77.80645161 70.12903226 June 14.23333333 13.5 27.23333333 August 2019 70.12903226 2020 27.23333333 25 26.5483871 June 2018 13.5 July 2018 13.67741935 August 2018 14.09677419 June 2020 27.23333333 July 2020 25 August 2020 26.5483871

example for the middle table
Power Query:
``````let
Source = Excel.CurrentWorkbook(),
Filter = Table.SelectRows(Source, each not Text.Contains([Name], "Query")),
TSC = Table.SelectColumns(Filter,{"Content"}),
Expand = Table.ExpandTableColumn(TSC, "Content", {"Date", "Value"}, {"Date", "Value"}),
Month = Table.AddColumn(Expand, "Month", each Date.MonthName([Date]), type text),
Year = Table.AddColumn(Month, "Year", each Date.Year([Date]), Int64.Type),
Group = Table.Group(Year, {"Month", "Year"}, {{"Average", each List.Average([Value]), type number}})
in
Group``````

Mjolnir07

New Member
:O

Thankyouthankyouthankyouthankyouthankyou

Last edited:

Replies
8
Views
123
Replies
8
Views
188
Replies
10
Views
180
Replies
2
Views
40
Replies
2
Views
257

Forum statistics

Threads
1,129,685
Messages
5,637,809
Members
416,983
Latest member
LessThanAverageUser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back