Max value for every fifth cell in a column.

[stockgoblin42]

Hi,

How do I get the MAX for every fifth cell in a column, starting with M5.

so MAX of M5, M10, M15, M20, M25....... Probably to m2500 or so.

thanks

[Jacc]

Like this?

[benishiryo]

hi stockgoblin, different intepretation, not sure if correct. try this array:
=MAX(IF(MOD(ROW(M5:M2500),5)=0,M5:M2500))

...confirmed by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL-SHIFT-ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[stockgoblin42]

yes, thanks! Max of every 5th cell, not every 5 cells. Is there any way around an array formula as speed is an issue and my program loops alot?

[benishiryo]

you're welcome. maybe a helper column beside it.
=IF(MOD(ROW(M5),5),"",M5)

copy down all the way. then do a MAX of this column. for eg if the formulas are in N5:N2500, then:
=MAX(N5:N2500)