Small formula with variable range

[cidfidou]

Hi Excel Masters,

Any idea on how to trick the small formula in order to have the range to look at blocks of 20 lines... I have a list of 20 scores for the same items in rows, and I would like to use =SMALL($M$10:$M$30,1) and to have the range M10 to M30 dynamicaly changes to 31 to 51 and 52 to 72 and so on.. every 20

As usual, your awesome help is much appreciated

Thanks

 

[Aladin Akyurek]

In O1 enter and copy down:

=SMALL(INDEX($M$10:$M$100,(ROWS(O$1:O1)-1)*20+1):INDEX($M$30:$M$100,(ROWS(O$1:O1))*20),1)

Adjust the range to suit.

 

[cidfidou]

Amazing formula... Thanks Aladin

 

[Aladin Akyurek]

Amazing formula... Thanks Aladin

You are welcome.