How to determine maximum sum of multiple ranges?

[runinguy]

Hey all:

For a large column of data, I would like to determine the maximum sum of sequential 24-row ranges. To do this, I would essentially need to sum every 24 rows of data, and then determine the maximum value of the resulting sums. The problem is, there are almost 9000 rows of data to consider. Any ideas how this could be easily accomplished?

Thanks in advance.

 

[BobUmlas]

Pretty sure you have to do it as you said. Enter =SUM(A1:A24) in B1, say, then fill it down to row 9000 (fill handle dbl click?), then take =MAX(B:B) and you have your answer. 9000 rows of 50 rows should be pretty much the same amount of time to do.

 

[Marcelo Branco]

Maybe this array formula

=MAX(SUBTOTAL(9,OFFSET(A1,ROW(A1:A9000)-ROW(A1),0,24)))

confirmed with Ctrl+Shift+Enter

M.

 

[runinguy]

Pretty sure you have to do it as you said. Enter =SUM(A1:A24) in B1, say, then fill it down to row 9000 (fill handle dbl click?), then take =MAX(B:B) and you have your answer. 9000 rows of 50 rows should be pretty much the same amount of time to do.

Seems like a good approach, but unfortunately it misses one detail of the solution I'm looking for (my fault, I see now my problem statement was ambiguous): the 24-row ranges must not overlap. For example, the first range would be rows 1 through 24, the second rows 25 through 48, etc. Your solution worked perfectly for the ranges corresponding to 1 through 24, 2 through 25, etc. Thanks regardless!

 

[runinguy]

Maybe this array formula

=MAX(SUBTOTAL(9,OFFSET(A1,ROW(A1:A9000)-ROW(A1),0,24)))

confirmed with Ctrl+Shift+Enter

M.

This worked like a charm (I was able to verify using the solution suggested by BobUmlas). Unfortunately, I don't fully understand why it worked and I'm hoping you can help me figure this out. Can you explain the arguments used in the OFFSET function?

 

[RoryA]

That formula should work the same as Bob's (albeit in one cell).

 

[runinguy]

That formula should work the same as Bob's (albeit in one cell).

Yes, in this situation it does happen to work the same since the overall maximum 24-row range corresponds with one of the non-overlapping 24-row ranges beginning from the first row. But, it could be possible for the maximum 24-row range to be offset by some degree from the non-overlapping 24-row ranges.

 

[RoryA]

My point was that if one didn't do what you need, nor would the other. Perhaps:
=MAX(SUBTOTAL(9,OFFSET(A1,(ROW(A1:A9000)-ROW(A1))*24,0,24)))

 

[runinguy]

My point was that if one didn't do what you need, nor would the other. Perhaps:
=MAX(SUBTOTAL(9,OFFSET(A1,(ROW(A1:A9000)-ROW(A1))*24,0,24)))

All of these methods result in the same answer. What I still don't understand though is the outcome of using "ROW(A1:A9000)-ROW(A1)" (or in your case, "(ROW(A1:A9000)-ROW(A1))*24" for the "ROW" argument in the OFFSET function. If I enter this string as a normal "ROW" function, the results is 0.

 

[RoryA]

It returns an array of numbers (0,1,2,3,4 etc in the original use and 0,24,48 etc in my modified version) and these are passed to the OFFSET function to get discrete 24 row ranges from the original range.