MAX of a windowed-SUM

[rgor]

Colleague asked for a formula to calculate the "maximum of a windowed sum", meaning that:
- he has a column with numbers, say in B10:B39
- he can create a column with a windowed-sum of these values in C10:C39, with window-width of 5 (defined as a parameter in cell B2); so C10 would be the sum(B10:B14), C11 = sum(B11:B15) etc.
- he can easily calculate the max of C10:C39
- but... well, it's an existing shared file with many columns, can't mess much with it... so it would be ideal to be able to do all the above in a single cell

Looking at some of the previous questions, formula forensics etc. in Chandoo I was able to come up with:
=MAX(MMULT((ROW(B$10:B$39)+$B$2>TRANSPOSE(ROW(B$10:B$39)))*(ROW(B$10:B$39)<=TRANSPOSE(ROW(B$10:B$39))),B$10:B$39))

Which is working fine so far... but slow, as this is applied to each column of a big table with x1000 rows and several columns. Wondering if some of the Excel experts here would come up with a more efficient solution?
VBA not an option this time - have some difficulties to run it in a shared environment.
Thanks!

 

[NARAYANK991]

Hi ,

Can you try this , entered as an array formula , using CTRL SHIFT ENTER ?

=MAX(SUBTOTAL(9,OFFSET(B10,ROW(INDIRECT("1:"&ROWS($B$10:$B$39)-$B$2+1))-1,,$B$2)))

Narayan

 

[rgor]

Many thanks,
My colleague showed me almost the same formula a few minutes after I sent the first post, it works wonderfully and certainly copes with much more data: in our first tests, my original formula failed for >10k rows (out of memory..), whereas the later can easily do >100k - all we need...