| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 .. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 .. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return cell reference instead of value | Excel Discussion (Misc queries) | |||
| Return value in cell above the reference | Excel Discussion (Misc queries) | |||
| Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
| ADD A RETURN BUTTON WHEN GOING TO FORMULA REFERENCE LINKS | New Users to Excel | |||
| formula to return the value of a cell based on a looked up true reference | Excel Worksheet Functions | |||
Linear Mode
