Referencing part of a column

  • Thread starter Thread starter henry
  • Start date Start date
H

henry

I have a list of numbers in column B...in this case B4 to
B43314. In cell A4, I want the maximum value from B4 to
B243. In cell A5, I want the maximum value from B244 to
B483. In cell A6, I want the maximum value for the next
240 cells from column B...and so on. How can I reference
that formula in cell A4 so I can copy from cell A4 to (in
my case) cell A4333? Thanks.
 
One way:

A4: =MAX(OFFSET($B$4,240*(ROW()-4),0,240,1))


copy down as far as necessary (but A4333 is too far - with your list
extending to B43314, you should only need to drag down to A185)
 
Henry,

Put this in A4 and copy down

=MAX(INDIRECT("B"&((ROW(A4)-4)*240+4)&":B"&((ROW(A5)-4)*240+3)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Put this in A4 and fill down:

=MAX(OFFSET($B$4,(ROW()-4)*240,,240))

HTH
Jason
Atlanta, GA
 
henry said:
I have a list of numbers in column B...in this case B4 to
B43314. In cell A4, I want the maximum value from B4 to
B243. In cell A5, I want the maximum value from B244 to
B483. In cell A6, I want the maximum value for the next
240 cells from column B...and so on. How can I reference
that formula in cell A4 so I can copy from cell A4 to (in
my case) cell A4333? Thanks.
Click to expand...

If I have understood correctly, the formula for A4 is:
=MAX(OFFSET($B$4,(ROW()-ROW($A$4))*240,0,240,1))
Then copy down column A. However, you will only be able to go down to about
A184 (not A4333). You will then have 181 formulas each referencing 240 rows
of data, which is 43440, and you say your data only goes to B43314, so the
last of these 181 formulas will reference some data and some empty cells.
 
Back
Top