Remaining value calculation problem

[Villalobos]

Hello Experts,

I would like ask some help regarding the remaining value 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 if a "PN" only one time in a list and that if the value: e.g. in A2 > 0 and B2=0 because I get #REF! error message. How should I modify it?

Here is the formulae: 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.

The explanatation to the formulae: 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.

The table:
Stock Delivered Remaining stock PN Confirmed quantity Open quantity after delivery
2000 2000 0 A 1000 0
0 0 -2000 A 3000 2000
0 0 -4000 A 2000 2000
2500 2500 0 B 500 0
0 0 -8000 A 4000 4000
3000 1500 1500 C 1500 0
0 0 500 C 1000 0
0 0 0 B 1500 0
0 0 -2000 B 2500 2000
0 0 -5500 B 3500 3500
0 0 -2000 C 2500 2000

Thanks in advance for the help!

[:) Sixthsense :)]

HI Villalobos,

Welcome to the forum

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Villalobos]

Hello Sixthsense ,

Thank you for your attention!
I have uploaded the sample workbook as you asked before, I hope that it contain every required information, if not, just ask.

[:) Sixthsense :)]

May be this...

In C2 Cell

=SUMIF($D$2:D2,D2,$A$2:A2)-SUMIF($D$2:D2,D2,$B$2:B2)

Drag it down...

[Villalobos]

I checked your formulae, but unfortunately doesn't help.

[:) Sixthsense :)]

unfortunately doesn't help.

Because I cannot understand your logic

[Villalobos]

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.

Explanation to the results of column C
Cell C2 =A2-B2
Cell C3 =C2-F3
Cell C4 =C3-F4
Cell C5 =A5-B5
Cell C6 =C4-F6
Cell C7 =A7-B7
Cell C8 =C7-E8
Cell C9 =C5-F9
Cell C10 =C9-F10
Cell C11 =C10-F11
Cell C12 =C8-E12
Cell C13 =A13-E13
Cell C14 =A14-E14
Cell C15 =C13-E15


Is it help?

[:) Sixthsense :)]

It will be very much easier to understand if you describe it with column headers instead of C, B F like that

[amit.wilson]

V - can you explain the logic for row 13 and row 14?

In row 14, A14-E14 = 0, but the "After" sheet shows a value of -2000.

Cheers

[Villalobos]

for "Remaining stock" is that if there are non zero values in "Stock" and "Delivered" then the formula is "Stock"-"Delivered" otherwise if in column "PN" there is an "Stock" or "Delivered" then find the previous value in "Remaining stock" for that value in "PN" and subtract the value in column "Open quantity after delivery" and if there is a "Remaining stock" in column "PN" then find the previous value in column "Remaining stock" for a "Remaining stock" in "PN" and subtract the value in column "Confirmed quantity".

I would like to integrate 2 new rule into the formulae:
#1. if there is a "Stock" and the "Delivered quantity" is 0 then show the formulae what is that quantity which is missing to the complete "Confirmed quantity" (row #13 and #15).
#2. if an item only one time in the column "PN" the "Remaining stock" show that how many pc missing to complete the "Confirmed quantity" (row #14).

@amit.wilson
I made mistake and you discovered it. I would like to ask a pardon from you and from you Sixthsense also!

The values in cell E14 and F14 on sheet "After" are incorrect, the good values in that cells are 4000 and 4000 as you can read on sheet "Before".

Basicly my target is that with this formulae to know how does it look likes the remaining stock after daily outbound deliveries.

[Villalobos]

Hello,

Could somebody help me in this issue?