Formula for Stocks Avg Cost

[punit83]

Hello !!!

Stock.xlsx
	A	B	C	D	E	F	G
1	DATE	CN#	SCRIP	S	UNIT	RATE	AMOUNT
2	28/01/22	N-2015019	KSK Energy Ventures	B	100	77.50	7,750.00
3	23/02/22	N-2015032	KSK Energy Ventures	S	60	85.50	5,130.00
4	25/02/22	N-2015038	KSK Energy Ventures	B	100	76.50	7,650.00
5	03/03/22	N-2015042	KSK Energy Ventures	S	50	80.50	4,025.00
6	20/03/22	N-2015054	KSK Energy Ventures	B	25	62.70	1,567.50
7	10/04/22	N-2015067	KSK Energy Ventures	S	25	63.50	1,587.50
8	24/04/22	N-2015076	KSK Energy Ventures	B	50	58.85	2,942.50
9	30/04/22	N-2015080	KSK Energy Ventures	S	50	57.02	2,851.00
10	31/07/22	N-2015145	KSK Energy Ventures	B	20	42.15	843.00
11	01/01/23	N-2016001	KSK Energy Ventures	S	20	51.85	1,037.00
Sheet1

 

[punit83]

Hello !!!

Stock.xlsx
	A	B	C	D	E	F	G
1	DATE	CN#	SCRIP	S	UNIT	RATE	AMOUNT
2	28/01/22	N-2015019	KSK Energy Ventures	B	100	77.50	7,750.00
3	23/02/22	N-2015032	KSK Energy Ventures	S	60	85.50	5,130.00
4	25/02/22	N-2015038	KSK Energy Ventures	B	100	76.50	7,650.00
5	03/03/22	N-2015042	KSK Energy Ventures	S	50	80.50	4,025.00
6	20/03/22	N-2015054	KSK Energy Ventures	B	25	62.70	1,567.50
7	10/04/22	N-2015067	KSK Energy Ventures	S	25	63.50	1,587.50
8	24/04/22	N-2015076	KSK Energy Ventures	B	50	58.85	2,942.50
9	30/04/22	N-2015080	KSK Energy Ventures	S	50	57.02	2,851.00
10	31/07/22	N-2015145	KSK Energy Ventures	B	20	42.15	843.00
11	01/01/23	N-2016001	KSK Energy Ventures	S	20	51.85	1,037.00
Sheet1

Column D : B=Buy / S= Sold

Need formula to calculate average cost for stock sold.

Eg: in row 3 when sold 60 unit that time want to calulate avg purchased cost from row 1

Stock.xlsx
	A	B	C	D	E	F	G	H	I
1	DATE	CN#	SCRIP	S	UNIT	RATE	AMOUNT	COS	TOTAL COS
2	28/01/22	N-2015019	KSK Energy Ventures	B	100	77.50	7,750.00
3	23/02/22	N-2015032	KSK Energy Ventures	S	60	85.50	5,130.00	77.50	4,650.00
Sheet1

Cell Formulas
Range	Formula
I3	I3	=E3*H3

and when in row 5 sold that time it calculates balance cost from row 2 + new purchased in row 4.
I think can be said running cost after.

Stock.xlsx
	L	M	N	O	P	Q
1	DATE	CN#	SCRIP	UNIT	RATE	AMOUNT
2	28/01/22	N-2015019	KSK Energy Ventures	40	77.50	3,100.00
3	23/02/22	N-2015032	KSK Energy Ventures	100	76.50	7,650.00
4				140	76.79	10,750.00
Sheet1 (2)

Cell Formulas
Range	Formula
Q2:Q3	Q2	=O2*P2
O4,Q4	O4	=SUM(O2:O3)
P4	P4	=Q4/O4

Thanxs in Advance