ExcelBanter - View Single Post

wrote:
wrote:
I got the help I needed. To be more precise I have items
A(1),A(2),...A(200)[.] I want to set up column B in which
B(J) = MAX((A(1),A(2),...A(J)) for J=1,2,....200.
Since I have the basic answer, I think with some experimentation
I can figure out how to restrict the range to fewer entries (J,J-1,...J-k+1))
instead of going all the way back to cell 1.

I do not see any answer remotely resembling what I think you
want. As I understand you now, you want the maximum of a
range, where the at least the beginning and ending row numbers
are variable. I think the following best fits your needs ....

If column A contains the list of values, and B1 and B2 contain
the beginning and ending row numbers, then:

=MAX(OFFSET($A$1,B1-1,0):OFFSET($A$1,B2-1,0))

produces the maximum value in cells A(B1) through A(B2),
written loosely as you did above.

The same paradigm would also permit you so select a variable
column range as well. Also, OFFSET() can be self-referencial.
For example, if the MAX() formula is in C1, the following gives
the same result:

=MAX(OFFSET(C1,B1-1,-2):OFFSET(C1,B2-1,-2))

HTH.

I saved your post. Thanks - I'll try it out later - for now I'm just
using the max running all the way back to cell 1 using the method
pointed out by another poster.