Minimum no. of consecutive cells such that the sum is greater than a target number

[ABHISKV4]

Query: Need to find minimum no. of consecutive cells, from above yellow highlighted range, such that the sum is >= "sum to Look for" (here 813). In case of a tie, i.e. say there are two sets of 8 cells which give value >= "sum to look for", then the block whose sum is higher, should be shown as the result.

EXCEL - Minimum range of cells such that the sum is greater than a target number 06Sep2020 FINAL.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2		Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	TOTAL	Sum to Look for
3	Sales Qty -->	155	95	109	159	68	86	73	119	102	74	48	88	94	195	160	1625	813
Sheet1 (2)

Cell Formulas
Range	Formula
R3	R3	=SUM(C3:Q3)
S3	S3	=ROUNDUP(50%*R3,0)

Further illustration can be seen below:

EXCEL - Minimum range of cells such that the sum is greater than a target number 06Sep2020 FINAL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
7
8			155	95	109	159	68	86	73	119	102	74	48	88	94	195	160	Finding Sum >= 813	No. of cells such that >=813	Remarks
9		155	155	250	359	518	586	672	745	864	966	1040	1088	1176	1270	1465	1625	864	8	Tie for 8 cell range, but not final answer as 864 is <= 880
10		95		95	204	363	431	517	590	709	811	885	933	1021	1115	1310	1470	885	9
11		109			109	268	336	422	495	614	716	790	838	926	1020	1215	1375	838	9
12		159				159	227	313	386	505	607	681	729	817	911	1106	1266	817	9
13		68					68	154	227	346	448	522	570	658	752	947	1107	947	10
14		86						86	159	278	380	454	502	590	684	879	1039	879	9
15		73							73	192	294	368	416	504	598	793	953	953	9
16		119								119	221	295	343	431	525	720	880	880	8	Tie for 8 cell range, this is final answer as 880 is >= 864
17		102									102	176	224	312	406	601	761	0	0
18		74										74	122	210	304	499	659	0	0
19		48											48	136	230	425	585	0	0
20		88												88	182	377	537	0	0
21		94													94	289	449	0	0
22		195														195	355	0	0
23		160
24
Sheet1 (2)

Cell Formulas
Range	Formula
C9	C9	=C8
P9:Q21,Q22,O9:O20,N9:N19,M9:M18,L9:L17,K9:K16,J9:J15,I9:I14,H9:H13,G9:G12,F9:F11,E9:E10,D9	P9	=O9+P$8
R9:R22	R9	=MIN(IF(D9:Q9>=$S$3,D9:Q9,""))
S9:S22	S9	=IF(R9=0,0,COUNTA($C9:INDEX(C9:Q9,0,MATCH(R9,C9:Q9,0))))
P22	P22	=P8
D10	D10	=D8
E11	E11	=E8
F12	F12	=F8
G13	G13	=G8
H14	H14	=H8
I15	I15	=I8
J16	J16	=J8
K17	K17	=K8
L18	L18	=L8
M19	M19	=M8
N20	N20	=N8
O21	O21	=O8
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

Perhaps another member can improve this, the formula works as required but is not very efficient.

Note:- Iterative calculation must be enabled for this to work. (Excel > File > Options > Formulas > check box for 'Enable iterative calculation' > Ok). If not done you will get a circular reference warning and the result will always be 0.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2		Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	TOTAL	Sum to Look for
3	Sales Qty -->	155	95	109	159	68	86	73	119	102	74	48	88	94	195	160	1625	813	880
Sheet1

