Cumulative Sum untill a value reached and reset

Sadia1989

New Member
Joined
Oct 3, 2019
Messages
8
I have a data set look like

Road_IDV1V1cumV2V2cumV3V3cum
1220011
1463312
1394746
13122917
2004422
2333724
2471837
21831118
208213412
2210114012

<tbody>
</tbody>

each Road_ID has several rows. V1cum, V2cum and V3cum are cumulative sum of V1, V2 and V3 respectively which starts from zero after finishing each Road_ID. I need to restart cumulative sum after reaching 5 in V1cum and after finishing a Road_ID.

My expected result is
Road_IDV1V1cumV2V2cumV3V3cum
1220011
1463312
1334746
1362211
2004422
2333724
2471137
2113411
2012645
2231705

<tbody>
</tbody>
Please help me reaching this result.

Thanks,
Sadia
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,404
Office Version
365
Platform
Windows
Welcome to the MrExcel board!

Hopefully you just made a mistake with the final expected value for V2cum as it follows a number greater than 5?

Try this copied down then copy that whole column of formuls to V2cum and V3cum

Excel Workbook
ABCDEFG
1Road_IDV1V1cumV2V2cumV3V3cum
21220011
31463312
41334746
51362211
62004422
72333724
82471137
92113411
102012645
112231105
Cumulative Sums
 

Sadia1989

New Member
Joined
Oct 3, 2019
Messages
8
I have a data set of roadway and roadway segments vehicle count for calculations. Data set is just like before. But I changed the values for better understanding.

Data set contains

Road_ID= ID of each road
Seg_num= Index of each segment in each Road_ID
V1= Vehicle counts of type 1 in each Seg_num
V2= Vehicle counts of type 2 in each Seg_num
V3= Vehicle counts of type 3 in each Seg_num
V1cum= Cumulative sum of V1 by Seg_num and Road_ID,
V2cum= Cumulative sum of V2 by Seg_num and Road_ID,
V3cum= Cumulative sum of V3 by Seg_num and Road_ID.
Data frame looks like:

Road_IDSeg_numV1V1cumV2V2cumV3V3cum
11001100
12000100
133535141511
14205572234
15237832526
16280025915
17484328015
18387432116
19390234117
110090034421
111090034627
11217107741027
1133110243128
1140110447230
21220011
22463312
23394746
243122917
2501241329

<tbody>
</tbody>
I need to restart cumulative sum in V1cum, V2cum and V3cum after reaching 5 in all these (V1cum, V2cum and V3cum) columns and also when starting a new Road_ID. And get the cumulative sum values from V1cum, V2cum and V3cum before restarting the cumulative sum and place in each row of that range in separate column named, V1sum, V2sum and V3sum.

My expected result is

Road_IDSeg_numV1V1cumV1sumV2V2cumV2sumV3V3cumV3sum
1100781125006
1200780125006
13353578141525116
1420557872225346
1523787832525266
162290079910
174693370910
1839947711010
1933202291111
11003200294511
111032002961111
11217202079901111
113333226113
114003466233
21229007116
22469337126
23399477466
24333226113
25003466233

<tbody>
</tbody>


Please help me reaching this result. V1cum, V2cum, V3cum columns are for better understanding, but I need only columns V1sum, V2sum and V3sum columns.

Thanks in advance.

Sadia
 

Watch MrExcel Video

Forum statistics

Threads
1,095,221
Messages
5,443,169
Members
405,219
Latest member
CraneS

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top