I would like to find the average cost of a particular item, searched on the calc sheet,
ie A2 = Item code / B2 = cost value / C2 = average purchase price / D2 auto adjusts if input says ‘Sold’ so that Stock Level does not count any sold item.
How would I create that ?
test example.ods (12.4 KB)
I won’t dare claim to have understood the question and the example.
However: The function AVERAGEIFS() might be your friend.
Me too. Anyway:
=AVERAGEIFS(B2:B99;A2:A99;A2;D2:D99;"Stock") [Edited to change comma for semicolon].
I have a spreadsheet i use for price calculations. I buy at differing rates, and each individual item has a stock code (marked under Item) - is listed multiple times in the spreadsheet (more than 30,000 items listed over the years) each has a unique - id number.
I needed to find a way to show the average price paid for every instance of a purchase of the item ‘CMP-NRC-RMC’ and then also if it an item was marked as ‘Sold’ it would not be displayed in the stock total for that item.
Following the next screenshot:
With and without stock? 3 (average of 1, 1 and 7)
Only with stock? 4 (average of 1 and 7)
An empty cell (or with 0) in column E?
One unit of item per row? Could it be more than one row with the same price?
Hello Leroy
the idea was to adjust the average purchase values, as each instance, was applied.
If an item sold, then a blank would be better than a numeric value.
The average cost, would be displayed including the sold value item value.
Thank you for your input.
C2: =AVERAGEIF(A$2:A$99;A2;B$2:B$99)
If the status values are only Sold and Stock, E2 could be =IF(D2="Stock";1;"") or =IF(D2="Sold";"";1)
test example_LeroyG.ods (13.9 KB)
Thank you for your help LeRoyG