Sum to a certain value, then reset the sum function

[DaWoff]

I have a column of data (column A). I need to find a way to sum the column up to a certain value, let's say 25,000,000. When the threshold is met, the sum function needs to 'reset' so that threshold is never exceeded.

My ultimate end state is to be able to group rows of data based on a summed threshold that will be flexible to sorts &/or filters.

Any and all assistance would be greatly appreciated!

Sample Data:
2,068,480
1,854,200
1,853,700
1,864,400
1,905,100
1,862,800
153,600
166,300
641,400
3,011,420
1,869,380
2,903,400
1,705,200
282,440
2,244,080
1,657,160
685,200
759,680
748,900
711,800
693,898
749,540
773,100
766,280
804,100
1,304,100
694,680
726,460
701,560
690,812
1,106,160
2,013,400
1,860,000
2,006,460
239,580
309,820
745,600
508,240
442,740
159,800
753,840
203,400
155,800
160,000
793,660
170,200
160,600
253,900
463,900
178,400
317,140
212,880
207,260
424,560
203,800
3,060,760
185,640
184,600
595,320
165,000
337,540
1,299,560
498,520
175,800
525,220
213,200
170,180
240,042
155,000
357,260
688,200
700,600
686,100
730,300
689,000
1,855,000
2,018,188
1,853,400
1,856,200
2,584,340
1,801,400
1,797,880

 

[SkipVought]

hi,

Use a helper column (B)
[tt]
=INT(SUM($A$2:A2)/Limit)
[/tt]
assuming your data starts in A2.

This yields GROUPS that do not exceed your limit. Limit is a named grange, containing your limiting value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaWoff]

Thank you for the response. This formula is grouping the numbers for the absolute value of the sum (i.e. a group when the total sum exceeds the next 25M). This is almost what I need. Where this particular formula is not meeting my needs is in group 1. The sum of groups 0 and 1 do not exceed 50M, however, the sum of group 1 alone exceeds 25M.

I need to ensure that no single group violates the limit.

 

[SkipVought]

Well did you play around with the formula as a basis for a solution?

What have you tried? What results did you get?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Try a rolling sum, using the previous value in the helper column PLUS the row's column A value, first testing to determine if that sum exceeds the limit. Seems to work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaWoff]

@Skip,

Thank you for the assistance. I am not sure that I understand how to write the formula for your last post.

I have the data values in column A, your initial formula previously posted in column B, a rolling sum in column C (=sum($A$1:A1), and the validation formula in column D (=IF(((C3+A4)<=25000000),SUM($A$3:A4),SUM(A4:A4)).

The false statement in column D is incorrect.

 

[SkipVought]

[tt]
=IF(B1+A2<Limit,B1+A2,A2)
[/tt]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaWoff]

@Skip, again thanks for all of the help. This latest formula will help ID the first group when it reaches the limit. After reaching the limit the first time, it just pulls the value from column A.

I am trying to find a way to 'reset' the counter so it start over and ID the next group.

 

[SkipVought]

It does exactly what you requested. Resets and sums to limit each time!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

DaWolf,

You do not seem to be trying things independently, using the suggestion posted to attempt to find a solution on your own. You MAY be, but your responses seem to me to indicate that you're simply sitting back and WAITING for a solution.

What do you say?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaWoff]

@Skip, I appreciate the assistance. This did exactly what I was needing. I have been working on so many different variations of formulas that I was confusing myself.

I appreciate your assistance with this!