Group sum in each row according to some condition set in columns

[Sadia1989]

I have a data set of roadway and roadway segments vehicle count calculations.
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:

[TABLE="width: 338"]
<tbody>[TR]
[TD]Road_ID[/TD]
[TD]Seg_num[/TD]
[TD]V1[/TD]
[TD]V1cum[/TD]
[TD]V2[/TD]
[TD]V2cum[/TD]
[TD]V3[/TD]
[TD]V3cum[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]80[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]84[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]87[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]32[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]34[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]107[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]27[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]28[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]
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

[TABLE="width: 563"]
<tbody>[TR]
[TD]Road_ID[/TD]
[TD]Seg_num[/TD]
[TD]V1[/TD]
[TD]V1cum[/TD]
[TD]V1sum[/TD]
[TD]V2[/TD]
[TD]V2cum[/TD]
[TD]V2sum[/TD]
[TD]V3[/TD]
[TD]V3cum[/TD]
[TD]V3sum[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]78[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]


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

 

[Peter_SSs]

I'm struggling to understand.

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.

This seems to be the same requirement as in your other thread and, as far as I know, answered there. Your expected results here for V1Cum do not appear to match your description as I cannot see anything restarting after 5.

V1cum= Cumulative sum of V1 by Seg_num and Road_ID,

Even that does not make sense to me. In your sample, for any Road_ID every segment number is different so to me a cumulative sum by segment number would just be the V1 value for every row.


What is the logic for why/when 78 changes to 9 or 9 changes to 20 in the V1sum column?


Clearly I do not understand the problem so at this stage cannot sugget anything.

 

[Sadia1989]

Thank you so much for responding to my problem.

You are correct that this problem has the same requirement as in my previous thread. However it is more complicated after adding some other conditions. As you noticed that V1cum and V2cum did not restart after reaching 5. The reason behind that is, all the columns which are V1cum, V2cum and V3cum have to reach 5 simultaneously. Therefore, when V1cum>=5 and V2cum>=5 and V1cum>=5, the cumulative sum restarted in each of these columns.

I made mistake writing the definition of V1cum, V2cum and V3cum, the correction would be "not for each segment". V1cum is the cumulative sum of V1 for each roadway.

The logic behind changing the V1sum from 78 to 9 is that, 78 is the sum of the V1 when cumulative sum of V1, cumulative sum of V2 and cumulative sum of V3, all reaches 5. Therefore, V1sum is the sum of V1 column for the first five rows. Similarly V2sum and V3sum are the sum of V2 and V3 columns respectively for the first five rows. Therefore, I get what is the exposure of the vehicle type V1, V2 and V3 on the first 5 segments on the road with Road_ID 1.

I hope this clarifies your understanding.

Thanks,
Sadia

 

[Peter_SSs]

A bit clearer, thanks.
I get a different result to you for the highlighted cells below but the same for all the rest I think. Hopefully they are mistakes at your end (cumulative of x and 0 is x not 0 as it was in D11:D12, J8 etc?).

Each formula copied down and then each column copied to the corresponding other columns.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Road_ID	Seg_num	V1	V1cum	V1sum	V2	V2cum	V2sum	V3	V3cum	V3sum
2	1	1	0	0	78	1	1	25	0	0	6
3	1	2	0	0	78	0	1	25	0	0	6
4	1	3	35	35	78	14	15	25	1	1	6
5	1	4	20	55	78	7	22	25	3	4	6
6	1	5	23	78	78	3	25	25	2	6	6
7	1	6	2	2	9	0	0	7	9	9	10
8	1	7	4	6	9	3	3	7	0	9	10
9	1	8	3	9	9	4	7	7	1	10	10
10	1	9	3	3	20	2	2	9	1	1	11
11	1	10	0	3	20	0	2	9	4	5	11
12	1	11	0	3	20	0	2	9	6	11	11
13	1	12	17	20	20	7	9	9	0	11	11
14	1	13	3	3	3	2	2	6	1	1	3
15	1	14	0	3	3	4	6	6	2	3	3
16	2	1	2	2	9	0	0	7	1	1	6
17	2	2	4	6	9	3	3	7	1	2	6
18	2	3	3	9	9	4	7	7	4	6	6
19	2	4	3	3	3	2	2	6	1	1	3
20	2	5	0	3	3	4	6	6	2	3	3
Cumulative Sums (2)

 

[Sadia1989]

Dear Peter,

Thanks a ton! You solved my problem! I was struggling to solve this in other pieces of software, however failed to complete due to not knowing those very deeply.

In D2 cell formula, I changed "<=5" to "<5", and the whole thing worked like a magic to get the correct output!

I apologies for my mistakes in the cells you mentioned.

For the highlighted cells which have different values than the requirement (<5) as a result of being the last segments of a road. I want to combine those segment/segments with the latest previous segments, counting as a single summing cluster (which might violet the requirement by continuing summation after reaching 5). For example, V1sum is the sum of E19:E20=3 and E16:E18=9 and road finishes after E20. Therefore, I want to sum V1 from C16:C18 and get the sum of E16:E20=12. Now, I am trying to do it in a separate column. I would also appreciate your help in this regard.

