Forum Discussion
Formula
I have a warehouse inventory list that I am working on. I have a warehouse inventory sheet, purchase sheet and an order sheet. I have item numbers for the warehouse products. I need the total cost of the item number purchases/by total quantity from the purchase sheet to give me the average cost. The average cost will be placed on the warehouse inventory and the order sheet. I believe the formula begins with sumif(), but I am not sure. Any guidance on this question is appreciated.
Thank you
Thank you! Where do you want the average?
7 Replies
The formula appears to be working. I need to take a class on formulas and functions.
Thank you
The average cost should be in the unit cost on the inventory sheet.
=IFERROR(SUMIFS(Table3[Total Cost], Table3[Item Number], [@[Inventory ID]])/SUMIFS(Table3[QTY], Table3[Item Number], [@[Inventory ID]]), "")
or if you prefer
=IFERROR(SUMIFS(Table3[Total Cost], Table3[Item Number], [@[Inventory ID]])/SUMIFS(Table3[QTY], Table3[Item Number], [@[Inventory ID]]), 0)
Most items will be purchased multiple times. So I want each transaction to be taken into account when coming up with the average cost.
For average, you should use AVERAGEIFS, but without knowing what your sheets look like it's hard to provide specific help.
Here is the file.
Thank you! Where do you want the average?
