Remaining stock - Formulae problem

[Villalobos]

Hello, I would like ask some help regarding the remaining stock calculation. Could somebody offer a formulae into the column C "Remaing stock"?! The sample table can be seen below. The formulae what I use now can be seen below also, but unfortunately does not take into account the deliveries, my problem is that I can not integrate it. How should I modify it?

Stock</SPAN>	Delivered</SPAN>	Remaining stock</SPAN>	PN</SPAN>	Confirmed quantity</SPAN>	Open quantity after delivery</SPAN>
2000</SPAN>	2000</SPAN>	0</SPAN>	A</SPAN>	1000</SPAN>	0</SPAN>
0</SPAN>	0</SPAN>	-2000</SPAN>	A</SPAN>	3000</SPAN>	2000</SPAN>
0</SPAN>	0</SPAN>	-4000</SPAN>	A</SPAN>	2000</SPAN>	2000</SPAN>
2500</SPAN>	2500</SPAN>	0</SPAN>	B</SPAN>	500</SPAN>	0</SPAN>
0</SPAN>	0</SPAN>	-8000</SPAN>	A</SPAN>	4000</SPAN>	4000</SPAN>
3000</SPAN>	1500</SPAN>	1500</SPAN>	C</SPAN>	1500</SPAN>	0</SPAN>
0</SPAN>	0</SPAN>	500</SPAN>	C</SPAN>	1000</SPAN>	0</SPAN>
0</SPAN>	0</SPAN>	0</SPAN>	B</SPAN>	1500</SPAN>	0</SPAN>
0</SPAN>	0</SPAN>	-2000</SPAN>	B</SPAN>	2500</SPAN>	2000</SPAN>
0</SPAN>	0</SPAN>	-5500</SPAN>	B</SPAN>	3500</SPAN>	3500</SPAN>
0</SPAN>	0</SPAN>	-2000</SPAN>	C</SPAN>	2500</SPAN>	2000</SPAN>
					=IFERROR(SUMIF('Sheet2'!$A$1:$B$2;'Sheet1'!D2;'Sheet2'!$A$1:$B$2)-(SUMIF($D$2:$D2;D2;$F$2:$F2));-(F2))</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[Villalobos]

Any suggest if you have?

 

[PaddyD]

Care to describe the logic of the formula you're after in words...

 

[Villalobos]

Hello PaddyD, Sorry for the late answer, so... for C is that if there are non zero values in A and B then the formula is A-B otherwise if in column D there is an A or B then find the previous value in C for that value in D and subtract the value in column F and if there is a C in column D then find the previous value in column C for a C in D and subtract the value in column E

 

[Villalobos]

I have the following formulae but this is not able to handle if a "PN" only one time in the list, and not able to handle if the value: e.g. in A2 > 0 and B2=0 because I get #REF! error message. IF(AND(A2>0,B2>0),A2-B2,OFFSET($C$1,LARGE(($D2=$D$1:$D1)*ROW($D$1:D1),1)-1,0)-OFFSET(E2,0,IF(D2<>"c",1,0))) with ctrl+shift+enter. Do you have any idea?

 

[Villalobos]

Hello, Could somebody offer a solution for that?

 

[PaddyD]

Hi - festive season & all that, so haven't been around.

Other than having the oddest data set up I've seen for a while, i can't follow your logic. e.g.:

"...for C is that if there are non zero values in A and B then the formula is A-B otherwise if in column D there is an A or B then find the previous value in C for that value in D and subtract the value in column F"

So, looking at your table, in the first row there are values in a & b, so subtract them. 2000 - 2000 = 0, which is what's in the table
row 2 - no values in a & b so seems like "if in column D there is an A or B then find the previous value in C for that value in D and subtract the value in column F" should hold. 0 - 0 = 0 but you've got -2000

???