Cell Formulas
Range	Formula
R3	R3	=SUM(C3:Q3)
S3	S3	=ROUNDUP(50%*R3,0)
T3	T3	=MAX(INDEX(VALUE(TEXT(SUBTOTAL(9,OFFSET($C3,0,COLUMN($C3:$Q3)-COLUMN($C3),1,TRANSPOSE(COLUMN($C3:$Q3))-COLUMN($C3)+1)),"[>"&$R3&"]\0;\0;0;")),0,AGGREGATE(15,6,(TRANSPOSE(COLUMN($C3:$Q3))-COLUMN($C3)+1)/(VALUE(TEXT(SUBTOTAL(9,OFFSET($C3,0,COLUMN($C3:$Q3)-COLUMN($C3),1,TRANSPOSE(COLUMN($C3:$Q3))-COLUMN($C3)+1)),"[>"&$R3&"]\0;\0;0;"))>=$S3),1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ABHISKV4]

@jasonb75 , formula works absolutely the way i wanted, many many thanks for your time and help on this. As rightly mentioned by you, now I look forward to more efficient formulas to achieve the same result, thanking everyone in advance for your contribution on this.

 

[jasonb75]

I'm not sure that a more efficient version will be possible without a matrix table like the second sheet in post 1. This was the only way that I could find to produce the matrix as part of a single formula.

One thing that I should mention is that it will not work properly if you have any negative values in the row from columns C to AE inclusive. Although not obvious from the formula, the range used crosses over the column with the formula and continues into a number of columns to the right, empty cells, positive numbers or text will not affect the formula but negative numbers or errors will.

 

[Peter_SSs]

Assuming no numerical values in the row to the left of the list (ie A3:B3), try. Not widely tested.

Edit: This is similar to Jason's approach except ..
- Using Excel 365's SEQUENCE function
- Overlapping the range to the left (including off the sheet) to avoid going over this or the other formula cells

20 09 06.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2			Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	TOTAL	Sum to Look for
3		Sales Qty -->	155	95	109	159	68	86	73	119	102	74	48	88	94	195	160	1625	813	880
Columns to sum

Cell Formulas
Range	Formula
R3	R3	=SUM(C3:Q3)
S3	S3	=ROUNDUP(50%*R3,0)
T3	T3	=AGGREGATE(14,6,INDEX(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3)))),AGGREGATE(15,6,SEQUENCE(COLUMNS(C3:Q3))/(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3))))>=S3),1),0),1)

 

[ABHISKV4]

One thing that I should mention is that it will not work properly if you have any negative values in the row from columns C to AE inclusive. Although not obvious from the formula, the range used crosses over the column with the formula and continues into a number of columns to the right, empty cells, positive numbers or text will not affect the formula but negative numbers or errors will.

Thanks for informing about all the precautions that I need to take to get the right result.
To be honest, I also doubt if a more efficient formula would be possible for the given situation.

 

[ABHISKV4]

=AGGREGATE(14,6,INDEX(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3)))),AGGREGATE(15,6,SEQUENCE(COLUMNS(C3:Q3))/(SUBTOTAL(9,OFFSET(Q3,,-SEQUENCE(,COLUMNS(C3:Q3),0),,-SEQUENCE(COLUMNS(C3:Q3))))>=S3),1),0),1)

This works like a charm, absolutely delighted once again with the solution, many thanks for your prompt help

 

[Peter_SSs]

You're welcome.

In case you may be interested in avoiding the fairly long worksheet formulas &/or worrying about overlapping ranges, another approach would be to employ a user-defined function. Here is one.

Function MinCellsVal(rng As Range, MinVal As Double) As Double
  Dim a As Variant
  Dim i As Long, j As Long, L As Long, Num As Long
  Dim S As Double
  
  a = Application.Index(rng.Value, 1, 0)
  Num = UBound(a)
  For L = 1 To Num
    For i = 1 To Num - L + 1
      S = 0
      For j = i To i + L - 1
        S = S + a(j)
      Next j
      If S >= MinVal And S > MinCellsVal Then MinCellsVal = S
    Next i
    If MinCellsVal > 0 Then Exit For
  Next L
End Function

ABHISKV4 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2			Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	TOTAL	Sum to Look for
3		Sales Qty -->	155	95	109	159	68	86	73	119	102	74	48	88	94	195	160	1625	813	880
Columns to sum

Cell Formulas
Range	Formula
R3	R3	=SUM(C3:Q3)
S3	S3	=ROUNDUP(50%*R3,0)
T3	T3	=MinCellsVal(C3:Q3,S3)

 

[ABHISKV4]

In case you may be interested in avoiding the fairly long worksheet formulas &/or worrying about overlapping ranges, another approach would be to employ a user-defined function. Here is one.

I can't thank you enough in words, that's just awesome. Glad and lucky to be part of such a helpful community

 

[Peter_SSs]

I can't thank you enough in words,

You just have.

Glad and lucky to be part of such a helpful community

Pleased that you have had good outcomes here.