Calculate Average by month for mass data

Mjolnir07

New Member
Joined
Sep 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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​
 

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
Joined
Oct 24, 2015
Messages
7,499
the same workbook, different sheets (each for one year) with tables (the same structure)
Month201920182020orMonthYearAverageorYearJuneJulyAugust
August70.1290322614.0967741926.5483871June201914.23333333201813.513.6774193514.09677419
July77.8064516113.6774193525July201977.80645161201914.2333333377.8064516170.12903226
June14.2333333313.527.23333333August201970.12903226202027.233333332526.5483871
June201813.5
July201813.67741935
August201814.09677419
June202027.23333333
July202025
August202026.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
Joined
Sep 6, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web
:O

Thankyouthankyouthankyouthankyouthankyou
 

Watch MrExcel Video

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

Which adblocker are you using?

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
Top