Minimum price paid for each item in a list over time

I have a large data set with about 10,000 items purchased over the last 12 months. Trying to calculate the minimum price paid for each, the maximum price paid for each, and the weighted average paid for each.

David,

Consider sending some fake data and will send something across to you

Here is a small example of the data set

DavidLang said:

Here is a small example of the data set

Item Number	UOM Desc	Item + UoM	Unit Cost	Received Quantity	Min Paid	Max Paid	Wgtd Avg Paid
73564	Each	73564Each	4.479	70
73624	Each	73624Each	9.816	77
73658	Each	73658Each	9.225	52
73948	Each	73948Each	12.784	49
73972	Each	73972Each	3.446	75
73972	Each	73972Each	3.568	123
73972	Each	73972Each	3.568	52
74779	Each	74779Each	208.000	10
75108	Each	75108Each	252.000	3
75117	Lb	75117Lb	177.000	20
75117	Lb	75117Lb	177.000	10
75117	Kg	75117Kg	80.450	10
75621	Each	75621Each	156.000	20
75621	Each	75621Each	160.000	3
75621	Each	75621Each	162.000	4
75621	Each	75621Each	170.000	6
75621	Each	75621Each	156.000	10
76156	Each	76156Each	32.191	4
76230	Feet	76230Feet	0.867	3,178
76230	Feet	76230Feet	0.843	2,637

Click to expand...

David,

You have the makings of a little project on your hands. You will have to rebase a few of your item numbers to get them all consistently reading in Lbs/Kgs, Feet/Meters etc else your metrics will get a little interesting, have done it for Kgs attached, look at the entire data set and adjust accordingly.

Also you have max/Min/Average attached. For weighted average calculation suggest build a Sumprod()/Sumif() formula within your full data set.

Knock 'em dead Tiger!

In the attached are two offerings:
1. A formula-only solution in columns H:J, which uses only columns A, D and E of your data. This solution gives you repeating data in multiple rows.
2. A pivot table (in cells L2:O13) solution based on columns A:F (although only columns A, D and F are used). Column F is a calculated column giving the cost of each row being Unit Cost x Received quantity. You may already have this column in your data somewhere in which case you won't need to calculate it. The pivot table has a calculated field in it called Weighted Average (it's only Cost/Received Quantity). This has the advantage of only one row per Item Number.

Edit post posting: I see Ed Kelly has responded with a very similar pivot table, though Ed, are you sure those averages are weighted?

Thank you Ed and p45cal. Very much appreciate your assistance! David

P45cal, your calculated field makes total sense, I had only considered the sumprod()/sumif() added to the raw data as the way forward, your calculated field is a cleaner and more elegant solution as once the end result is in a pivot table the ability to slice and dice and play with potential other configurations is on the table.

Nice one!