Finding the average monthly value time

[wayne0881]

Hi All,

I am creating a table to try and find the average value of a date range but unsure how to best approach it.

A4:A264 are dates - with the header "Week"
B4:B264 is data (values are between 1-100) - with the header "Data"

Columns D3:P9 is a table
D4:D9 Years (2017-2022)
E3:P3 Months (Jan-Dec)

E4 represents Jan 2017

The end result I am hoping for is that I can get in cell E4 the average total for Jan 2017 from (column B) - E5 would represent Jan 2018 and so on until cell P9 (which would probably show #DIV/0! as we haven't got to Dec 2022 yet so there is no data.

Can excel go and retrieve that data with the data as is? (AVERAGEIFS) or do you think it would be better to split column A into two seperate columns one for Month and one for Year and perform a Vlookup/Match function (or something alike)

Thanks All

 

[Snakehips]

@wayne0881 Here is a possible solution.
Formula is a tad ugly looking. Could be simplified dependant upon your Excel version or happiness to use 'helper' columns / row.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Week	Data			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
4	4/23/17	48		2017				48.5	45.75	44.5	48.2	50	44.75	46.2	49.5	50.2
5	4/30/17	49		2018	44.5	47.25
6	5/7/17	47		2019
7	5/14/17	47		2020
8	5/21/17	44		2021
9	5/28/17	45		2022			49
10	6/4/17	45
11	6/11/17	45
12	6/18/17	43
13	6/25/17	45
14	7/2/17	46
15	7/9/17	48
16	7/16/17	49
17	7/23/17	48
18	7/30/17	50
19	8/6/17	50
20	8/13/17	50
21	8/20/17	49
22	8/27/17	51
23	9/3/17	44
24	9/10/17	45
25	9/17/17	45
26	9/24/17	45
27	10/1/17	43
28	10/8/17	45
29	10/15/17	46
30	10/22/17	48
31	10/29/17	49
32	11/5/17	48
33	11/12/17	50
34	11/19/17	50
35	11/26/17	50
36	12/3/17	49
37	12/10/17	51
38	12/17/17	51
39	12/24/17	51
40	12/31/17	49
41	1/7/18	45
42	1/14/18	45
43	1/21/18	43
44	1/28/18	45
45	2/4/18	46
46	2/11/18	48
47	2/18/18	49
48	2/25/18	46
49	3/4/22	52
50	3/11/22	46
Gradings_2

Cell Formulas
Range	Formula
E4:P9	E4	=IFERROR(SUMPRODUCT((YEAR($A$4:$A$300)=$D4)*(MONTH($A$4:$A$300)=COLUMNS($E:E))*($B$4:$B$300))/SUMPRODUCT((YEAR($A$4:$A$300)=$D4)*(MONTH($A$4:$A$300)=COLUMNS($E:E))),"")

Hope that helps.

 

[alansidman]

An alternative is to use Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Week", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Week]), type text),
    #"Inserted Year" = Table.AddColumn(#"Inserted Month Name", "Year", each Date.Year([Week]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Year", {"Year", "Month Name"}, {{"Average", each List.Average([Data]), type number}}),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[#"Month Name"]), "Month Name", "Average"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Year", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"})
in
    #"Reordered Columns"

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

With the same data as @Snakehips has used

If you have a recent version with the FILTER function ..

22 04 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Week	Data			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
4	23-Apr-17	48		2017				48.5	45.75	44.5	48.2	50		46.2	49.5	50.2
5	30-Apr-17	49		2018	44.5	47.25
6	07-May-17	47		2019
7	14-May-17	47		2020
8	21-May-17	44		2021
9	28-May-17	45		2022			49
10	04-Jun-17	45
11	11-Jun-17	45
Month Average

Cell Formulas
Range	Formula
E4:P9	E4	=IFNA(AVERAGE(FILTER($B$4:$B$264,TEXT($A$4:$A$264,"mmmyyyy")=E$3&$D4,NA())),"")

For older versions ..

22 04 23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Week	Data			Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sept	Oct	Nov	Dec
4	23-Apr-17	48		2017				48.5	45.75	44.5	48.2	50		46.2	49.5	50.2
5	30-Apr-17	49		2018	44.5	47.25
6	07-May-17	47		2019
7	14-May-17	47		2020
8	21-May-17	44		2021
9	28-May-17	45		2022			49
10	04-Jun-17	45
11	11-Jun-17	45
Month Average (2)

Cell Formulas
Range	Formula
E4:P9	E4	=IFERROR(AVERAGE(IF(TEXT($A$4:$A$264,"mmmyyyy")=E$3&$D4,$B$4:$B$264)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[EXCEL MAX]

Here is UDF that may be useful.
First parameter takes range with dates,
second parameter takes range with data and
third parameter takes address top left cell of the final table ("D3" in this case).
NOTE: Don't forget to insert fixed parameter refrences,
you will be able to fill formulas with draging.

Function MonthAverageByYear(vR1 As Range, vR2 As Range, vR3 As Range)

        Dim vDates, vData, vRows As Long, vNRows As Long, _
            vSum As Double, vTopLeftCell As Integer

        vDates = vR1.Value
        vData = vR2.Value
        vTopLeftCell = vR3.Column
        vYear = Cells(Application.Caller.Row, vTopLeftCell)
        For vN = 1 To UBound(vData)
            If Format(vDates(vN, 1), "yyyy") = CStr(vYear) And _
                Month(vDates(vN, 1)) = Application.Caller.Column - vTopLeftCell Then
                vSum = vSum + vData(vN, 1)
                vNRows = vNRows + 1
            End If
        Next
        If vSum = 0 Then
            MonthAverageByYear = ""
        Else
            MonthAverageByYear = vSum / vNRows
        End If

End Function

 

[wayne0881]

Thanks everyone for your very helpful replies.

I will try out all of these later today and update

 

[sucett]

I think this could also be accomplished with a PivotTable