Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I find the max in each group? | Excel Worksheet Functions | |||
how do i find values on a line graph in Excel? | Excel Discussion (Misc queries) | |||
Need help w/ Weight Formula | Excel Discussion (Misc queries) | |||
Count Distinct Values by Group Using Pivot Table (NM) | Excel Worksheet Functions | |||
Subtotal of Subtotal displays Grand Total in wrong row | Excel Worksheet Functions |