Getting running total and balance

A

amilaart

New Member
Joined
Feb 16, 2017
Messages
2
Hi
Im new to here, I have following question
[TABLE="class: outer_border, width: 400"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Product[/TD]
[TD]Production[/TD]
[TD]Cumulative[/TD]
[TD]Dispatch[/TD]
[TD]Balance[/TD]
[/TR]
[TR]
[TD]1 Jan 17[/TD]
[TD]Apple[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]2 Jan 17[/TD]
[TD]Banana[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3 Jan 17[/TD]
[TD]Apple[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]14[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]4 Jan 17[/TD]
[TD]Apple[/TD]
[TD]16[/TD]
[TD]27[/TD]
[TD]14[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 41px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 444"]
<tbody>[TR]
[TD="width: 78"]Cumulative

[/TD]
[/TR]
</tbody>[/TABLE]
</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
 
Sort by date Sort by votes
Maybe:

ABCDEFGHI
Apple
Banana

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]Product[/TD]
[TD="bgcolor: #FAFAFA"]Production[/TD]
[TD="bgcolor: #FAFAFA"]Cumulative[/TD]
[TD="bgcolor: #FAFAFA"]Dispatch[/TD]
[TD="bgcolor: #FAFAFA"]Balance[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Product[/TD]
[TD="bgcolor: #FAFAFA"]Dispatch Value[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA, align: right"]14[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA, align: right"]14[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]41[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]37[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]46[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]43[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]52[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet23​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=SUMIFS(C$2:C2,B$2:B2,B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=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)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=SUMIFS(C$2:C2,B$2:B2,B2)-SUMIFS(E$2:E2,B$2:B2,B2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Here's a somewhat simpler set of formulas:

ABCDEFGHI
Apple
Banana

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FAFAFA"]Date[/TD]
[TD="bgcolor: #FAFAFA"]Product[/TD]
[TD="bgcolor: #FAFAFA"]Production[/TD]
[TD="bgcolor: #FAFAFA"]Cumulative[/TD]
[TD="bgcolor: #FAFAFA"]Dispatch[/TD]
[TD="bgcolor: #FAFAFA"]Balance[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FAFAFA"]Product[/TD]
[TD="bgcolor: #FAFAFA"]Dispatch Value[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA, align: right"]14[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #FAFAFA, align: right"]2-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]12[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: #FAFAFA, align: right"]14[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FAFAFA, align: right"]3-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]15[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]16[/TD]
[TD="bgcolor: #FAFAFA, align: right"]41[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]25[/TD]
[TD="bgcolor: #FAFAFA, align: right"]37[/TD]
[TD="bgcolor: #FAFAFA, align: right"]28[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Apple[/TD]
[TD="bgcolor: #FAFAFA, align: right"]5[/TD]
[TD="bgcolor: #FAFAFA, align: right"]46[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #FAFAFA, align: right"]7-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]6[/TD]
[TD="bgcolor: #FAFAFA, align: right"]43[/TD]
[TD="bgcolor: #FAFAFA, align: right"]14[/TD]
[TD="bgcolor: #FAFAFA, align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]8-Jan-17[/TD]
[TD="bgcolor: #FAFAFA"]Banana[/TD]
[TD="bgcolor: #FAFAFA, align: right"]9[/TD]
[TD="bgcolor: #FAFAFA, align: right"]52[/TD]
[TD="bgcolor: #FAFAFA, align: right"]0[/TD]
[TD="bgcolor: #FAFAFA, align: right"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet24​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=SUMIFS(C$2:C2,B$2:B2,B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=D2-F2-SUMIFS($E$1:$E1,$B$1:$B1,B2)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=MOD(D2,VLOOKUP(B2,$H$2:$I$5,2,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

ExcelNewbie2020
  • Solved
Convert horizontal data to vertical with running balance
Replies
4
Views
218
alansidman
alansidman
A
  • Question Question
Daily Interest Loan Scheduler | Missed Payments
Replies
0
Views
280
aurelius33
A
K
  • Solved
Calculate Total Expense With Condition
2
Replies
10
Views
541
Cubist
Cubist
ExcelNewbie2020
  • Solved
sum function that will reset the running total every after 5 counts
Replies
3
Views
323
Cubist
Cubist
S
  • Question Question
How to sum and rank with multi criteria uniquely without duplicate? For excel 2019
Replies
3
Views
568
Sochen
S

Forum statistics

Threads
1,226,840
Messages
6,193,278
Members
453,788
Latest member
drcharle

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top