Using the MAX function with "constant increment" cell references

[John Dwyer]

I'd like to return the maximum value for every 12th cell
in a very long column of values. i.e. MAX
(BC2,BC14,BC26,BC38,BC50....) Can I do this using the MAX
function without listing each individual cell reference in
the function argument? If not, is there another way to do
this?

Thanks much,
John

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MAX(IF(MOD(ROW(B1:B1000),12)=2,B1:B1000,""))

 

[Ben McBen]

How about the below. BTW I have a tendency to do
things "by hand" there may be an easier way another
memeber can suggest


Public Function MaxRange(RangeToParse As Range, JumpSize
As Integer)

Dim aCell As Range
Dim MaxVal
Dim i

For Each aCell In RangeToParse

If i Mod JumpSize = 0 Then

If aCell > MaxVal Then
MaxVal = aCell
End If

End If

i = i + 1

Next aCell

MaxRange = MaxVal

End Function

 

[Max]

One way is to strip the values of all the 12th cells
into say, another sheet ..

Assuming the data is in Sheet1, col BC

In Sheet2
-------------
Put in A1: =INDIRECT("'Sheet1'!BC"&ROW(A1)*12-10)

Copy down

This'll extract contents of Sheet1's
BC2,BC14,BC26,BC38,BC50 ..
into col A

Then just put in B1: =MAX(A:A)
should do it