| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Show Cell Reference
Hi Groupies
I would like to be able to see which cell the Max function is referring to. For example: I have a list of values in B1 - B250. The Max function returns the highest value in that range, but I would also like to find out which cell contains that value. Is this possible? -- Thanks for the brainwaves! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Cell Reference
Maybe this works:
=address(match(max(b1:b250),b1:b250,0),2) CJ wrote: Hi Groupies I would like to be able to see which cell the Max function is referring to. For example: I have a list of values in B1 - B250. The Max function returns the highest value in that range, but I would also like to find out which cell contains that value. Is this possible? -- Thanks for the brainwaves! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Cell Reference
Hi, I it is close, but not quite.
I created a column of data starting at 10 incrementing by 5 from D2 - D20 (10 - 100). When I use your suggestion in cell D21, it give me the answer of $B$19 Here is my formula: =ADDRESS(MATCH(MAX(D2:D21),D2:D21,0),2) ??? -- Thanks for the brainwaves! "willwonka" wrote: Maybe this works: =address(match(max(b1:b250),b1:b250,0),2) CJ wrote: Hi Groupies I would like to be able to see which cell the Max function is referring to. For example: I have a list of values in B1 - B250. The Max function returns the highest value in that range, but I would also like to find out which cell contains that value. Is this possible? -- Thanks for the brainwaves! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Cell Reference
Formula correction:
=ADDRESS(MATCH(MAX(D2:D20),D2:D20,0),2) -- Thanks for the brainwaves! "CJ" wrote: Hi, I it is close, but not quite. I created a column of data starting at 10 incrementing by 5 from D2 - D20 (10 - 100). When I use your suggestion in cell D21, it give me the answer of $B$19 Here is my formula: =ADDRESS(MATCH(MAX(D2:D21),D2:D21,0),2) ??? -- Thanks for the brainwaves! "willwonka" wrote: Maybe this works: =address(match(max(b1:b250),b1:b250,0),2) CJ wrote: Hi Groupies I would like to be able to see which cell the Max function is referring to. For example: I have a list of values in B1 - B250. The Max function returns the highest value in that range, but I would also like to find out which cell contains that value. Is this possible? -- Thanks for the brainwaves! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Cell Reference
I should have said that the 2 stood for the second column which is
column B. If you are in column D, then a 4 should work. CJ wrote: Formula correction: =ADDRESS(MATCH(MAX(D2:D20),D2:D20,0),2) -- Thanks for the brainwaves! "CJ" wrote: Hi, I it is close, but not quite. I created a column of data starting at 10 incrementing by 5 from D2 - D20 (10 - 100). When I use your suggestion in cell D21, it give me the answer of $B$19 Here is my formula: =ADDRESS(MATCH(MAX(D2:D21),D2:D21,0),2) ??? -- Thanks for the brainwaves! "willwonka" wrote: Maybe this works: =address(match(max(b1:b250),b1:b250,0),2) CJ wrote: Hi Groupies I would like to be able to see which cell the Max function is referring to. For example: I have a list of values in B1 - B250. The Max function returns the highest value in that range, but I would also like to find out which cell contains that value. Is this possible? -- Thanks for the brainwaves! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Cell Reference
Also, the MATCH formula returns the row number within the range, so if
you don't start on row 1 then would need to add the amount of rows above your range. =ADDRESS(MATCH(MAX(D2:D20),D2:D20,0)+1,4) willwonka wrote: I should have said that the 2 stood for the second column which is column B. If you are in column D, then a 4 should work. CJ wrote: Formula correction: =ADDRESS(MATCH(MAX(D2:D20),D2:D20,0),2) -- Thanks for the brainwaves! "CJ" wrote: Hi, I it is close, but not quite. I created a column of data starting at 10 incrementing by 5 from D2 - D20 (10 - 100). When I use your suggestion in cell D21, it give me the answer of $B$19 Here is my formula: =ADDRESS(MATCH(MAX(D2:D21),D2:D21,0),2) ??? -- Thanks for the brainwaves! "willwonka" wrote: Maybe this works: =address(match(max(b1:b250),b1:b250,0),2) CJ wrote: Hi Groupies I would like to be able to see which cell the Max function is referring to. For example: I have a list of values in B1 - B250. The Max function returns the highest value in that range, but I would also like to find out which cell contains that value. Is this possible? -- Thanks for the brainwaves! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Show Cell Reference
OK, that works!!
Thanks a bunch! -- Thanks for the brainwaves! "willwonka" wrote: Also, the MATCH formula returns the row number within the range, so if you don't start on row 1 then would need to add the amount of rows above your range. =ADDRESS(MATCH(MAX(D2:D20),D2:D20,0)+1,4) willwonka wrote: I should have said that the 2 stood for the second column which is column B. If you are in column D, then a 4 should work. CJ wrote: Formula correction: =ADDRESS(MATCH(MAX(D2:D20),D2:D20,0),2) -- Thanks for the brainwaves! "CJ" wrote: Hi, I it is close, but not quite. I created a column of data starting at 10 incrementing by 5 from D2 - D20 (10 - 100). When I use your suggestion in cell D21, it give me the answer of $B$19 Here is my formula: =ADDRESS(MATCH(MAX(D2:D21),D2:D21,0),2) ??? -- Thanks for the brainwaves! "willwonka" wrote: Maybe this works: =address(match(max(b1:b250),b1:b250,0),2) CJ wrote: Hi Groupies I would like to be able to see which cell the Max function is referring to. For example: I have a list of values in B1 - B250. The Max function returns the highest value in that range, but I would also like to find out which cell contains that value. Is this possible? -- Thanks for the brainwaves! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do i assign cell A1 to show the current cursor cell in Excel? | Excel Discussion (Misc queries) | |||
| Show values in formula instead of cell reference | Excel Worksheet Functions | |||
| Input cell reference is not valid (One Variable Data Table) | Excel Worksheet Functions | |||
| how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
| Possible Lookup Table | Excel Worksheet Functions | |||
Linear Mode
