Average inventory on hand

[Jadeking]

How do I get the average cost of bats/balls we bought. I have 25 in inventory so average the last cost of 25 or whatever our inventory shows. I would need to use the date as I want the most recent average cost based on the inventory we have.
I want to get the average cost of the last 25 bats I bought. If we would only have 20 bats on inventory i would want the average of only the last 20 that we bought.

[Bernie Deitrick]

The attached solution requires that the items be grouped together, and sorted older on top.

[Jadeking]

Thank you for your reply. This works great except when I have only 5 balls left it shows my average is zero.

[Bernie Deitrick]

Here is an amended version that addresses that specific case

[Jadeking]

Awesome! Thanks so much!

[Jadeking]

Is there a way to do this without an Array formula? It really slows my very large workbook.

[PeteABC123]

Jadeking:

You might limit the amount of rows evaluated in this portion of the formula so it does not evaluate the entire column length
Instead of

Please Login or Register  to view this content.

Try something like:

Please Login or Register  to view this content.

Could help the speed.

Pete

[Bernie Deitrick]

Yes - what he said!

[Jadeking]

That don't seem to help anything. I have 150 different items and the products purchased is thousands of rows long. Would there be any other solution?

[Bernie Deitrick]

I would look to reduce the data set by:

1) removing items that have been fully consumed: the amount of the item bought later than their date is greater than the on-hand inventory number, so they can be deleted.
2) 'grouping' items that are bought consecutively at the same price. Might be worthwhile with low price volatility, not as useful if the prices change all the time.
3) using separate workbooks for each item - your table could be a power query from a main table, which would allow faster calculations for the item of interest.

[PeteABC123]

Did you try without the array formula acceptance? Just the formula works.