Hello, I've been trying to get this figured out and I just can't seem to get it. I'm trying to find the average cost per unit of my inventory purchased based on a serial number. prices flux everyday so not every per unit cost of each transaction is the same but I have different units being purchased on different days. On Sheet 1 i have a material list with the serial no. a description of the material and i want to show the avg unit cost there.
Serial No. ||| Material Description ||| Average Unit Cost
1 ||| A
2 ||| B
3 ||| C
4 ||| D
Now on Sheet 2 I have a purchasing list. I used the Vlookup function to be able to plug in different serial numbers and then show the material description then i have a column for the price of that transaction, the quantity of that transaction, and the average unit cost of THAT transaction only.
Serial No. ||| Material Description ||| Price ||| Quantity ||| Per unit cost
1 ||| A ||| $50.00 ||| 100 ||| $0.50
2 ||| B ||| $25.00 ||| 100 ||| $0.25
3 ||| C ||| $25.00 ||| 50 ||| $0.50
1 ||| A ||| $75.00 ||| 110 ||| $0.68
3 ||| C ||| $35.00 ||| 50 ||| $0.70
1 ||| A ||| $65.00 ||| 90 ||| $0.72
So as you can see I bought Serial No. 1 (Material A) 3 different times with a per unit cost of $0.50, $0.68, and $0.72. Now I want to be able to show the average cost ($0.63) on Sheet 1 as well as for the other purchases.
Thanks in advance for the help and if anything is confusing just let me know!
Bookmarks