Loop thru range within groupings not to exceed a sum of 90

[FryGirl]

Hello and Happy New Year too all,

I have a range of cells that in column R are the count of number of words in column Q by row. I did not post the contents of column Q because they are not so much in the question. In column S, I now have the cumulative total starting from R2. Once the cumulative total in column S is less than or equal to 90, that would be the first group in the loop. The next loop would start in R7 and count until the cumulative total is less than or equal to 90. And so on.

With the set of data, I would expect 11 grouping, or the loop to process 11 times. How can I achieve this loop, of is there a formula I can use in column S to get me the highest number in each grouping less than 90. Basically, what I'm doing, column Q has some text that I'm transferring over to ppt. I have all of that working, but right now it's static to just doing every three rows. I would like to make it dynamic to select any number of rows but not greater than 90. I hope this make sense.

R2:R6 = 90
R7:R9 = 67
R10:R12 = 79
R13:R15 = 76
R16:R19 = 90
and so on...

11	11
29	40
12	52
16	68
22	90
24	114
26	140
17	157
25	182
23	205
31	236
30	266
11	277
35	312
23	335
27	362
16	378
24	402
11	413
27	440
35	475
23	498
11	509
30	539
34	573
51	624
22	646
45	691
36	727
39	766
25	791

 

[DanteAmor]

Hello and happy new year to you too.

Check if this result is what you need:

Dante Amor
	A																Q	R	S
1																		Count	Group
2																		11
3																		29
4																		12
5																		16
6																		22	90
7																		24
8																		26
9																		17	67
10																		25
11																		23
12																		31	79
13																		30
14																		11
15																		35	76
16																		23
17																		27
18																		16
19																		24	90
20																		11
21																		27
22																		35	73
23																		23
24																		11
25																		30	64
26																		34
27																		51	85
28																		22
29																		45	67
30																		36
31																		39	75
32																		25	25
Hoja3

Cell Formulas
Range	Formula
S2:S32	S2	=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))

 

[FryGirl]

Hi Dante and thanks a lot for the help,

Now I need to get a number I can use to build the resize component. As you can see in column T, I started to work on a formula, but the results should be as in column U.

Count	Group	Resize
11
29
12
16
22	90	5	5
24
26
17	67	8	3
25
23
31	79	11	3
30
11
35	76	14	3
23
27
16
24	90	18	4
11
27
35	73	21	3
23
11
30	64	24	3
34
51	85	26	2
22
45	67	28	2
36
39	75	30	2
25	25	31	1

 

[FryGirl]

Hi Dante, Here is what I've come up with.

U2 and Down -- =IF(N(S2),COUNT($R$2:R2)-MAX($T$1:T1),"")

Not sure if there is a way to combine column T and U into one

T2 and Down -- =IF(N(S2),COUNT($R$2:R2)-T1,"")

Count	Group	Resize
11
29
12
16
22	90	5	5
24
26
17	67	8	3
25
23
31	79	11	3
30
11
35	76	14	3
23
27
16
24	90	18	4
11
27
35	73	21	3
23
11
30	64	24	3
34
51	85	26	2
22
45	67	28	2
36
39	75	30	2
25	25	31	1

 

[DanteAmor]

Try this:

Dante Amor
	A																Q	R	S	T
1																		Count	Group	Resize
2																		11
3																		29
4																		12
5																		16
6																		22	90	5
7																		24
8																		26
9																		17	67	3
10																		25
11																		23
12																		31	79	3
13																		30
14																		11
15																		35	76	3
16																		23
17																		27
18																		16
19																		24	90	4
20																		11
21																		27
22																		35	73	3
23																		23
24																		11
25																		30	64	3
26																		34
27																		51	85	2
28																		22
29																		45	67	2
30																		36
31																		39	75	2
32																		25	25	1
Hoja3

Cell Formulas
Range	Formula
S2:S32	S2	=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))
T2:T32	T2	=IF(R3="",COUNT($R$2:R2)-SUM($T$1:T1),IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",COUNT($R$2:R2)-SUM($T$1:T1)))

 

[DanteAmor]

Or this:

Dante Amor
	A																Q	R	S	T
1																		Count	Group	Resize
2																		11
3																		29
4																		12
5																		16
6																		22	90	5
7																		24
8																		26
9																		17	67	3
10																		25
11																		23
12																		31	79	3
13																		30
14																		11
15																		35	76	3
16																		23
17																		27
18																		16
19																		24	90	4
20																		11
21																		27
22																		35	73	3
23																		23
24																		11
25																		30	64	3
26																		34
27																		51	85	2
28																		22
29																		45	67	2
30																		36
31																		39	75	2
32																		25	25	1
Hoja3

Cell Formulas
Range	Formula
S2:S32	S2	=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))
T2:T32	T2	=IF(S2="","",COUNT($R$2:R2)-SUM($T$1:T1))

 

[FryGirl]

That is absolutely perfect. Thank you!

 

[Peter_SSs]

That is absolutely perfect. Thank you!

Just checking ..
I have altered one of the column R values (yellow cell). Doesn't that make the orange cell incorrect? Perhaps I have not understood properly but I was thinking that the final row value should then be 81 per the modified formula in column T?

23 01 03.xlsm
	R	S	T
1	Count	Group	Group
2	11
3	29
4	12
5	16
6	22	90	90
7	24
8	26
9	17	67	67
10	25
11	23
12	31	79	79
13	30
14	11
15	35	76	76
16	23
17	27
18	16
19	24	90	90
20	11
21	27
22	35	73	73
23	23
24	11
25	30	64	64
26	34
27	51	85	85
28	22
29	45	67	67
30	36
31	20
32	25	25	81
Groups

Cell Formulas
Range	Formula
S2:S32	S2	=IF(R3="",R2,IF(SUM($R$2:R3)-SUM($S$1:S1)<=90,"",SUM($R$2:R2)-SUM($S$1:S1)))
T2:T32	T2	=IF(OR(SUM($R$2:R3)-SUM($T$1:T1)>90,R3=""),SUM($R$2:R2)-SUM($T$1:T1),"")

 

[FryGirl]

Hi Peter, yes, after some testing, that is correct. Your new updated formula caught it. Thanks.

 

[Peter_SSs]

You're welcome. Thanks for the confirmation.