calculate monthly averages

[lcastanheiro]

Hi, I have data arranged data table how do I calculate the averages of C,D,E and F and tabulate it by year and month as in the Average China Prices table?
Thanks for you help

summary table.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	PO Spot Market Price (unit: RMB/mt)							Average China PO prices RMB/ton SCI
4	Date	Shandong	North China	East China	Northeast China				2023	2022	2021	2020	2019	2018	2017
5	1/4/2022	10,350	10,650	10,400	10,650			January	¥ 9,134	¥ 10,998	¥ 18,268	¥ 9,591	¥ 7,902	¥ 10,544	¥ 10,995
6	1/5/2022	10,550	10,850	10,600	10,850			February	¥ 9,690	¥ 11,627	¥ 17,068	¥ 8,716	¥ 7,997	¥ 10,509	¥ 10,621
7	1/6/2022	10,650	11,000	10,800	10,950			March	¥ 10,580	¥ 11,902	¥ 19,135	¥ 8,208	¥ 8,360	¥ 10,590	¥ 9,926
8	1/7/2022	10,750	11,050	10,800	11,050			April	¥ 9,698	¥ 11,301	¥ 18,099	¥ 7,694	¥ 8,367	¥ 10,634	¥ 9,880
9	1/10/2022	10,750	11,050	10,700	11,050			May	¥ 9,858	¥ 11,119	¥ 17,987	¥ 9,018	¥ 7,507	¥ 10,790	¥ 9,378
10	1/11/2022	10,750	11,050	10,700	11,050			June	¥ 9,474	¥ 10,674	¥ 13,586	¥ 9,615	¥ 7,180	¥ 10,266	¥ 9,937
11	1/12/2022	10,750	11,050	10,650	11,050			July	¥ 9,331	¥ 9,083	¥ 16,093	¥ 10,136	¥ 7,435	¥ 9,801	¥ 10,358
12	1/13/2022	10,525	10,775	10,450	10,750			August	¥ 9,358	¥ 9,030	¥ 16,545	¥ 12,833	¥ 7,965	¥ 10,454	¥ 12,182
13	1/14/2022	10,525	10,775	10,450	10,750			September		¥ 10,272	¥ 16,070	¥ 16,043	¥ 8,126	¥ 10,919	¥ 10,957
14	1/17/2022	10,850	11,175	10,650	11,075			October		¥ 9,882	¥ 17,898	¥ 18,416	¥ 8,074	¥ 10,357	¥ 11,735
15	1/18/2022	10,850	11,175	10,750	11,075			November		¥ 9,241	¥ 15,474	¥ 16,671	¥ 7,729	¥ 9,491	¥ 11,761
16	1/19/2022	11,000	11,375	10,850	11,200			December		¥ 9,032	¥ 13,287	¥ 17,285	¥ 7,762	¥ 9,295	¥ 12,582
17	1/20/2022	11,050	11,400	11,000	11,200
18	1/21/2022	11,150	11,500	11,050	11,300
19	1/24/2022	11,200	11,550	11,150	11,300
20	1/25/2022	11,150	11,500	11,150	11,300
21	1/26/2022	11,150	11,500	11,150	11,300
22	1/27/2022	11,150	11,500	11,100	11,300
23	1/28/2022	11,150	11,500	11,100	11,300
24	1/29/2022	10,950	11,350	11,050	11,200
25	1/30/2022	10,950	11,350	11,050	11,200
26	2/7/2022	10,950	11,350	11,150	11,200
27	2/8/2022	11,150	11,525	11,250	11,400
28	2/9/2022	11,250	11,625	11,400	11,500
29	2/10/2022	11,550	11,900	11,600	11,850
30	2/11/2022	11,650	12,000	11,600	11,950
31	2/14/2022	11,850	12,100	11,800	12,150
Sheet1

 

[alansidman]

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Inserted Year" = Table.AddColumn(#"Unpivoted Other Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Month Name",{"Date"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Year", type text}}, "en-US")[Year]), "Year", "Value", List.Sum)
in
    #"Pivoted Column"

Attribute	Month Name	2022
East China	February	68800
East China	January	227600
North China	February	70500
North China	January	235125
Northeast China	February	70050
Northeast China	January	232900
Shandong	February	68400
Shandong	January	228200

If you want China not by region then:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Date"}, "Attribute", "Value"),
    #"Inserted Year" = Table.AddColumn(#"Unpivoted Other Columns", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Month Name", {"Year", "Month Name"}, {{"Average", each List.Average([Value]), type number}})
in
    #"Grouped Rows"

Year	Month Name	Average
2022	January	10997.91667
2022	February	11572.91667

 

[lcastanheiro]

Hello alansidman, I appreciate your reply, but I have no clue how to use power query at all , and the result is not quite what I was looking for, but rather have the months in rows and the year in columns, so I could complement the master set already have. Thank you for your effort.

 

[AhoyNC]

Try:
Drag formula across and down as needed.

Book2
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	PO Spot Market Price (unit: RMB/mt)							Average China PO prices RMB/ton SCI
4	Date	Shandong	North China	East China	Northeast China				2023	2022	2021	2020	2019	2018	2017
5	1/4/2022	10350	10650	10400	10650			January	0	10998	0	0	0	0	0
6	1/5/2022	10550	10850	10600	10850			February	0	11573	0	0	0	0	0
7	1/6/2022	10650	11000	10800	10950			March	0	0	0	0	0	0	0
8	1/7/2022	10750	11050	10800	11050			April	0	0	0	0	0	0	0
9	1/10/2022	10750	11050	10700	11050			May	0	0	0	0	0	0	0
10	1/11/2022	10750	11050	10700	11050			June	0	0	0	0	0	0	0
11	1/12/2022	10750	11050	10650	11050			July	0	0	0	0	0	0	0
12	1/13/2022	10525	10775	10450	10750			August	0	0	0	0	0	0	0
13	1/14/2022	10525	10775	10450	10750			September	0	0	0	0	0	0	0
14	1/17/2022	10850	11175	10650	11075			October	0	0	0	0	0	0	0
15	1/18/2022	10850	11175	10750	11075			November	0	0	0	0	0	0	0
16	1/19/2022	11000	11375	10850	11200			December	0	0	0	0	0	0	0
17	1/20/2022	11050	11400	11000	11200
18	1/21/2022	11150	11500	11050	11300
19	1/24/2022	11200	11550	11150	11300
20	1/25/2022	11150	11500	11150	11300
21	1/26/2022	11150	11500	11150	11300
22	1/27/2022	11150	11500	11100	11300
23	1/28/2022	11150	11500	11100	11300
24	1/29/2022	10950	11350	11050	11200
25	1/30/2022	10950	11350	11050	11200
26	2/7/2022	10950	11350	11150	11200
27	2/8/2022	11150	11525	11250	11400
28	2/9/2022	11250	11625	11400	11500
29	2/10/2022	11550	11900	11600	11850
30	2/11/2022	11650	12000	11600	11950
31	2/14/2022	11850	12100	11800	12150
Sheet1

Cell Formulas
Range	Formula
J5:P16	J5	=AVERAGE(FILTER($C$5:$F$31,(YEAR($B$5:$B$31)=YEAR(J$4))*(MONTH($B$5:$B$31)=MONTH($I5)),0))

 

[alansidman]

Let me enlighten you about Power Query since you are using O365 for which it is integral.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Also, I have shown results in two different formats which can be interchanged to provide you with the design look you desire.

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!
Cross posted at: Tabulating averages by month and year
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.