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

A

ABHISKV4

New Member
Joined
May 26, 2009
Messages
31
Office Version
  1. 365
Platform
  1. 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.
 
Sort by date Sort by votes
Another formula
Book1
ABCDEFGHIJKLMNOPQRST
1
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3Sales Qty -->15595109159688673119102744888941951601625813880
Sheet2
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=MOD(AGGREGATE(15,6,TEXT(SUBTOTAL(9,OFFSET(B3,,SEQUENCE(COUNT(C3:Q3)),,IF(SEQUENCE(COUNT(C3:Q3))+SEQUENCE(,COUNT(C3:Q3))>COUNT(C3:Q3),15-SEQUENCE(COUNT(C3:Q3))+1,SEQUENCE(,COUNT(C3:Q3))))),"[>"&S3&"]0; ")-SEQUENCE(,COUNT(C3:Q3))*10^9,1),10^9)
 
Upvote 0

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Perhaps this sample data is not possible but I note some of the suggestions so far fail with it by my understanding of the requirement.
If that is so then perhaps there are other more realistic samples where they also fail?

ABHISKV4 1.xlsm
CDEFGHIJKLMNOPQRSTUVW
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
3155951091596886731191027410000889419516011577578911577100001000010537
Columns to sum (3)
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)))
U3U3=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)
V3V3=MinCellsVal(C3:Q3,S3)
W3W3=MOD(AGGREGATE(15,6,TEXT(SUBTOTAL(9,OFFSET(B3,,SEQUENCE(COUNT(C3:Q3)),,IF(SEQUENCE(COUNT(C3:Q3))+SEQUENCE(,COUNT(C3:Q3))>COUNT(C3:Q3),15-SEQUENCE(COUNT(C3:Q3))+1,SEQUENCE(,COUNT(C3:Q3))))),"[>"&S3&"]0; ")-SEQUENCE(,COUNT(C3:Q3))*10^9,1),10^9)
 
Upvote 0
Peter_SSs said:
perhaps there are other more realistic samples where they also fail?
Click to expand...
Like this?

ABHISKV4 1.xlsm
CDEFGHIJKLMNOPQRSTUVW
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
35070503080405030505010050604050800400480400400430
Columns to sum (4)
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)))
U3U3=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)
V3V3=MinCellsVal(C3:Q3,S3)
W3W3=MOD(AGGREGATE(15,6,TEXT(SUBTOTAL(9,OFFSET(B3,,SEQUENCE(COUNT(C3:Q3)),,IF(SEQUENCE(COUNT(C3:Q3))+SEQUENCE(,COUNT(C3:Q3))>COUNT(C3:Q3),15-SEQUENCE(COUNT(C3:Q3))+1,SEQUENCE(,COUNT(C3:Q3))))),"[>"&S3&"]0; ")-SEQUENCE(,COUNT(C3:Q3))*10^9,1),10^9)
 
