Calculate most current price per unit from a list

[zmilot]

I am trying to figure out a way to get the most current average cost of however many units are still in stock.

An example is shown below of the type of list that I will keep adding to whenever I make a purchase or a sale. The part that I am having trouble calculating is the average cost of what is still in stock (in red). I can calculate it manually but would obviously prefer for it to be automated. I want the formula or code to take the 400 units that are in stock and start from the bottom of the list and take the 300 from the most recent purchase that cost $4.50 each and 100 from the next most recent purchase that cost $3.00 and calculate that the most recently purchased 400 units cost $4.13.

Any help would be appreciated.

[TABLE="width: 528"]
<tbody>[TR]
[TD]Amount[/TD]
[TD]Price per Unit[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD]Average per Unit[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD] $3.00[/TD]
[TD] $300.00[/TD]
[TD][/TD]
[TD]Purchased[/TD]
[TD="align: right"]1000[/TD]
[TD] $3.25[/TD]
[/TR]
[TR]
[TD="align: right"]150[/TD]
[TD] $3.00[/TD]
[TD] $450.00[/TD]
[TD][/TD]
[TD]Sold[/TD]
[TD="align: right"]600[/TD]
[TD] $5.00[/TD]
[/TR]
[TR]
[TD="align: right"]200[/TD]
[TD] $2.00[/TD]
[TD] $400.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]250[/TD]
[TD] $3.00[/TD]
[TD] $750.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]300[/TD]
[TD] $4.50[/TD]
[TD] $1,350.00[/TD]
[TD][/TD]
[TD]Left[/TD]
[TD="align: right"]400[/TD]
[TD] $4.13[/TD]
[/TR]
[TR]
[TD="align: right"]-600[/TD]
[TD] $5.00[/TD]
[TD] $(3,000.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[footoo]

Have you tried the Function per post #9 ?

 

[Peter_SSs]

It doesn't have to be exactly formatted like this but this was my general idea. So same as before I want A to calculate the most recently purchased 50 items and B to calculate the most recent 200 items. I am pretty good at deciphering what a formula does when I see it so I should be able to add additional items and reorder things later on. I just cant figure out a way to keep the different items separate.

Thanks again

If you have the layout set up like that, the formulas for all except the average for the remaining units and want to continue with a non-macro solution ..

E2 copied down
I4 copied to I8, I12, etc

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Item	Amount	Price per Unit	Total	Remaining Cost			Amount	Average per Unit
2	A	100	3	300	0	A	Purchased	550	3.82
3	A	150	3	450	0		Sold	500	5
4	B	200	2	400	0		Left	50	4.5
5	B	250	3	750	300
6	A	300	4.5	1350	225	B	Purchased	550	3
7	A	-500	5	-2500	0		Sold	350	4
8	B	-350	4	-1400	0		Left	200	4
9	B	100	5	500	500
Average Cost

 

[footoo]

Based on your format in post#10, the formulas :

H2 : =SUMIFS(B:B,A:A,F2,B:B,">0")
I2 : =SUMIFS(D:D,B:B,">0",A:A,F2)/H2
H3 : =-SUMIFS(B:B,A:A,F2,B:B,"<0")
H4 : =H2-H3
I4 : =APU(H4)

Function APU(cel As Range)
Dim rng As Range, bal!, c%, v!
Application.Volatile
Set rng = Range([B2], Cells(Rows.Count, "B").End(xlUp))
bal = cel
For c = rng.Cells.Count To 1 Step -1
    If rng(c)(1, 0) = cel(-1, -1) And rng(c) > 0 Then
        If bal > rng(c) Then
            v = v + rng(c) * rng(c)(1, 2)
            bal = bal - rng(c)
        Else
            v = v + bal * rng(c)(1, 2)
            Exit For
        End If
    End If
Next
If v <> 0 Then APU = WorksheetFunction.Round(v / cel, 2)
End Function

 

[footoo]

You might also want the Cost of Goods sold per unit :

=(SUMIFS(D:D,B:B,">0",A:A,F2)-(H4*I4))/H3

 

[zmilot]

Thank you both for your help and responses. I tried both of your solutions and they seem to work great. I am probably going to stick with using the strictly formula based answer simply because I can expand and troubleshoot them easier.

Thanks again!