Finding Max/Min/Avg of the sum of user defined no. of blocks

[ABHISKV4]

Assume that i have 156 cells in a row running from cell C3 to FB3. Now I want the "block size" to be user input (call it 1) and the "no. of highest sum blocks" to be found also as an user input (call it 2). Based on these two user inputs I want to arrive at the formula or macro to find the top no. of blocks and give the Max of sum of each block, Min of sum of each block and the Average of sum of each block. Here if the user defines the block size as 10, then the no. of blocks can be maximum 15 as 10*15 is 150 which is less than 156 cells available, the moment users provides no. of blocks as 16, it needs to have minimum 160 cells. Similarly for a block size of 8 the no. of blocks can be maximum 19, 8*19 is 152.
I hope I have not made the situation too complex. Both excel formula (with or without helper cells, preferably with minimum helper cells and VBA code are equally welcome as a solution to this problem.

EXCEL - Finding block having the maximum sum for a range of cells ADVANCED 14Sep2020.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO	CP	CQ	CR	CS	CT	CU	CV	CW	CX	CY	CZ	DA	DB	DC	DD	DE	DF	DG	DH	DI	DJ	DK	DL	DM	DN	DO	DP	DQ	DR	DS	DT	DU	DV	DW	DX	DY	DZ	EA	EB	EC	ED	EE	EF	EG	EH	EI	EJ	EK	EL	EM	EN	EO	EP	EQ	ER	ES	ET	EU	EV	EW	EX	EY	EZ	FA	FB
1
2			Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	Week16	Week17	Week18	Week19	Week20	Week21	Week22	Week23	Week24	Week25	Week26	Week27	Week28	Week29	Week30	Week31	Week32	Week33	Week34	Week35	Week36	Week37	Week38	Week39	Week40	Week41	Week42	Week43	Week44	Week45	Week46	Week47	Week48	Week49	Week50	Week51	Week52	Week53	Week54	Week55	Week56	Week57	Week58	Week59	Week60	Week61	Week62	Week63	Week64	Week65	Week66	Week67	Week68	Week69	Week70	Week71	Week72	Week73	Week74	Week75	Week76	Week77	Week78	Week79	Week80	Week81	Week82	Week83	Week84	Week85	Week86	Week87	Week88	Week89	Week90	Week91	Week92	Week93	Week94	Week95	Week96	Week97	Week98	Week99	Week100	Week101	Week102	Week103	Week104	Week105	Week106	Week107	Week108	Week109	Week110	Week111	Week112	Week113	Week114	Week115	Week116	Week117	Week118	Week119	Week120	Week121	Week122	Week123	Week124	Week125	Week126	Week127	Week128	Week129	Week130	Week131	Week132	Week133	Week134	Week135	Week136	Week137	Week138	Week139	Week140	Week141	Week142	Week143	Week144	Week145	Week146	Week147	Week148	Week149	Week150	Week151	Week152	Week153	Week154	Week155	Week156
3		Sales Qty -->	266	345	259	244	250	156	49	333	21	60	375	244	191	344	220	334	288	324	375	322	208	219	344	313	63	265	315	186	294	398	341	35	166	376	44	190	19	34	373	163	274	371	165	290	63	194	385	345	350	97	298	123	212	158	250	69	183	190	359	398	291	30	10	180	363	284	262	392	317	76	173	210	176	326	140	278	357	65	143	302	281	135	78	169	253	109	179	25	299	99	295	24	179	352	176	151	59	339	53	121	329	279	16	25	171	389	317	280	163	354	96	272	242	107	34	360	214	187	357	308	178	178	48	83	134	257	231	343	361	115	11	238	270	97	96	122	208	378	257	134	154	372	179	381	293	228	161	346	128	75	127	78	32	375	282	247
4		Yellow indicates User Input, always an integer
5		Green indicates, formula/answer needed in this cell
6
7			User Input
8		1. Block Size (No. of continuous cells in a block)	6
9		2. No. of Non Overlapping Blocks (Highest Sum block as Block#1 and likewise)	4
10		3. Out of above defined no. of blocks (Point 2 above), sum of block holding maximum sum		400
11		4. Out of above defined no. of blocks (Point 2 above), sum of block holding minimum sum		280
12		5. Out of above defined no. of blocks (Point 2 above), average of the sum of each block		335
13
14		Illustration of expected Answers:
15		Assume Block 1 Sum is	400
16		Assume Block 2 Sum is	360
17		Assume Block 3 Sum is	300
18		Assume Block 4 Sum is	280
Sheet1

Cell Formulas
Range	Formula
D10	D10	=MAX(C15:C18)
D11	D11	=MIN(C15:C18)
D12	D12	=AVERAGE(C15:C18)

 

[DanteAmor]

How about:

Sub Max_Min_Avg()
'Finding Max/Min/Avg of the sum of user defined no. of blocks
  Dim a As Variant
  Dim i As Long, j As Long, k As Long
  Dim nSize As Long, nBlock As Long, nTotal As Long
  Dim nSum As Long, nAcum As Long, nMax As Long, nMin As Long
  
  a = Range("C3", Cells(3, Columns.Count).End(1)).Value2
  
  nSize = Range("C8").Value     'Block Size (No. of continuous cells in a block)
  nBlock = Range("C9").Value    'No. of Non Overlapping Blocks
  nTotal = nSize * nBlock       'Total blocks
  
  nMax = 0
  nMin = 9 ^ 9
  
  If nTotal > 156 Then
    MsgBox "The number exceeds 156"
    Exit Sub
  End If
  
  For i = 1 To nTotal Step nSize
  
    nSum = 0
    For j = 1 To nSize
      k = k + 1
      nSum = nSum + a(1, k)
      nAcum = nAcum + a(1, k)
    Next
    
    If nSum < nMin Then nMin = nSum
    If nSum > nMax Then nMax = nSum
    
  Next
  
  Range("C10").Value = nMax
  Range("C11").Value = nMin
  Range("C12").Value = nAcum / nBlock

End Sub

 

[ABHISKV4]

How about:

Many thanks for your code. My apologies If i didn't make it clear enough in the first post. Blocks are all types of combinations of 6 continuous cells but then when we pick the top no. of blocks they cannot be overlapping with each other. So Block1 is C3:H3, Block2 is D3:I3, Block3 is E3:J3 and so on. With above logic, the block of 6 cells with highest sum would be 1885, minimum as 1674 and average as 1789 (i have found the blocks manually one by one). May be the excel snapshot, see below, be of help in understanding the problem better:

EXCEL - Finding block having the maximum sum for a range of cells ADVANCED 14Sep2020.xlsx
	C	D	E	F	G	H
1				Block#
2				Block1	1885	Max
3				Block2	1799
4				Block3	1798
5				Block4	1674	min
6					1789	Average
7
8	Week#	Sales Units	Running Total of 6 cells	Block#	Remarks
9	Week1	266	1520
10	Week2	345	1303
11	Week3	259	1291
12	Week4	244	1053
13	Week5	250	869
14	Week6	156	994
15	Week7	49	1082
16	Week8	333	1224
17	Week9	21	1235		Can't be used as overlaps with Block1
18	Week10	60	1434		Can't be used as overlaps with Block1
19	Week11	375	1708		Can't be used as overlaps with Block1
20	Week12	244	1621		Can't be used as overlaps with Block1
21	Week13	191	1701		Can't be used as overlaps with Block1
22	Week14	344	1885	Block1	Can't be used as overlaps with Block1
23	Week15	220	1863	Block1	Can't be used as overlaps with Block1
24	Week16	334	1851	Block1	Can't be used as overlaps with Block1
25	Week17	288	1736	Block1	Can't be used as overlaps with Block1
26	Week18	324	1792	Block1	Can't be used as overlaps with Block1
27	Week19	375	1781	Block1	Can't be used as overlaps with Block1
28	Week20	322	1469
29	Week21	208	1412		Can't be used as overlaps with Block2
30	Week22	219	1519		Can't be used as overlaps with Block2
31	Week23	344	1486		Can't be used as overlaps with Block2
32	Week24	313	1436		Can't be used as overlaps with Block2
33	Week25	63	1521		Can't be used as overlaps with Block2
34	Week26	265	1799	Block2	Can't be used as overlaps with Block2
35	Week27	315	1569	Block2	Can't be used as overlaps with Block2
36	Week28	186	1420	Block2	Can't be used as overlaps with Block2
37	Week29	294	1610	Block2	Can't be used as overlaps with Block2
38	Week30	398	1360	Block2	Can't be used as overlaps with Block2
39	Week31	341	1152	Block2	Can't be used as overlaps with Block2
40	Week32	35	830
41	Week33	166	829
42	Week34	376	1036
43	Week35	44	823
44	Week36	190	1053
45	Week37	19	1234
46	Week38	34	1380
47	Week39	373	1636
48	Week40	163	1326
49	Week41	274	1357
50	Week42	371	1468
51	Week43	165	1442
52	Week44	290	1627
53	Week45	63	1434
54	Week46	194	1669
55	Week47	385	1598
56	Week48	345	1425
57	Week49	350	1238
58	Week50	97	1138
59	Week51	298	1110
60	Week52	123	995
61	Week53	212	1062
62	Week54	158	1209
63	Week55	250	1449
64	Week56	69	1490
65	Week57	183	1451
66	Week58	190	1278
67	Week59	359	1268		Can't be used as overlaps with Block3
68	Week60	398	1272		Can't be used as overlaps with Block3
69	Week61	291	1158		Can't be used as overlaps with Block3
70	Week62	30	1129		Can't be used as overlaps with Block3
71	Week63	10	1491		Can't be used as overlaps with Block3
72	Week64	180	1798	Block3	Can't be used as overlaps with Block3
73	Week65	363	1694	Block3	Can't be used as overlaps with Block3
74	Week66	284	1504	Block3	Can't be used as overlaps with Block3
75	Week67	262	1430	Block3	Can't be used as overlaps with Block3
76	Week68	392	1344	Block3	Can't be used as overlaps with Block3
77	Week69	317	1278	Block3	Can't be used as overlaps with Block3
78	Week70	76	1101
79	Week71	173	1303
80	Week72	210	1487
81	Week73	176	1342
82	Week74	326	1309
83	Week75	140	1285
84	Week76	278	1426
85	Week77	357	1283
86	Week78	65	1004
87	Week79	143	1108
88	Week80	302	1218
89	Week81	281	1025
90	Week82	135	923
91	Week83	78	813
92	Week84	169	1034
93	Week85	253	964
94	Week86	109	1006
95	Week87	179	921
96	Week88	25	921
97	Week89	299	1248
98	Week90	99	1125
99	Week91	295	1177
100	Week92	24	941
101	Week93	179	1256
102	Week94	352	1130
103	Week95	176	899
104	Week96	151	1052
105	Week97	59	1180
106	Week98	339	1137
107	Week99	53	823
108	Week100	121	941		Can't be used as overlaps with Block4
109	Week101	329	1209		Can't be used as overlaps with Block4
110	Week102	279	1197		Can't be used as overlaps with Block4
111	Week103	16	1198		Can't be used as overlaps with Block4
112	Week104	25	1345		Can't be used as overlaps with Block4
113	Week105	171	1674	Block4	Can't be used as overlaps with Block4
114	Week106	389	1599	Block4	Can't be used as overlaps with Block4
115	Week107	317	1482	Block4	Can't be used as overlaps with Block4
116	Week108	280	1407	Block4	Can't be used as overlaps with Block4
117	Week109	163	1234	Block4	Can't be used as overlaps with Block4
118	Week110	354	1105	Block4	Can't be used as overlaps with Block4
119	Week111	96	1111
120	Week112	272	1229
121	Week113	242	1144
122	Week114	107	1259
123	Week115	34	1460
124	Week116	360	1604
125	Week117	214	1422
126	Week118	187	1256
127	Week119	357	1152
128	Week120	308	929
129	Week121	178	878
130	Week122	178	931
131	Week123	48	1096
132	Week124	83	1409
133	Week125	134	1441
134	Week126	257	1318
135	Week127	231	1299
136	Week128	343	1338
137	Week129	361	1092
138	Week130	115	827
139	Week131	11	834
140	Week132	238	1031
141	Week133	270	1171
142	Week134	97	1158
143	Week135	96	1195
144	Week136	122	1253
145	Week137	208	1503
146	Week138	378	1474
147	Week139	257	1477
148	Week140	134	1513
149	Week141	154	1607
150	Week142	372	1614
151	Week143	179	1588
152	Week144	381	1537
153	Week145	293	1231
154	Week146	228	1065
155	Week147	161	915
156	Week148	346	786
157	Week149	128	815
158	Week150	75	969
159	Week151	127	1141
160	Week152	78
161	Week153	32
162	Week154	375
163	Week155	282
164	Week156	247
Sheet2

Cell Formulas
Range	Formula
G2:G5	G2	=SUMIF($F$9:$F$164,F2,$D$9:$D$164)
G6	G6	=AVERAGE(G2:G5)
E9:E159	E9	=SUM(D9:D14)

 

[DanteAmor]

So Block1 is C3:H3, Block2 is D3:I3, Block3 is E3:J3 and so on. With above logic, the block of 6 cells with highest sum would be 1885, minimum as 1674 and average as 1789

Now I understand less. In your first example the structure is horizontal now it is vertical, that doesn't help. The macro is not dynamic to adapt to changes in the structure of your data.

So why in your example the first block is C3: H3, but in your data range the first block is not the first cell, the first block according to the yellow color is up to cell D22, this is very confusing for me.

It looks like you get all 4 blocks at random.

 

[ABHISKV4]

Now I understand less. In your first example the structure is horizontal now it is vertical, that doesn't help. The macro is not dynamic to adapt to changes in the structure of your data.

My apologies if it is confusing. Please note that I transposed the data from horizontal to vertical just for my ease of calculations and finding the desired blocks. The data remains horizontally only as posted in the original post. First block still remains C3:H3, 2nd block D3:I3, and so on.
So in 2nd excel snapshot (the transposed version), the equivalent first block becomes D9:D14, 2nd block D10:D15, and so on. The range highlighted in yellow (D22:D27) is the block of 6 cells which is having the highest sum, I did the calculations manually and figured out the maximum sum block just for conveying the expected solution to the original problem. Once Block1 is decided, any sum range of continuous 6 cells cannot have even a single cell which is already used by Block1, and that's how we find the 2nd highest block, and so on. Please see the excel snapshot again and may be you will understand what I am looking for. Happy to further elaborate if still not clear, thanks.

 

[Eric W]

Start with this (with the sales data going off to the right as far as needed):

Book1
	A	B	C	D	E
1
2			Week1	Week2	Week3
3		Sales Qty -->	266	345	259
4
5
6
7			User Input
8		1. Block Size (No. of continuous cells in a block)	6
9		2. No. of Non Overlapping Blocks (Highest Sum block as Block#1 and likewise)	4
Sheet1

Try this macro:

Sub BlockMax()
Dim SalesData As Variant, BlkSize As Long, NumBlks As Long
Dim Weeks(), MaxWeek As Long, MaxSum As Double, BlockNo As Long
Dim i As Long, j As Long, sd As Range, tlc As Range

    Set sd = Range("C3")            ' Make this the leftmost cell of the sales data
    BlkSize = Range("C8").Value     ' This is where you put the block size
    NumBlks = Range("C9").Value     ' This is where you put the number of blocks you want
    Set tlc = Range("C10")          ' This is the top left corner of where you want the results
    
    SalesData = Range(sd, Cells(sd.Row, Cells(sd.Row, Columns.Count).End(xlToLeft).Column)).Value
    ReDim Weeks(1 To NumBlks, 1 To 2)
    BlockNo = 0
    
Loop1:
    MaxWeek = -1
    MaxSum = -1
    For i = 1 To UBound(SalesData, 2) - BlkSize + 1
        For j = 1 To BlockNo
            If i > Weeks(j, 1) - BlkSize + 1 And i < Weeks(j, 1) + BlkSize - 1 Then GoTo IterateI:
        Next j
        wksum = 0
        For j = i To i + BlkSize - 1
            wksum = wksum + SalesData(1, j)
        Next j
        If wksum > MaxSum Then
            MaxSum = wksum
            MaxWeek = i
        End If
IterateI:
    Next i
    
    If MaxWeek = -1 Then
        MsgBox "Unable to come up with enough non-overlapping blocks."
        Exit Sub
    End If
    
    BlockNo = BlockNo + 1
    Weeks(BlockNo, 1) = MaxWeek
    Weeks(BlockNo, 2) = MaxSum
    
    If BlockNo < NumBlks Then GoTo Loop1:
    
    tlc.Resize(, 2).Value = Array("Week starting", "Sum")
    tlc.Offset(1).Resize(NumBlks, 2).Value = Weeks
    
    tlc.Offset(NumBlks + 1) = "Average"
    tlc.Offset(NumBlks + 1, 1).FormulaR1C1 = "=AVERAGE(R[-" & NumBlks & "]C:R[-1]C)"
    
End Sub

Note the first 4 lines at the top of the macro where you can set the locations of the input and output cells.

This is the result:

Book1
	A	B	C	D	E
1
2			Week1	Week2	Week3
3		Sales Qty -->	266	345	259
4
5
6
7			User Input
8		1. Block Size (No. of continuous cells in a block)	6
9		2. No. of Non Overlapping Blocks (Highest Sum block as Block#1 and likewise)	4
10			Week starting	Sum
11			14	1885
12			26	1799
13			64	1798
14			19	1781
15			Average	1815.75
Sheet1

Cell Formulas
Range	Formula
D15	D15	=AVERAGE(D11:D14)

 

[ABHISKV4]

This is the result:

This is exactly what I was looking for, thank you so much for this awesome code. Please note that the macro output gives Week starting as 14,16,64 and 19. Cell U3 (highlighted in Red), which is the last week of the first block (which starts from week 14) overlaps with the first week of last block (which starts from Week19). If you could fix this please, then it's perfect.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2			Week1	Week2	Week3	Week4	Week5	Week6	Week7	Week8	Week9	Week10	Week11	Week12	Week13	Week14	Week15	Week16	Week17	Week18	Week19	Week20	Week21	Week22	Week23	Week24	Week25	Week26	Week27
3			266	345	259	244	250	156	49	333	21	60	375	244	191	344	220	334	288	324	375	322	208	219	344	313	63	265	315
4																Block1	Block1	Block1	Block1	Block1	Block1
5																					Block4	Block4	Block4	Block4	Block4	Block4
Sheet1

 

[Eric W]

Dagnabbit! ? Pesky little signs, OK, change this line:

If i > Weeks(j, 1) - BlkSize + 1 And i < Weeks(j, 1) + BlkSize - 1 Then GoTo IterateI:

to

If i >= Weeks(j, 1) - BlkSize + 1 And i <= Weeks(j, 1) + BlkSize - 1 Then GoTo IterateI:

 

[ABHISKV4]

If i >= Weeks(j, 1) - BlkSize + 1 And i <= Weeks(j, 1) + BlkSize - 1 Then GoTo IterateI:

Wow, it works just the way I wanted it to. Many many thanks for this great help. I never thought this would be really possible in excel, but you have proved me wrong, and slowly I realize how powerful the VBA is. Cheers!