Identifying rows that sum up to the largest total

[bbibo]

Row #	Value
1	385
2	-732
3	244
4	457
5	436

<tbody>
</tbody>

Is there a formula in excel that would identify the consecutive values that yield the highest sum ? In this simple example adding rows 3 to 5 will give you the highest total which is 1,137.

I have a list of nearly 1,000 rows and I'll need a formula to identify which consecutive rows should be added to arrive at the highest sum total.

Thanks !

 

[NeonRedSharpie]

Is it always going to be the three consecutive rows?

 

[bbibo]

No, it can be 3 rows or 1,000 rows - the number of rows is not a condition. The condition is that the rows are consecutive and their sum total generates the highest sum

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=MAX(SUBTOTAL(9,OFFSET(B1,ROW(B1:B5)-ROW(B1)+1,0,3)))

Is this what you are after?

Or is it?

=MAX(SUBTOTAL(9,OFFSET(B1,ROW(B1:B5)-ROW(B1),0,ROW(INDIRECT("1:"&ROWS(B1:B5))))))

 

[bbibo]

Thanks a lot for your post Aladin. I guess your first formula limits it to 3 consecutive rows which is not the case here.

I guess your second formula is more relevant in my case, however please refer to the attached file on this link: FileSwap.com : Excel problem.xlsx download free

I am arriving at a value of 9,738 although I can see that If I manually add cells B1:B131 I can get 10,265... Please take a look.

Thanks a lot !!

 

[XOR LX]

Hi,

I haven't downloaded your attachment, but if I understand you, then first go to Name Manager and define the following:

Name: Range1
Refers to: =$A$1:$A$5

(Or whatever the range in question happens to be.)

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&ROWS(Range1)))

Exit Name Manager.

Enter this array formula** in B1:

=MIN(IF(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)=MAX(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)),TRANSPOSE(Arry1)))

Then this array formula** in C1:

=IF(ROWS($1:1)>$B$1,"",INDEX(Range1,MIN(IF(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)=MAX(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)),Arry1))+ROWS($1:1)-1))

Copy this formula down (though not the one in B1) until you start to get blanks for the results.

Note that I have added a small qualifier to this formula to differentiate cases where there are actually more than one consecutive stringa in the range which share the maximum value. You didn't mention which should be preferred in this case: my solution will give the first it finds within the range.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).</SPAN></SPAN>

 

[bbibo]

Hi,

I haven't downloaded your attachment, but if I understand you, then first go to Name Manager and define the following:

Name: Range1
Refers to: =$A$1:$A$5

(Or whatever the range in question happens to be.)

Name: Arry1
Refers to: =ROW(INDIRECT("1:"&ROWS(Range1)))

Exit Name Manager.

Enter this array formula** in B1:

=MIN(IF(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)=MAX(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)),TRANSPOSE(Arry1)))

Then this array formula** in C1:

=IF(ROWS($1:1)>$B$1,"",INDEX(Range1,MIN(IF(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)=MAX(SUBTOTAL(9,OFFSET(INDEX(Range1,1,1),Arry1-1,,TRANSPOSE(Arry1),))+1/(Arry1*10^9)),Arry1))+ROWS($1:1)-1))

Copy this formula down (though not the one in B1) until you start to get blanks for the results.

Note that I have added a small qualifier to this formula to differentiate cases where there are actually more than one consecutive stringa in the range which share the maximum value. You didn't mention which should be preferred in this case: my solution will give the first it finds within the range.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Thank You very much !

 

[XOR LX]

Thank You very much !

I take it it worked then?!

Cheers