using excel function on group of numbers with steady jumps

Question

Hello, i have a column A with about 10,000 random numbers.

I want to perform a function ( =MAX() for instance) on group of 100 numbers every time,

i.e. i want to do

=MAX(A1:A101) then

=MAX(A102:A202)

=MAX(A203:A303) etc...

notice there's no overlapping in the groups. normal Dragging doesn't work, it will perform

jumps of 1 for each side of the values

how do i do it so i can drag and it will continue from the last value, and to another group of 100 ?

Thanks

Answer 1

Put this formula in B1 and fill down as far as you need

=MAX(OFFSET($A$1,(ROW()-1)*100,0,100,1))

Going from A1 to A101 is actually 101 values. The above formula only does 100 values. You may have to adjust if you really want 101. Just change the two instances of '100' to '101' or whatever group number you like.