finding block having the maximum sum for a range of cells

[ABHISKV4]

I want two formulas for a particular scenario:

Query 1. the block of 4 consecutive cells which has the maximum sum(blocks to be considered from the left most cell and then the blocks run from left to right)
Query 2. the starting cell number of the block having the maximum sum.

If there are two blocks having the same sum, then the first block can be used as the result. In the below image, the first block (Block1) contains 18,50,37 and 44 while the last block (Block15) contains 72,84,88,30

 

[ABHISKV4]

Also, thanks for updating your profile.

Thanks for reminding me to do the basics, I should have done it at the first place

 

[Sulprobil]

Abhiskv4,
Is this calculation to be mixed with the other one you asked for earlier today?
Anyway, you should really plan how your sales or stats table should develop and look like over a whole reporting period.
My thesis is that no formula monster will make you happy at the end of the year.

 

[ABHISKV4]

Is this calculation to be mixed with the other one you asked for earlier today?

@Sulprobil , there is no connection between this query and the other query.

 

[ABHISKV4]

Anyway, you should really plan how your sales or stats table should develop and look like over a whole reporting period.
My thesis is that no formula monster will make you happy at the end of the year.

I really wish I could do that, however in reality, the data can extend to multiple years as well, depends on case to case basis. There is nothing like a pre-defined limit on the no. of columns in the so called stats table. However, I am happy to state that the formula's suggested by all the esteemed members on this community are very well serving my purpose

 

[ABHISKV4]

So now I am tweaking the requirement to a more useful yet practical scenario.
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 the formula/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. I am treating this as an extension to the original problem, so not posting this as a new thread for now, hope it gets attention from the super users on this community, thanking in advance

Book28
	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)