excel - Max function by using row number and column number of cell reference - Stack Overflow

admin管理员组

文章数量:1379419

The following formula is static.

=MAX(A5:C10)

I need dynamic formula because data range is variable.

I want to use row number and column number of cell reference.

I have tried the following formula but the following formula doesnt work.

=MAX(ADDRESS(5,1):ADDRESS(10,3))

Any solution?

The following formula is static.

=MAX(A5:C10)

I need dynamic formula because data range is variable.

I want to use row number and column number of cell reference.

I have tried the following formula but the following formula doesnt work.

=MAX(ADDRESS(5,1):ADDRESS(10,3))

Any solution?

• excel
• excel-formula

Share Improve this question Follow edited Mar 20 at 9:34 Markowitz asked Mar 20 at 7:55 MarkowitzMarkowitz 3566 bronze badges 4

• 2 What makes it dynamic? Are there certain values that indicate the start and end row/column? Is it always columns A:C or could that change? Is the first row static and the end row variable? The answer all depends on the data.... =MAX(A5:INDEX($C:$C,COUNTA($A:$A)+4)) maybe, or =MAX(Table1) might be better... all depends on the data. – Darren Bartrup-Cook Commented Mar 20 at 8:11
• 3 =MAX(INDIRECT(ADDRESS(5,1) & ":" & ADDRESS(10,3))) but I think you're making it more complicated than it needs to be. – Darren Bartrup-Cook Commented Mar 20 at 8:28
• TRIMRANGE() is introduced to refer full columns. – Harun24hr Commented Mar 20 at 8:41
• Instead/Surplus of (Solved) in the title you can answer your own question and after a time check as accepted. This helps to filter and ranking the question. – Black cat Commented Mar 20 at 9:23

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Formula like =ADDRESS(5,1) will return a cell address as text, not as an actual cell reference.

To turn it into an actual cell reference you need to wrap the formula with indirect - =INDIRECT(ADDRESS(5,1)).

As you no doubt know you reference a range of cells using : between the start and end cell reference, but =ADDRESS(5,1):ADDRESS(10,3) won't work as the ADDRESS function returns text, while the : part expects a cell reference.

So we can either use INDIRECT on both ADDRESS functions: =INDIRECT(ADDRESS(5,1)):INDIRECT(ADDRESS(10,3))

or create a text string representing the full range and use INDIRECT on that =INDIRECT(ADDRESS(5,1) & ":" & ADDRESS(10,3)) - here : is being treated as text so needs to be joined with the rest using & ":" &

Then just wrap the whole lot with MAX to find the maximum value: =MAX(INDIRECT(ADDRESS(5,1) & ":" & ADDRESS(10,3)))

The address row and column numbers can be swapped out for cell references if you're calculating those elsewhere - something like =MAX(INDIRECT(ADDRESS(F1,G1) & ":" & ADDRESS(F2,G2)))

本文标签： excelMax function by using row number and column number of cell referenceStack Overflow