Thanks again. You are a life saver!

 

[Peter_SSs]

For the highlighted cells which have different values than the requirement (<5) as a result of being the last segments of a road. I want to combine those segment/segments with the latest previous segments, counting as a single summing cluster (which might violet the requirement by continuing summation after reaching 5). For example, V1sum is the sum of E19:E20=3 and E16:E18=9 and road finishes after E20. Therefore, I want to sum V1 from C16:C18 and get the sum of E16:E20=12. Now, I am trying to do it in a separate column.

I'm afraid I do not understand that at all.
Can you show anything that would help make it clearer?

 

[Sadia1989]

Hi Peter,

I am trying to clarify what I want to do next.

Suppose, The current output is like

	A	B	C	D	E	F	G	H	I	J	K

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: center"]Road_ID[/TD]
[TD="align: center"]Seg_num[/TD]
[TD="align: center"]V1[/TD]
[TD="align: center"]V1cum[/TD]
[TD="align: center"]V1sum[/TD]
[TD="align: center"]V2[/TD]
[TD="align: center"]V2cum[/TD]
[TD="align: center"]V2sum[/TD]
[TD="align: center"]V3[/TD]
[TD="align: center"]V3cum[/TD]
[TD="align: center"]V3sum[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] , align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] , align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]

</tbody>

I want to merge the sum of Road_ID 1, seg_num 14 and 15 to the sum of seg_num 10 to 13 as it does not meet the criterion ">5 or =5". Similarly, in other cases too. Therefore, the corrected output would look like,

	A	B	C	D	E	F	G	H	I	J	K

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: center"]Road_ID[/TD]
[TD="align: center"]Seg_num[/TD]
[TD="align: center"]V1[/TD]
[TD="align: center"]V1cum[/TD]
[TD="align: center"]V1sum[/TD]
[TD="align: center"]V2[/TD]
[TD="align: center"]V2cum[/TD]
[TD="align: center"]V2sum[/TD]
[TD="align: center"]V3[/TD]
[TD="align: center"]V3cum[/TD]
[TD="align: center"]V3sum[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]78[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] , align: center"]23[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]14[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]19[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]9[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]20[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFCC00]#FFCC00[/URL] , align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]13[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]9[/TD]

</tbody>

I put, another column for V1sum_corrected= L2==IF(AND(A3=A2, OR(AND(E2>=5, E3<5), AND(H2>=5, H3<5), AND(K2>=5, K3<5 ))), E3+E2, E3), but it is not working properly. I would appreciate any help.


I am sorry for late reply. I was traveling last two days.

Thanks in advance.

Sadia

<tbody>
</tbody>

 

[Peter_SSs]

I want to merge the sum of Road_ID 1, seg_num 14 and 15 to the sum of seg_num 10 to 13 as it does not meet the criterion ">5 or =5". Similarly, in other cases too. Therefore, the corrected output would look like,

I can see your argument for the end segments for V1 and V3 (green cells) but I cannot see the reasoning fo V2 as the V2cum would again reach the criterion of >=5 in rows 15 and 20 using the previous calculation . Can you clarify further?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	Road_ID	Seg_num	V1	V1cum	V1sum	V2	V2cum	V2sum	V3	V3cum	V3sum
2	1	1	0	0	78	1	1	25	0	0	6
3	1	2	0	0	78	0	1	25	0	0	6
4	1	3	35	35	78	14	15	25	1	1	6
5	1	4	20	55	78	7	22	25	3	4	6
6	1	5	23	78	78	3	25	25	2	6	6
7	1	6	2	2	9	0	0	7	9	9	10
8	1	7	4	6	9	3	3	7	0	9	10
9	1	8	3	9	9	4	7	7	1	10	10
10	1	9	3	3	23	2	2	15	1	1	14
11	1	10	0	3	23	0	2	15	4	5	14
12	1	11	0	3	23	0	2	15	6	11	14
13	1	12	17	20	23	7	9	15	0	11	14
14	1	13	3	23	23	2	11	15	1	12	14
15	1	14	0	23	23	4	15	15	2	14	14
16	2	1	2	2	12	0	0	13	1	1	9
17	2	2	4	6	12	3	3	13	1	2	9
18	2	3	3	9	12	4	7	13	4	6	9
19	2	4	3	12	12	2	9	13	1	7	9
20	2	5	0	12	12	4	13	13	2	9	9
Cumulative Sums (4)

 

[Sadia1989]

Hi Peter,

Thank you so much for your response.

The requirement for adding the end segment to the previous one is, none of them can be <5. If all the end segments highlighted (green and blue) is greater than or equal to 5 then we can leave those portions as it is. If any of the end segment among V1, V2 and V3 is <5, we need to add all the end segments to their respective previous segment. Therefore, the summing range for V1, V2 and V3 would always be same.

I hope this explanation helps to understand.

Thanks,

Sadia

 

[Peter_SSs]

Can you define what you mean by an "end segment"? Since all the segments have different numbers, is it just the last row for each road?