Calculate most current price per unit from a list

Z

zmilot

New Member
Joined
Feb 23, 2016
Messages
5
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]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Here's a macro.
You didn't provide column refs, so I've guessed.
Code: 
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
 
Upvote 0
Welcome to the MrExcel board!

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

Excel Workbook
ABCDEFGH
1AmountPrice per UnitTotalRemaining CostAmountAverage per Unit
21003.00300.000Purchased10003.25
31503.00450.000Sold6005
42002.00400.000
52503.00750.00300
63004.501350.001350Left4004.13
7-6005.00(3000.00)0
Average
 
Upvote 0
Thank you both for your responses, I am going to go with the hidden column with formulas because I like it to auto update.
 
Upvote 0
zmilot said:
Thank you both ..
Click to expand...
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.
 
Last edited:
Upvote 0
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?


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Amount[/TD]
[TD]Price per Unit[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]3.00[/TD]
[TD]300.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]150[/TD]
[TD]3.00[/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[TD]200[/TD]
[TD]2.00[/TD]
[TD]400.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]250[/TD]
[TD]3.00[/TD]
[TD]750.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]300[/TD]
[TD]4.50[/TD]
[TD]1350.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A[/TD]
[TD]-500[/TD]
[TD]5.00[/TD]
[TD](2500.00)[/TD]
[/TR]
</tbody>[/TABLE]

<tbody></tbody>
 
Upvote 0
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
ABCD
1ItemAmountPrice per UnitTotal
2A1003300
3A1503450
4B2002400
5B2503750
6A3004.51350
7A-5005-2500
8B-3504-1400
9B1005500
Current Cost
 
Upvote 0
zmilot said:
Thank you both for your responses, I am going to go with the hidden column with formulas because I like it to auto update.
Click to expand...
The following function will automatically update :
Code: 
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)
 
Upvote 0
zmilot said:
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?


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Amount[/TD]
[TD]Price per Unit[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]3.00[/TD]
[TD]300.00[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]150[/TD]
[TD]3.00[/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[TD]200[/TD]
[TD]2.00[/TD]
[TD]400.00[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]250[/TD]
[TD]3.00[/TD]
[TD]750.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]300[/TD]
[TD]4.50[/TD]
[TD]1350.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A[/TD]
[TD]-500[/TD]
[TD]5.00[/TD]
[TD](2500.00)[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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)

Code: 
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
 
Upvote 0
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

[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item[/TD]
[TD]Amount[/TD]
[TD]Price per Unit[/TD]
[TD]Total[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD]Average per Unit[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]A[/TD]
[TD]100[/TD]
[TD]3.00[/TD]
[TD]300.00[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]Purchased[/TD]
[TD]550[/TD]
[TD]3.82[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]A[/TD]
[TD]150[/TD]
[TD]3.00[/TD]
[TD]450.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sold[/TD]
[TD]500[/TD]
[TD]5.00[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]B[/TD]
[TD]200[/TD]
[TD]2.00[/TD]
[TD]400.00[/TD]
[TD][/TD]
[TD][/TD]
[TD]Left[/TD]
[TD]50[/TD]
[TD]4.50[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]B[/TD]
[TD]250[/TD]
[TD]3.00[/TD]
[TD]750.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]A[/TD]
[TD]300[/TD]
[TD]4.50[/TD]
[TD]1350.00[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]Purchased[/TD]
[TD]550[/TD]
[TD]3.00[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]A[/TD]
[TD]-500[/TD]
[TD]5.00[/TD]
[TD](2500.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sold[/TD]
[TD]350[/TD]
[TD]4.00[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]B[/TD]
[TD]-350[/TD]
[TD]4.00[/TD]
[TD](1400.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD]Left[/TD]
[TD]200[/TD]
[TD]4.00[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]B[/TD]
[TD]100[/TD]
[TD]5.00[/TD]
[TD]500.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Calculating weighted average unit cost using histroical purchasing data
Replies
6
Views
855
itsmekamalkkr
I
H
  • Question Question
populate average price on listbox based on selected sheet from combobox
Replies
1
Views
313
RoryA
RoryA
A
  • Question Question
create report for 40000 rows to calculation stock across sheet based on sheet name in cell value
Replies
1
Views
345
Amer Omar
A
E
  • Question Question
FIFO Macro giving different results each time it runs
Replies
7
Views
1K
Lee Wei Sin
L
L
  • Locked
  • Solved
Calculate target margin, break-even and purchase order
Replies
1
Views
789
StephenCrump
S

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top