How to use Google Spreadsheet ADDRESS function to create a range

Question

I'm trying yo create a formula in google spreadsheet that calculates the maximum value for all the cells above the current cell, in a way I can copy it to any cell and still workss. I tried using the ADDRESS function like this:

=MAX(ADDRESS(1;COLUMN()):ADDRESS(ROW()-1;COLUMN()))

But I get an parse error. I tried many variants of this code, but always get an error. Apparently, the ADDRESS function is not allowed as part of a range.

Is there any way to create a reference to a range based on the current cell position?

Answer 1

Was stuck on something very similar. Something like this should work.

=MAX(INDIRECT(1, COLUMN()) & ":" & ADDRESS(ROW()-1, COLUMN())))

Answer 2

This might be useful too if you want to get the sting address for a range:

=ADDRESS(row(A3:A6),COLUMN(A3:A6))&":"&ADDRESS(row(A3:A6)+rows(A3:A6)-1,COLUMN(A3:A6)+COLUMNS(A3:A6)-1)

This will return a string on the range, in this case

$A$3:$A$6

Answer 3

Using the INDIRECT function on your range string returns a range reference, which can then be used in a formula, eg.

INDIRECT(ADDRESS(1;COLUMN()):ADDRESS(ROW()-1;COLUMN()))

should work in

MAX(INDIRECT(ADDRESS(1;COLUMN()):ADDRESS(ROW()-1;COLUMN())))