ExcelBanter - cell reference

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - cell reference (https://www.excelbanter.com/new-users-excel/104298-cell-reference.html)

I have a list of numbers in column A. I wish at the bottom to return the
maximum value in column A using =max(a1:a100) but i also want the cell
reference of this value. how can i do this.

thanks

Use the MATCH function - it will give you the relative position of the
maximum value in your range, and as this begins at A1 then this is the
same as the row. If you have two maximum values, however, it will only
report the first occurrence.

Hope this helps.

Pete

lees news wrote:
I have a list of numbers in column A. I wish at the bottom to return the
maximum value in column A using =max(a1:a100) but i also want the cell
reference of this value. how can i do this.

thanks

Pete_UK wrote:
Use the MATCH function - it will give you the relative position of the
maximum value in your range, and as this begins at A1 then this is the
same as the row. If you have two maximum values, however, it will only
report the first occurrence.

Hope this helps.

Pete

lees news wrote:
I have a list of numbers in column A. I wish at the bottom to return the
maximum value in column A using =max(a1:a100) but i also want the cell
reference of this value. how can i do this.

thanks

Try this:
="A"&MATCH(MAX(A1:A100),A1:A100)
This will place the cell reference to the largest number in your list
in the cell that contains this formula.