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

 

[onlyadrafter]

Hello,

have assumed your cell 18 is in Cell C3.
In cell C2 enter this formula =SUM(C3:F3) and copy over as far as required.

then use this formula to get the four cells with the maximum =ADDRESS(3,MATCH(MAX(C2:T2),2:2,0))&":"&ADDRESS(3,MATCH(MAX(C2:T2),2:2,0)+4) should give G3:K3

and use this formula to get the starting cell =ADDRESS(3,MATCH(MAX(C2:T2),2:2,0)) should give G3.

If you require the block name then use =OFFSET(B2,MATCH("b",OFFSET(B:B,0,MATCH(MAX(C2:T2),C2:T2,0)),-1)-2,MATCH(MAX(C2:T2),C2:T2,0)) should give Block5

Are any of these what you require?

 

[ABHISKV4]

Hi there,

Many thanks for your time and help, much appreciated. Numbers highlighted in yellow start from cell C3 and end in cell T30, so your assumption is absolutely right. The formula works beautifully and does give the expected results.

Just wondering if there is a way to get the same result, value of highest sum block, using a single cell formula (array formula), as I have seen the solutions to some similar questions through a single cell array formula. I am not an expert on array formulas so not sure if it is possible at all to get the result through a single cell array formula. Quoting below a similar question and it's solution using single cell array formula
Question: Get the average of the last 5 non-zero cells in a row (not column).
Array formula: {=SUM(P18:INDEX(B18:P18,LARGE((COLUMN(B18:P18)-MIN(COLUMN(B18:P18))+1)*(B18:P18<>0),5)))/5}

 

[Amit Tandon]

Given a bit of time, surely a shorter formula can be derived. For now, maybe below:

26Aug19.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	18	50	37	44	80	74	41	94	31	53	39	2	79	9	72	84	88	30		G2:J2
3																				Block5
Sheet50

Cell Formulas
Range	Formula
V2	V2	=ADDRESS(ROW(C2:Q2),MATCH(LARGE(SUBTOTAL(9,OFFSET(C2:Q2,,COLUMN(C2:Q2)-COLUMN(C2),,4)),1),SUBTOTAL(9,OFFSET(C2:Q2,,COLUMN(C2:Q2)-COLUMN(C2),,4)),0)+COLUMN(C2)-1,4)&":"&ADDRESS(ROW(C2:Q2),MATCH(LARGE(SUBTOTAL(9,OFFSET(C2:Q2,,COLUMN(C2:Q2)-COLUMN(C2),,4)),1),SUBTOTAL(9,OFFSET(C2:Q2,,COLUMN(C2:Q2)-COLUMN(C2),,4)),0)+COLUMN(C2)+2,4)
V3	V3	="Block"&MATCH(LARGE(SUBTOTAL(9,OFFSET(C2:Q2,,COLUMN(C2:Q2)-COLUMN(C2),,4)),1),SUBTOTAL(9,OFFSET(C2:Q2,,COLUMN(C2:Q2)-COLUMN(C2),,4)),0)

 

[ABHISKV4]

Hi Amit,

Many thanks for the magical formula, for me it's nothing less than a miracle.. Now instead of block of 4 consecutive cells, if I want to make the blocks of 5 cells or 6 cells then which parts of the formulas need to be edited, formula is a bit difficult for me to interpret and change on my own, so please guide on this.

 

[ABHISKV4]

I gave it a look and have been able to understand the formula, so now I know which parts of the formula need to be edited in order to make the formula work for block of 5 cells or 6 cells. Once again I thank to the courteous and kind users on this community.

 

[Peter_SSs]

@ABHISKV4
It would help if you investigated XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, IF you have Excel 365 with the SEQUENCE function then this may be what you want?

20 09 03.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2																				Max	Block no.
3	18	50	37	44	80	74	41	94	31	53	39	2	79	9	72	84	88	30		289	5
Max Block

Cell Formulas
Range	Formula
V3	V3	=MAX(SUBTOTAL(9,OFFSET(C3:Q3,,SEQUENCE(COLUMNS(C3:Q3),,0),,4)))
W3	W3	=MATCH(V3,SUBTOTAL(9,OFFSET(C3:Q3,,SEQUENCE(COLUMNS(C3:Q3),,0),,4)),0)

If not Excel 365 then

20 09 03.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1
2																				Max	Block no.
3	18	50	37	44	80	74	41	94	31	53	39	2	79	9	72	84	88	30		289	5
Max BlockSheet2 (2)

Cell Formulas
Range	Formula
V3	V3	=MAX(SUBTOTAL(9,OFFSET(C3:Q3,,COLUMN(C3:Q3)-COLUMN(C3),,4)))
W3	W3	=MATCH(V7,SUBTOTAL(9,OFFSET(C3:Q3,,COLUMN(C3:Q3)-COLUMN(C3),,4)),0)

 

[ABHISKV4]

It would help if you investigated XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks for all your suggestions, I have explored XL2BB and added it as an add-in to my excel, so that i can use the same for all future posts. I have also gone through the FAQ, Rules and Guidelines.

 

[ABHISKV4]

V7

I believe this is a typo error, V7 has to be V3. Only again many thank for all your formulas, they are just awesome

 

[Peter_SSs]

I believe this is a typo error, V7 has to be V3.

Yes, sorry. I had those two different formulas for returning the maximum value of a block & forgot to change the reference when preparing my second screen shot. Thanks for pointing it out. .


Also, thanks for updating your profile.