Calculate Average by month for mass data

[Mjolnir07]

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​

 

[Mjolnir07]

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?

This should read "TLDR is there a faster way to look up cell ranges in huge databases for the purpose of outputting the average for that range, without having to scroll all over the place copy cell locations for the ranges (I search to see that June 2019 is A1:A35 so now I type in =Average B1:B5. I search to see that August 2020 is B500:B531 so now I type in =Average B500:B531) This is what I've been doing for each average month for 11 years of data per database.

 

[sandy666]

something like this?

Month	Year	Average
June	2019	14.23333333
July	2019	77.80645161
August	2019	70.12903226

 

[Misca]

Welcome to the msg board!
The word database makes me think of Power BI / DAX. That would be the right way to go. If your database is in 300 spreadsheets you could use Power Query ( or "Get and Transform" as they seem to call it nowadays ) to combine all those worksheets into a single table. If you're using Excel it's up to you if you want to upload the table into a spreadsheet or just memory. The file size is a lot smaller if you load it in memory only but the functionality is about the same either way.

Once you have combined the spreadsheets into a single table you could either take the Power Pivot / DAX route (which is a bit harder way if you've never worked with DAX before but would give you a ton of more flexibility and tools to work with) or simply use the old fashioned Pivot Table, which is super easy but maybe not that flexible way if you want to do more than just the simple calculations.

Or if you don't like to use either of those but want to get the averages into another column in your database use Power Query to combine the data in your workbooks into a single table and use the AVERAGEIF(s) to calculate the monthly averages in the Monthly Average column.

You can find a tons of tutorials from YouTube for all these solutions.

 

[sandy666]

with Power Pivot (Data Model) size of file will increase almost twice, I suggest Power Query (result is in post#3)

 

[Mjolnir07]

something like this?

Month	Year	Average
June	2019	14.23333333
July	2019	77.80645161
August	2019	70.12903226

Well I definitely like how much prettier it is. However, I'm looking for a way to shorten my present method of finding those averages, being that I'm basically selecting each range by hand and there are probably somewhere close to six million unique cell ranges for me to calculate averages for.

 

[Mjolnir07]

I think I failed to emphasize that I'm not allowed to alter the spreadsheets in any major way or to combine them. I am required to leave them each as independent databases and to calculate the average data for that one column for each month for a combined 3300 years worth of months.

 

[Mjolnir07]

I am not familiar with power query but I will definitely give it a crack, anything can help at this point.

 

[Misca]

Combining the workbooks into a single table with Power Query does not make any changes to the original files. It only collects the data from multiple files to a single database so that it's easier to do calculations with.

 

[sandy666]

add each table to the PQ
and here is an example M to do average as you can see above

let
    Source = Table.Combine({Table1, Table2, Table3, etc...}),
    Month = Table.AddColumn(Source, "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

that's all

example from two tables (I changed year only so avg is the same)

Month	Year	Average
June	2019	14.23333333
July	2019	77.80645161
August	2019	70.12903226
June	2020	14.23333333
July	2020	77.80645161
August	2020	70.12903226