CODEWITHSUNDEEP
excelmax
fifa09
fifa09

Reputation: 25

how to find maximum value in column multiple times

Is there any way in excel to find max value from a column at regular intervals? I mean say in column "A", I need to find MAX(A1:A100), MAX(A101:200), MAX(A201:300), and so on. It will be in same intervals but have a huge amount of data. Is there any way to do this?

Upvotes: 2

Views: 925

Answers (2)

Scott Craner
Scott Craner

Reputation: 152450

This will cycle every 100 rows for every row it is copied or dragged down:

=MAX(INDEX(A:A,(ROW(1:1)-1)*100+1):INDEX(A:A,(ROW(1:1)*100)))

Unlike OFFSET, INDEX is not volatile. A volatile function will recalculate every time that Excel recalculates regardless if the underlying data changes or not. If there are a lot of formulas that use OFFSET or INDIRECT it will make a noticeable effect on the recalculation times.

enter image description here

Upvotes: 2

CallumDA
CallumDA

Reputation: 12113

This should work:

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

Note @ScottCraner's point. Liberal use of OFFSET can slow your file down.

Upvotes: 1

Related Questions