Need formula

[punit83]

Hello,

I am looking for a formula, which can help me to calculate my cost of the goods at time of selling.
Attachment is for refrence.

just for info
a) in coloumn j4 unit of my 1st purchase are sold out and by balance unit is 0, so in coloumn j5 i want to take new cost from the date of purchase from 09/03/21.
b) in coloumn j9 cost of 3 units is average from 28/04/21 (2 @ 673.017) to 12/05/21 (1 @ 676.676)

Hope i could make understand my query with given example.
Thank in advance for any reply/help.

Need Formula.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	#	Date	Status	Scrip Name	Qty	Rate	Amount		C.O.G/Unit	Cost of Goods Total	Difference
2	01	19/11/20	Buy	Adani Enterprise	15	490.708	7,360.63			7,360.63	0.00
3	02	07/01/21	Sell	Adani Enterprise	-10	502.121	-5,021.21		490.708	-4,907.08	114.13
4	03	12/01/21	Sell	Adani Enterprise	-5	517.310	-2,586.55		490.708	-2,453.54	133.01
5	04	09/03/21	Buy	Adani Enterprise	10	673.017	6,730.17			6,730.17	0.00
6	05	28/04/21	Sell	Adani Enterprise	-8	644.594	-5,156.75		673.017	-5,384.14	-227.39
7	06	12/05/21	Buy	Adani Enterprise	11	676.676	7,443.43			7,443.43	0.00
8	07	13/05/21	Sell	Adani Enterprise	-10	700.483	-7,004.83		676.676	-6,766.76	238.07
9	08	18/10/21	Sell	Adani Enterprise	-3	68.082	-204.25		674.237	-2,022.71	-1,818.46
10		Total	8		0	-	1,560.64			0.00	-1,560.64
Sheet 1

Cell Formulas
Range	Formula
J3:J4,J8:J9,J6	J3	=[@[C.O.G/Unit]]*[@Qty]
K2:K9	K2	=[@[Cost of Goods Total]]-[@Amount]
J10	J10	=SUBTOTAL(109,[Cost of Goods Total])
K10	K10	=SUBTOTAL(109,[Difference])
A2:A9	A2	=ROW(A1)
C10	C10	=SUBTOTAL(103,[Status])
E10	E10	=SUBTOTAL(109,[Qty])
F10	F10	=IFERROR(Cs.Transaction27[[#Totals],[Amount]]/Cs.Transaction27[[#Totals],[Qty]],"-")
G10	G10	=SUBTOTAL(109,[Amount])