Upvote 0
Correct my formula, it should be Aggregate(14
Book1
ABCDEFGHIJKLMNOPQRST
1
2Week1Week2Week3Week4Week5Week6Week7Week8Week9Week10Week11Week12Week13Week14Week15TOTALSum to Look for
35070503080405030505010050604050800400450
4
5
6 450500600650710750800
7 450550600660700750750
8 480530590630680680680
9 430480540580630630630630
10 450510550600600600600600
11 430470520520520520520520
12 430480480480480480480480
13 430430430430430430430430
Sheet1
Cell Formulas
RangeFormula
R3R3=SUM(C3:Q3)
S3S3=ROUNDUP(50%*R3,0)
T3T3=MOD(AGGREGATE(14,6,TEXT(SUBTOTAL(9,OFFSET(B3,,SEQUENCE(COUNT(C3:Q3)),,IF(SEQUENCE(COUNT(C3:Q3))+SEQUENCE(,COUNT(C3:Q3))>COUNT(C3:Q3),15-SEQUENCE(COUNT(C3:Q3))+1,SEQUENCE(,COUNT(C3:Q3))))),"[>"&S3&"]0; ")-SEQUENCE(,COUNT(C3:Q3))*10^9,1),10^9)
C6:Q20C6=TEXT(SUBTOTAL(9,OFFSET(B3,,SEQUENCE(COUNT(C3:Q3)),,IF(SEQUENCE(COUNT(C3:Q3))+SEQUENCE(,COUNT(C3:Q3))>COUNT(C3:Q3),15-SEQUENCE(COUNT(C3:Q3))+1,SEQUENCE(,COUNT(C3:Q3))))),"[>"&S3&"]0; ")
Dynamic array formulas.
 
Upvote 0
If you like to highlight the minimum adjacent cells which add up to 50%:
VBA Code: 
Option Explicit

Private Enum xlCI 'Excel Color Index
: xlCIBlack = 1: xlCIWhite: xlCIRed: xlCIBrightGreen: xlCIBlue '1 - 5
: xlCIYellow: xlCIPink: xlCITurquoise: xlCIDarkRed: xlCIGreen '6 - 10
: xlCIDarkBlue: xlCIDarkYellow: xlCIViolet: xlCITeal: xlCIGray25 '11 - 15
: xlCIGray50: xlCIPeriwinkle: xlCIPlum: xlCIIvory: xlCILightTurquoise '16 - 20
: xlCIDarkPurple: xlCICoral: xlCIOceanBlue: xlCIIceBlue: xlCILightBrown '21 - 25
: xlCIMagenta2: xlCIYellow2: xlCICyan2: xlCIDarkPink: xlCIDarkBrown '26 - 30
: xlCIDarkTurquoise: xlCISeaBlue: xlCISkyBlue: xlCILightTurquoise2: xlCILightGreen '31 - 35
: xlCILightYellow: xlCIPaleBlue: xlCIRose: xlCILavender: xlCITan '36 - 40
: xlCILightBlue: xlCIAqua: xlCILime: xlCIGold: xlCILightOrange '41 - 45
: xlCIOrange: xlCIBlueGray: xlCIGray40: xlCIDarkTeal: xlCISeaGreen '46 - 50
: xlCIDarkGreen: xlCIGreenBrown: xlCIBrown: xlCIDarkPink2: xlCIIndigo '51 - 55
: xlCIGray80 '56
End Enum

Sub sbHighlightMinAdjacentCells50P(r As Range)
'Highlights in pink the minimum adjacent cells of r which sum up to 50% of the overall total.
'If more than one range certifies then the max is taken.
'If more than one range still has same max the leftmost is taken.
Dim d As Double, dNew As Double, dMax As Double
Dim lMax As Long, i As Long, j As Long
Dim v As Variant

With Application.WorksheetFunction
For Each v In r
    r.Interior.ColorIndex = xlCIYellow
Next v
d = .Sum(r) / 2#
For i = 1 To r.Count
    dMax = 0#
    For j = 1 To r.Count - i + 1
        dNew = .Sum(r.Offset(0, j - 1).Resize(1, i))
        If dNew >= d Then
            If dNew > dMax Then
                dMax = dNew
                lMax = j
            End If
        End If
    Next j
    If dMax > 0# Then Exit For
Next i
For j = lMax To lMax + i - 1
    r(j).Interior.ColorIndex = xlCIPink
Next j
End With
End Sub

Sub doit()
Call sbHighlightMinAdjacentCells50P([C3:Q3])
End Sub
 
Upvote 0
Peter_SSs said:
Perhaps this sample data is not possible but I note some of the suggestions so far fail with it by my understanding of the requirement.
If that is so then perhaps there are other more realistic samples where they also fail?
Click to expand...
I've just been evaluating my formula to find why it was giving the wrong result, looks like I made more than one error :oops:
Given that there are several better alternatives, I think that it might be time to abort my attempt.

@CA_Punit that is setting a custom number format with parameters so that anything greater than the total in R3 will be zeroed out. Most excel blog / guide sites have detailed explanations of such things.
 
Upvote 0
Peter_SSs said:
Perhaps this sample data is not possible but I note some of the suggestions so far fail with it by my understanding of the requirement. ...
Click to expand...
All of the other suggestions so far fail by my understanding of the requirement. :)
I thought the OP was interested in the actual cells which make up 50% of the total amount but if he says thank you then he seems to be satisfied with just the amount.
 
Upvote 0
Bo_Ry said:
,"[>"&S3&"]0; ")
Click to expand...
Replacing ">" in above by ">=" gives the result as 400, which is the right answer. Kindly note, my original query says ">=", so in the above example, there is a tie for 400 for 7 consecutive cells, so the right answer is 400 as per my expectation. Many thanks @Bo_Ry for providing this solution :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Find highest scorers and return their name
2
Replies
13
Views
182
Peter_SSs
Peter_SSs
K
  • Question Question
Consolidate data from individual months by summing 2, 3 or more months together
Replies
0
Views
317
Kaleo
K
P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
901
paulkc
P
P
  • Solved
Keep W-L of card game played over time
Replies
6
Views
724
Preacherman771
P
A
  • Solved
Conditional macro
Replies
8
Views
452
Fluff
Fluff

Forum statistics

Threads
1,223,904
Messages
6,175,295
Members
452,632
Latest member
jladair

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top