Getting running total and balance

[amilaart]

Hi
Im new to here, I have following question

Date	Product	Production	Cumulative	Dispatch	Balance
1 Jan 17	Apple	10	10	0	10
2 Jan 17	Banana	12	12	0	12
3 Jan 17	Apple	15	25	14	11
4 Jan 17	Apple	16	27	14	13

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 41px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Cumulative

<tbody>
</tbody>

</body>I have Excel sheet like this, and have same column. Having six different product. Each product having unique dispatch value. As per above example, for apple need to dispatch once cumulative total exceed 14,
Cumulative total = Production+Balance-Dispatch

Currently Im manually select previous product balance cell when calculating cumulative balance total.
I tried to several way to automate this, but still failed.
Need your expert views to complete this task

Thank you in advance
regards
Amila

 

[amilaart]

https://drive.google.com/file/d/0B4bdjOhsvPUwSG91dkFFTmxOcjg/view?usp=sharing

attachment

 

[Eric W]

Maybe:

	A	B	C	D	E	F	G	H	I
1	Date	Product	Production	Cumulative	Dispatch	Balance		Product	Dispatch Value
2	1-Jan-17	Apple	10	10	0	10		Apple	14
3	2-Jan-17	Banana	12	12	0	12		Banana	14
4	3-Jan-17	Apple	15	25	14	11
5	4-Jan-17	Apple	16	41	14	13
6	5-Jan-17	Banana	25	37	28	9
7	6-Jan-17	Apple	5	46	14	4
8	7-Jan-17	Banana	6	43	14	1
9	8-Jan-17	Banana	9	52	0	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet23​

Worksheet Formulas Cell Formula D2 =SUMIFS(C$2:C2,B$2:B2,B2) E2 =IF(D2-SUMIFS(E$1:E1,B$1:B1,B2)>=VLOOKUP(B2,$H$2:$I$5,2,FALSE),D2-SUMIFS(E$1:E1,B$1:B1,B2)-MOD(D2-SUMIFS(E$1:E1,B$1:B1,B2),VLOOKUP(B2,$H$2:$I$5,2,FALSE)),0) F2 =SUMIFS(C$2:C2,B$2:B2,B2)-SUMIFS(E$2:E2,B$2:B2,B2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Eric W]

Here's a somewhat simpler set of formulas:

	A	B	C	D	E	F	G	H	I
1	Date	Product	Production	Cumulative	Dispatch	Balance		Product	Dispatch Value
2	1-Jan-17	Apple	10	10	0	10		Apple	14
3	2-Jan-17	Banana	12	12	0	12		Banana	14
4	3-Jan-17	Apple	15	25	14	11
5	4-Jan-17	Apple	16	41	14	13
6	5-Jan-17	Banana	25	37	28	9
7	6-Jan-17	Apple	5	46	14	4
8	7-Jan-17	Banana	6	43	14	1
9	8-Jan-17	Banana	9	52	0	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet24​

Worksheet Formulas Cell Formula D2 =SUMIFS(C$2:C2,B$2:B2,B2) E2 =D2-F2-SUMIFS($E$1:$E1,$B$1:$B1,B2) F2 =MOD(D2,VLOOKUP(B2,$H$2:$I$5,2,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>