getting a formula to return the reference of a cell
 

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

=ADDRESS(MATCH(MAX(F$1:F$23719),F$1:F$23719,0),1,4 )

"CB" wrote:

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

One way:

="F"&MATCH(MAX(F1:F23719),F1:F23719,0)

Biff

"CB" wrote in message
...
I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

Great but When i cointue down and do the top ten,I get two values that are
the same (my top 10 songs , and how many records they sell , two of them
sold 53 this week ) . so I cannot use the match fuction which returns only
the match for the first occurence.
I was hoping to use the adress fuctiondirectly within the large function to
avoid the problem

"Teethless mama" wrote:

=ADDRESS(MATCH(MAX(F$1:F$23719),F$1:F$23719,0),1,4 )

"CB" wrote:

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1),F$1:F$ 23719,0),1,4)

Copy down to as far as needed


"CB" wrote:

Great but When i cointue down and do the top ten,I get two values that are
the same (my top 10 songs , and how many records they sell , two of them
sold 53 this week ) . so I cannot use the match fuction which returns only
the match for the first occurence.
I was hoping to use the adress fuctiondirectly within the large function to
avoid the problem

"Teethless mama" wrote:

=ADDRESS(MATCH(MAX(F$1:F$23719),F$1:F$23719,0),1,4 )

"CB" wrote:

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

I get a " errro in formula"

"Teethless mama" wrote:

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1),F$1:F$ 23719,0),1,4)

Copy down to as far as needed


"CB" wrote:

Great but When i cointue down and do the top ten,I get two values that are
the same (my top 10 songs , and how many records they sell , two of them
sold 53 this week ) . so I cannot use the match fuction which returns only
the match for the first occurence.
I was hoping to use the adress fuctiondirectly within the large function to
avoid the problem

"Teethless mama" wrote:

=ADDRESS(MATCH(MAX(F$1:F$23719),F$1:F$23719,0),1,4 )

"CB" wrote:

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

Here is the correct formula, the previous one was missing a bracket ")"

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1)),F$1:F $23719,0),1,4)


"CB" wrote:

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

Still does nto work . I will try a differnt way ..

"Teethless mama" wrote:

Here is the correct formula, the previous one was missing a bracket ")"

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1)),F$1:F $23719,0),1,4)


"CB" wrote:

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..

getting a formula to return the reference of a cell
 

Try this:

Entered as an array using the key combination of CTRL,SHIFT,ENETER (not just
ENTER):

="F"&MATCH(LARGE(F1:F23719-ROW(F1:F23719)/10^10,ROWS($1:1)),F1:F23719-ROW(F1:F23719)/10^10,0)

Biff

"CB" wrote in message
...
Still does nto work . I will try a differnt way ..

"Teethless mama" wrote:

Here is the correct formula, the previous one was missing a bracket ")"

=ADDRESS(MATCH(LARGE(F$1:F$23719,ROWS($1:1)),F$1:F $23719,0),1,4)


"CB" wrote:

I find in a one column range the top value with the function
LARGE(F$1:F$23719,1)
How can i find the reference of the cell wher this value is stored?

thasnk you in advance ..