Aggregate calculations

[excelos]

Hello

It is very often that there is data in the below format

Product Prices:

Type	Product	01/01/2020​	02/01/2020​	03/01/2020​	04/01/2020​	05/01/2020​
A	A1	1​	2​	3​	4​	5​
A	A2	6​	7​	8​	9​	10​
B	B1	11​	12​	13​	14​	15​
B	B2	16​	17​	18​	19​	20​
B	B3	21​	22​	23​	24​	25​
C	C1	26​	27​	28​	29​	30​
C	C2	31​	32​	33​	34​	35​

Such data is impossible to pivot in various ways e.g. to get the average of all products per day or per week or the sum of of prices of a specific Type versus dates etc.

Obviously this can be done with SUMIFs, COUNTIFs, AVERAGEIFs but do you have any other technique to be able to quickly create aggregations and perform various aggregate calculations?

Thanks!

 

[SullyWYO]

I think the first step is to get the data organized in a better format. My first step would be to use Power Query to get it into something like this:

Once you have it in this format, you can use Pivot Tables or continue on with Power Query to aggregate the data you need. Here is the Power Query code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    #"Unpivoted Only Selected Columns" = Table.Unpivot(Source, {"01/01/2020", "02/01/2020", "03/01/2020", "04/01/2020", "05/01/2020"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Date"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Date", "Type", "Product", "Value"}),
    #"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"Date", Order.Ascending}})
in
    #"Sorted Rows"

 

[excelos]

Thanks, that's what I do but it can be quite slow, inflexible and it has the known null issue.
Rebuilding that with regular Excel formulas will be complex.
Any other way?