Excel Forum

Min, Max, Average or Sum variable number of columns

Dave Green

Member

Members

Forum Posts: 25

Member Since:
January 7, 2012

Offline

August 10, 2022 - 5:02 am

The situation is I have a list of products with their prices. As time progresses prices ge updated via contract variations, and the new rates are added to a new column to the right of the products - this over time gives a history of the individual products.

Note - not all products update at the same time, and they can be plus or minus from the original.

The issue is I need to do some calls based on the price history - i.e. what was the Max price or the Min, Average etc….

The column headers are chosen by the users so they may use something like Variation1, or 5/7/2022 or Summer22 - so basically no regular values.

How then can I find all the columns with pricing (lets say I know the first two columns are and index, and the product), and carry out the necessary calculations?

Im on a train at the moment using my phone so cannot upload a workbook until later, if that would help.

Thanks in advance for any comments

GreenBoy

See Answer

Alan Sidman

Steamboat Springs, CO

Member

Members

Trusted Members

Forum Posts: 132

Member Since:
October 18, 2018

Offline

August 10, 2022 - 1:20 pm

A Workbook would be very handy. I am guessing that your data should be normalized by unpivoting your table and doing a Group By on the products to find the Min, Max and Average.

Dave Green

Member

Members

Forum Posts: 25

Member Since:
January 7, 2012

Offline

August 13, 2022 - 9:42 pm

Hi Alan

Sorry for the delay in responding - the holiday season seems to make everything busier...

Please see a sample sheet below (cant add files to this forum)

I have played with pivoting and unpivoting... but I am obviously missing something.

Any help gratefully received.

Thanks

GreenBoy

Product ID Product Unit Rate Var 1 RPI Var 2

1	Widget	Nr	100	98	102.9	105
2	Metric Widget	Nr	80	83	87.15	85
3	Boxes	Nr		25	26.25	30
4	Meters	m	17	17	17.85	18

Riny van Eekelen

Örnsköldsvik, Sweden

Moderator

Members

Trusted Members

Moderators

Power BI

Forum Posts: 440

Member Since:
January 31, 2022

Offline

August 13, 2022 - 10:49 pm

Hi Dave,

Copied your example in a empty sheet and played around with PQ a bit. Perhaps that's what you had in mind. All it does is unpivot all columns except for the first three (ID, Product and Unit). Then you can group by ID with three separate aggregations (min, max and average).

By the way, you can upload files here. Befor you press "Submit Reply" press "Attachments" at the far left-hand side of the editor window. Select the file(s) "Add File" and then press "Start Upload".

If I missed the point completely, try uploading a file again and include what you have tried so far and what you would want as the end result.

Riny