• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

'Bring Forward' stock formula in excel

yande

New Member
Hi Master,

Can excel calculate the total of stock for selected date?
Here the story:

1) Balance stock in 31st Jan 2019 is 20.
2) 50 new stock up enter the warehouse on 2nd Feb 2019.
3) Stock sold on 5th Feb 2019 was 30.

I need to learn a formula that able to calculate the balance of stock when i select the date. For example:

1) I select date 30th Jan 2019. stock balance will appear 20.
2) i select date 1st Feb 2019, stock balance will appear 20.
3) i select date 3rd Feb 2019, stock balance will appear 70.
4) i select date 6th Feb 2019, stock balance will appear 40.

Note: My database for in and out stock was in same table.

Please help me. Teach me.

Tq so much in advance.
 

bosco_yip

Excel Ninja
63380

1] Stock in, out and balance table as per above picture.

2] "Input", D2 formula copied down :

=N(D1)+B2-C2

3] "Output", B8 enter array formula (confirm by pressing CTRL+SHIFT+ENTER 3 keystrokes together instead of just ENTER) and copied down :

=INDEX($D$2:$D$4,MATCH(MIN(ABS($A$2:$A$4-A8)),ABS($A$2:$A$4-A8),0))

Regards
Bosco
 
Last edited:

yande

New Member
View attachment 63380

1] Stock in, out and balance table as per above picture.

2] Output, B8 enter array formula (confirm by pressing CTRL+SHIFT+ENTER 3 keystrokes together instead of just ENTER) and copied down :

=INDEX($D$2:$D$4,MATCH(MIN(ABS($A$2:$A$4-A8)),ABS($A$2:$A$4-A8),0))

Regards
Bosco
tq so much sir! it make me able to create such an easy way for dashboard.

but how about the table like this?

dateitemstatusqty
31-Jan​
stockin
20​
02-Feb​
stockin
50​
05-Feb​
stockout
30​

because this is currently my record in excel. do i need to do pivot or excel have the formula for this kind of table?

tq sir for helping me.
 

bosco_yip

Excel Ninja
63382

Then, you add a new "Stock balance" in Column E

In E2, formula copied down ;

=SUMIF(C$2:C2,"in",D$2:D2)-SUMIF(C$2:C2,"out",D$2:D2)

Regards
Bosco
 

yande

New Member
View attachment 63380

1] Stock in, out and balance table as per above picture.

2] "Input", D2 formula copied down :

=N(D1)+B2-C2

3] "Output", B8 enter array formula (confirm by pressing CTRL+SHIFT+ENTER 3 keystrokes together instead of just ENTER) and copied down :

=INDEX($D$2:$D$4,MATCH(MIN(ABS($A$2:$A$4-A8)),ABS($A$2:$A$4-A8),0))

Regards
Bosco
Hi master,

I need to use for selected item. Below is the example.
63456
what is the formula that able solve this issue?
please help me. thanks in advance.
 

Attachments

yande

New Member
View attachment 63382

Then, you add a new "Stock balance" in Column E

In E2, formula copied down ;

=SUMIF(C$2:C2,"in",D$2:D2)-SUMIF(C$2:C2,"out",D$2:D2)

Regards
Bosco
Hi master,

After searching and trying a few try and error formula, i got the solution.
I'm glad to share with you. Please find the attachment.
But, after applied in my project, I'm facing another issue. In my database got multiple same date which is lead the result appear was from first latest date, but i want is the last one from latest date.

63472

As you can see in the picture above, date 6th-May show result 90, it take the first one from latest date(F10) and the correct answer should be in cell F11.

If you have the solution, please share with me.

Million thanks in advance.
 

Attachments

bosco_yip

Excel Ninja
63475

In C20, array formula (Ctrl+Shift+Enter) copied down :

=INDEX($F$5:$F$12,MATCH(MIN(IF($C$17=$C$5:$C$12,ABS(B20-($B$5:$B$12+(ROW($A$1:$A$8)/100))))),IF($C$17=$C$5:$C$12,ABS(B20-($B$5:$B$12+(ROW($A$1:$A$8)/100)))),0))

Regards
Bosco
 
Top