find the max values for cells in consecutive groups of 600
 

I have a list of numbers, 65000 in all; they are arranged in groups of 600 in
a single column, say B2 to B65001. That is, 650 consecutive groups of 600
cells each. I want to find the max value in each group and tabulate the
values alongside the list...but not with each one spaceed 600 cells apart!
The fomula is =MAX(B2:B601) for the first group, of course. And I can put the
answer to this in say cell J2. But the next one for cell J3 will be
=MAX(B602:1201) and so on. I'll have to do this 100 times, and I'd rather
copy and paste. But Excel can't spot the pattern and copy, generate and paste
the right cell numbers. Any advice?

Alternative: How can I change from B2 system to R[]C[] system? That might
help.

Hi John,

copy this code into the Visual basic Editor (Alt+F11), and run it.


Code:
--------------------
Public Sub Max600()

Dim iCnt As Long

'Set cell to hold first result
ActiveSheet.Range("J2").Activate

'Simple loop
For iCnt = 1 To 650
ActiveCell.Offset(iCnt - 1, 0).Formula = _
"=MAX(B" & (600 * (iCnt - 1)) + 2 & ":B" & (600 * (iCnt - 1)) + 601 & ")"
Next iCnt

End Sub

--------------------


Not sure you mean 650 groups of 600, but you can adjst the loop to
suit.

HTH

Art


--
HiArt
------------------------------------------------------------------------
HiArt's Profile: http://www.excelforum.com/member.php...o&userid=19953
View this thread: http://www.excelforum.com/showthread...hreadid=472893

One way ..

Put in the starting cell J2:
=MAX(OFFSET(INDIRECT("B"&ROWS($A$1:A1)*600-600+1),600,))

Copy down to J109

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"john" wrote in message
...
I have a list of numbers, 65000 in all; they are arranged in groups of 600
in
a single column, say B2 to B65001. That is, 650 consecutive groups of 600
cells each. I want to find the max value in each group and tabulate the
values alongside the list...but not with each one spaceed 600 cells apart!
The fomula is =MAX(B2:B601) for the first group, of course. And I can put
the
answer to this in say cell J2. But the next one for cell J3 will be
=MAX(B602:1201) and so on. I'll have to do this 100 times, and I'd rather
copy and paste. But Excel can't spot the pattern and copy, generate and
paste
the right cell numbers. Any advice?

Alternative: How can I change from B2 system to R[]C[] system? That might
help.