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?[TABLE="width: 305"]
<TBODY>[TR]
[TD="align: center"]Stock</SPAN>[/TD]
[TD="align: center"]Delivered</SPAN>[/TD]
[TD="align: center"]Remaining stock</SPAN>[/TD]
[TD="align: center"]PN</SPAN>[/TD]
[TD="align: center"]Confirmed quantity</SPAN>[/TD]
[TD="align: center"]Open quantity after delivery</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]2000</SPAN>[/TD]
[TD="align: center"]2000</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]A</SPAN>[/TD]
[TD="align: center"]1000</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]-2000</SPAN>[/TD]
[TD="align: center"]A</SPAN>[/TD]
[TD="align: center"]3000</SPAN>[/TD]
[TD="align: center"]2000</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]-4000</SPAN>[/TD]
[TD="align: center"]A</SPAN>[/TD]
[TD="align: center"]2000</SPAN>[/TD]
[TD="align: center"]2000</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]2500</SPAN>[/TD]
[TD="align: center"]2500</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]B</SPAN>[/TD]
[TD="align: center"]500</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]-8000</SPAN>[/TD]
[TD="align: center"]A</SPAN>[/TD]
[TD="align: center"]4000</SPAN>[/TD]
[TD="align: center"]4000</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]3000</SPAN>[/TD]
[TD="align: center"]1500</SPAN>[/TD]
[TD="align: center"]1500</SPAN>[/TD]
[TD="align: center"]C</SPAN>[/TD]
[TD="align: center"]1500</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]500</SPAN>[/TD]
[TD="align: center"]C</SPAN>[/TD]
[TD="align: center"]1000</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]B</SPAN>[/TD]
[TD="align: center"]1500</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]-2000</SPAN>[/TD]
[TD="align: center"]B</SPAN>[/TD]
[TD="align: center"]2500</SPAN>[/TD]
[TD="align: center"]2000</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]-5500</SPAN>[/TD]
[TD="align: center"]B</SPAN>[/TD]
[TD="align: center"]3500</SPAN>[/TD]
[TD="align: center"]3500</SPAN>[/TD]
[/TR]
[TR]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]0</SPAN>[/TD]
[TD="align: center"]-2000</SPAN>[/TD]
[TD="align: center"]C</SPAN>[/TD]
[TD="align: center"]2500</SPAN>[/TD]
[TD="align: center"]2000</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=IFERROR(SUMIF('Sheet2'!$A$1:$B$2;'Sheet1'!D2;'Sheet2'!$A$1:$B$2)-(SUMIF($D$2:$D2;D2;$F$2:$F2));-(F2))</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[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

???