Row # | Value |
1 | 385 |
2 | -732 |
3 | 244 |
4 | 457 |
5 | 436 |
<tbody>
</tbody>
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 !
Row # | Value |
1 | 385 |
2 | -732 |
3 | 244 |
4 | 457 |
5 | 436 |
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).