Calculate average cost of goods

[SavingsJoe]

Hi

I have a sheet with the cost of the goods we purchased, my current stock is 160 apples and 90 oranges how do i calculate the cost of each item which are currently in stock, I dont want the average cost from the first purchase, this should be the calculated using the cost of the latest purchase, so for the apples it would calculate 120 @ £1.65 and 40 at £1.52
thanks

Date	Item	Qty	Cost
01/01/20	Apples	120	£1.81​
01/01/20	Oranges	120	£1.28​
03/03/20	Apples	120	£1.52​
03/03/20	Oranges	120	£1.35​
06/06/20	Apples	120	£1.65​
06/06/20	Oranges	120	£1.34​

 

[Eric W]

Welcome to MrExcel!

You could do something like this:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Item	Qty	Cost	Total stock purchased	Amount to use for this date		Item	Qty	Cost of inventory (FIFO)
2	1/1/2020	Apples	120	1.81	360	0		Apples	160	258.8
3	1/1/2020	Oranges	120	1.28	360	0		Oranges	90	120.6
4	3/3/2020	Apples	120	1.52	240	40
5	3/3/2020	Oranges	120	1.35	240	0
6	6/6/2020	Apples	120	1.65	120	120
7	6/6/2020	Oranges	120	1.34	120	90
8
Sheet14

Cell Formulas
Range	Formula
E2:E7	E2	=SUMIFS($C2:$C$7,$B2:$B$7,B2)
F2:F7	F2	=MIN(C2,VLOOKUP(B2,$H$2:$I$3,2,0)-SUMIFS($F3:$F$8,$B3:$B$8,B2))
J2:J3	J2	=SUMPRODUCT(--($B$2:$B$7=H2),$D$2:$D$7,$F$2:$F$7)

You'd be adding the E and F helper columns. You can hide them if you don't want to see them. Then the cost of inventory in J2 is a pretty basic SUMPRODUCT formula. I'm thinking about what it would take to create a single-cell formula, but MIN, SUMIFS, and variable ranges are all tricky elements to include in an array formula, and all together? Well . . .

The other option would be a UDF (User Defined Function) which could be written in VBA. It would be a much simpler function, but it would require you to enable macros.

 

[SavingsJoe]

Thanks Eric for your quick response, it does the job for me

 

[Eric W]

Glad it works for you. Thanks for the update.