# '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.

#### bosco_yip

##### Excel Ninja

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?

 date item status qty 31-Jan​ stock in 20​ 02-Feb​ stock in 50​ 05-Feb​ stock out 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

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 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:D2resolved)

Regards
Bosco
Million thanks sir!! My problem has been resolved...!!!

#### 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.

what is the formula that able solve this issue?

#### Attachments

• 9.6 KB Views: 1

#### 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.
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.

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.

#### Attachments

• 9.9 KB Views: 5

#### bosco_yip

##### Excel Ninja

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