Want FIFO Based Purchase Price of Goods Sold

[CA_Punit]

I want to get the purchase price of goods sold on the basis of FIFO .
Example of Data

I have tried but not able to get Quantity

New Microsoft Excel Worksheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
3		Item	Quantity Purchased	Rate	Cumulative Total
4		Part 1	10	100	10
5		Part 1	10	110	20
6			20	120	40
7			10	130	50
8
9
10
11		Quantity Sold		Sale Price	Cumulative Quantity	Fifo Based Purchase Price
12			11		11
13			31	115	42		#DIV/0!			10
14							9			10
15							29			20
16							39			10
17
18
19
Sheet1

Cell Formulas
Range	Formula
E4:E7	E4	=SUMPRODUCT($C$3:C4)
E12:E13	E12	=SUM($C$11:C12)
G13:G16	G13	=1/(1/(IF($E$4:$E$7-E12<=0,0,$E$4:$E$7-E12)))
J13:J16	J13	=INDEX($C$4:$C$7,ROW(INDIRECT("1:"&AGGREGATE(15,6,1/(1/(NOT(C13>=IF($E$4:$E$7-E12<=0,0,$E$4:$E$7-E12))*(ROW($E$4:$E$7)-ROW($E$4)+1))),1))))
Dynamic array formulas.

Hope someone could help

 

[Bo_Ry]

Please change E4 to
=SUM($C$3:C3)

then F12
=SUMPRODUCT(TEXT(E12-C12*{0,1}-$E$4:$E$7,"0;\0;0")*($D$4:$D$7-N(+$D$3:$D$6))*{1,-1})


I have my Thai clip to explain, Maybe you might get some idea on how the formula works.

Book1
	A	B	C	D	E	F	G	H
1
2
3		Item	Quantity Purchased	Rate	Cumulative Total
4		Part 1	10	100	0
5		Part 1	10	110	10
6			20	120	20
7			10	130	40
8
9
10
11		Quantity Sold		Sale Price	Cumulative Quantity	Fifo Based Purchase Price		Manual check
12			11		11	1110		1110
13			31	115	42	3650		3650
Sheet1

Cell Formulas
Range	Formula
E4:E7	E4	=SUM($C$3:C3)
E12:E13	E12	=SUM($C$11:C12)
F12:F13	F12	=SUMPRODUCT(TEXT(E12-C12*{0,1}-$E$4:$E$7,"0;\0;0")*($D$4:$D$7-N(+$D$3:$D$6))*{1,-1})
H12	H12	=10*D4+1*D5
H13	H13	=9*D5+20*D6+2*D7

 

[CA_Punit]

Thanks @Bo_Ry

For your help. My formula turned out to be Almost Half Page long.
I have learnt the following today from you

New Microsoft Excel Worksheet.xlsx
	W	X
3	N(+	I would have Used If error
4	"/0"	In Text to convert (-) number to Zero
Sheet2

Thanks

 

[jasonb75]

Different method, same results.

Book2
	B	C	D	E	F
3	Item	Quantity Purchased	Rate	Cost variance	Total previous purchase qty
4	Part 1	10	100	100	0
5	Part 1	10	110	10	10
6		20	120	10	20
7		10	130	10	40
8
9
10
11	Quantity Sold		Sale Price	Cumulative Quantity	Fifo Based Purchase Price
12		11		11	1110
13		31	115	42	3650
Sheet31

Cell Formulas
Range	Formula
E4:E7	E4	=D4-N(D3)
F4:F7	F4	=SUM(C$3:C3)
E12:E13	E12	=SUM($C$12:C12)
F12:F13	F12	=SUMPRODUCT(--(E12>$F$4:$F$7),E12-$F$4:$F$7,$E$4:$E$7)-SUM(F$11:F11)