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

ABHISKV4

New Member
Joined
May 26, 2009
Messages
26
Office Version
365
Platform
Windows
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
BCDEFGHIJKLMNOPQRS
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813
Sheet1 (2)
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=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
ABCDEFGHIJKLMNOPQRST
7
81559510915968867311910274488894195160Finding Sum >= 813No. of cells such that >=813Remarks
91551552503595185866727458649661040108811761270146516258648Tie for 8 cell range, but not final answer as 864 is <= 880
10959520436343151759070981188593310211115131014708859
111091092683364224956147167908389261020121513758389
12159159227313386505607681729817911110612668179
136868154227346448522570658752947110794710
14868615927838045450259068487910398799
1573731922943684165045987939539539
161191192212953434315257208808808Tie for 8 cell range, this is final answer as 880 is >= 864
1710210217622431240660176100
18747412221030449965900
19484813623042558500
20888818237753700
21949428944900
2219519535500
23160
24
Sheet1 (2)
Cell Formulas
RangeFormula
C9C9=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,D9P9=O9+P$8
R9:R22R9=MIN(IF(D9:Q9>=$S$3,D9:Q9,""))
S9:S22S9=IF(R9=0,0,COUNTA($C9:INDEX(C9:Q9,0,MATCH(R9,C9:Q9,0))))
P22P22=P8
D10D10=D8
E11E11=E8
F12F12=F8
G13G13=G8
H14H14=H8
I15I15=I8
J16J16=J8
K17K17=K8
L18L18=L8
M19M19=M8
N20N20=N8
O21O21=O8
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,559
Office Version
2019
Platform
Windows
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
BCDEFGHIJKLMNOPQRST
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813880
Sheet1
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=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

New Member
Joined
May 26, 2009
Messages
26
Office Version
365
Platform
Windows
@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

Well-known Member
Joined
Dec 30, 2008
Messages
10,559
Office Version
2019
Platform
Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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
ABCDEFGHIJKLMNOPQRST
1
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813880
Columns to sum
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=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)
 
Last edited:

ABHISKV4

New Member
Joined
May 26, 2009
Messages
26
Office Version
365
Platform
Windows
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

New Member
Joined
May 26, 2009
Messages
26
Office Version
365
Platform
Windows
=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
45,918
Office Version
365
Platform
Windows
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.

VBA Code:
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
ABCDEFGHIJKLMNOPQRST
1
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813880
Columns to sum
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=MinCellsVal(C3:Q3,S3)
 

ABHISKV4

New Member
Joined
May 26, 2009
Messages
26
Office Version
365
Platform
Windows
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 :)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,857
Messages
5,507,756
Members
408,647
Latest member
Nicho la zido

This Week's Hot Topics

Top