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.

Amount	Price per Unit	Total			Amount	Average per Unit
100	$3.00	$300.00		Purchased	1000	$3.25
150	$3.00	$450.00		Sold	600	$5.00
200	$2.00	$400.00
250	$3.00	$750.00
300	$4.50	$1,350.00		Left	400	$4.13
-600	$5.00	$(3,000.00)

<tbody>
</tbody>

 

[footoo]

Here's a macro.
You didn't provide column refs, so I've guessed.

Sub Bal_Unit_Cost()
Dim cel As Range, rng As Range, bal!, c%, v!
Set cel = Cells(Rows.Count, "F").End(xlUp)
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
bal = cel
For c = rng.Cells.Count To 1 Step -1
    If 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
cel(1, 2) = WorksheetFunction.Round(v / cel, 2)
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Could we use a helper column (which could be hidden)?
Formula in D2 is copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Amount	Price per Unit	Total	Remaining Cost			Amount	Average per Unit
2	100	3.00	300.00	0		Purchased	1000	3.25
3	150	3.00	450.00	0		Sold	600	5
4	200	2.00	400.00	0
5	250	3.00	750.00	300
6	300	4.50	1350.00	1350		Left	400	4.13
7	-600	5.00	(3000.00)	0
Average

 

[zmilot]

Thank you both for your responses, I am going to go with the hidden column with formulas because I like it to auto update.

 

[Peter_SSs]

Thank you both ..

You are very welcome.
Just keep an eye on the results for a while to make sure it is working as required as I have not tested it heavily.

 

[zmilot]

So I actually have another twist that I thought I was going to be able to take care of easily. If I am going to have 20-30 different types of items that I want to put into this same list I can't seem to figure out a way to keep them all separate. I am assuming MATCH or INDEX or something like that?

	A	B	C	D
1	Item	Amount	Price per Unit	Total
2	A	100	3.00	300.00
3	A	150	3.00	450.00
4	B	200	2.00	400.00
5	B	250	3.00	750.00
6	A	300	4.50	1350.00
7	A	-500	5.00	(2500.00)

<tbody>
</tbody>

<tbody></tbody>

 

[Peter_SSs]

I'm wondering how/where the results will be laid out.
I have added a couple more rows to the sample data. Can you manually calculate the results for this data and post those results so we can see what you are expecting & how it would be presented?

Book1
	A	B	C	D
1	Item	Amount	Price per Unit	Total
2	A	100	3	300
3	A	150	3	450
4	B	200	2	400
5	B	250	3	750
6	A	300	4.5	1350
7	A	-500	5	-2500
8	B	-350	4	-1400
9	B	100	5	500
Current Cost

 

[footoo]

Thank you both for your responses, I am going to go with the hidden column with formulas because I like it to auto update.

The following function will automatically update :

Function APU(cel As Range)
Dim rng As Range, bal!, c%, v!
Application.Volatile
Set rng = Range([A2], Cells(Rows.Count, "A").End(xlUp))
bal = cel
For c = rng.Cells.Count To 1 Step -1
    If 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
APU = WorksheetFunction.Round(v / cel, 2)
End Function

If the "Left" balance is in F6 (say), enter in G6 (or any other cell): =APU(F6)

 

[footoo]

So I actually have another twist that I thought I was going to be able to take care of easily. If I am going to have 20-30 different types of items that I want to put into this same list I can't seem to figure out a way to keep them all separate. I am assuming MATCH or INDEX or something like that?

	A	B	C	D
1	Item	Amount	Price per Unit	Total
2	A	100	3.00	300.00
3	A	150	3.00	450.00
4	B	200	2.00	400.00
5	B	250	3.00	750.00
6	A	300	4.50	1350.00
7	A	-500	5.00	(2500.00)

<tbody>
</tbody>

My suggestion requires a separate unique list of all the Items that appear in column A - for illustration purposes I have assumed the list in column E.

Put in F2 and fill down (to get the "Left" amount for each item): =SUMIF(A:A,E2,B:B)

Use this function in column G : =APU(F2)

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, 0) 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
APU = WorksheetFunction.Round(v / cel, 2)
End Function

 

[zmilot]

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

	A	B	C	D	E	F	G	H	I
1	Item	Amount	Price per Unit	Total				Amount	Average per Unit
2	A	100	3.00	300.00		A	Purchased	550	3.82
3	A	150	3.00	450.00			Sold	500	5.00
4	B	200	2.00	400.00			Left	50	4.50
5	B	250	3.00	750.00
6	A	300	4.50	1350.00		B	Purchased	550	3.00
7	A	-500	5.00	(2500.00)			Sold	350	4.00
8	B	-350	4.00	(1400.00)			Left	200	4.00
9	B	100	5.00	500.00

<tbody>
</tbody>