| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return cell reference rather than value
MIN and MAX return the value. I would like to return the cell reference
containing that value. For example MAX(B2:CH555) returns 7580. I would like it to return M350. Or better yet, the value in A350, the player! Application: Scores for our group. Names appear in ColumnA. Dates we played in Row1. Individual scores in B2:CH555. A couple of extra rows and columns for averages. I want to know WHO had the highest score for the year, who had the lowest, etc. The tedious way is to use MIN and MAX, then CNTRL-F to find the returned value. Thanks for solving the puzzle. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return cell reference rather than value
In CJ2 enter =MAX(B2:CH2)
Copy this down to row 555 To get the name use =INDEX(A2:A555,MATCH(MAX(CJ2:CI555),CI2:CJ555,0)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Suzy" wrote in message ... MIN and MAX return the value. I would like to return the cell reference containing that value. For example MAX(B2:CH555) returns 7580. I would like it to return M350. Or better yet, the value in A350, the player! Application: Scores for our group. Names appear in ColumnA. Dates we played in Row1. Individual scores in B2:CH555. A couple of extra rows and columns for averages. I want to know WHO had the highest score for the year, who had the lowest, etc. The tedious way is to use MIN and MAX, then CNTRL-F to find the returned value. Thanks for solving the puzzle. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return cell reference rather than value
Silly typos:
=INDEX(A2:A555,MATCH(MAX(CJ2:CJ555),CJ2:CJ555,0)) sorry! -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernard Liengme" wrote in message ... In CJ2 enter =MAX(B2:CH2) Copy this down to row 555 To get the name use =INDEX(A2:A555,MATCH(MAX(CJ2:CI555),CI2:CJ555,0)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Suzy" wrote in message ... MIN and MAX return the value. I would like to return the cell reference containing that value. For example MAX(B2:CH555) returns 7580. I would like it to return M350. Or better yet, the value in A350, the player! Application: Scores for our group. Names appear in ColumnA. Dates we played in Row1. Individual scores in B2:CH555. A couple of extra rows and columns for averages. I want to know WHO had the highest score for the year, who had the lowest, etc. The tedious way is to use MIN and MAX, then CNTRL-F to find the returned value. Thanks for solving the puzzle. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return cell reference rather than value
Individual scores in B2:CH555.
That's a lot of cells. In all of those cells, is there only 1 cell that contains a max and /or min score? Try these array formulas** : Max sco =INDEX(A:A,MAX((B2:CH555=MAX(B2:CH555))*ROW(B2:CH5 55))) Min sco =INDEX(A:A,MIN(IF(B2:CH555=MIN(B2:CH555),ROW(B2:CH 555)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Suzy" wrote in message ... MIN and MAX return the value. I would like to return the cell reference containing that value. For example MAX(B2:CH555) returns 7580. I would like it to return M350. Or better yet, the value in A350, the player! Application: Scores for our group. Names appear in ColumnA. Dates we played in Row1. Individual scores in B2:CH555. A couple of extra rows and columns for averages. I want to know WHO had the highest score for the year, who had the lowest, etc. The tedious way is to use MIN and MAX, then CNTRL-F to find the returned value. Thanks for solving the puzzle. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return cell reference rather than value
I'm a bit over my head with arrays, but this is the opportunity to learn!
I don't understand what the asterisk does in the MAX array. I don't understand the IF in the MIN formula, but not in the MAX. Thanks for your patience with me. "T. Valko" wrote: Individual scores in B2:CH555. That's a lot of cells. In all of those cells, is there only 1 cell that contains a max and /or min score? Try these array formulas** : Max sco =INDEX(A:A,MAX((B2:CH555=MAX(B2:CH555))*ROW(B2:CH5 55))) Min sco =INDEX(A:A,MIN(IF(B2:CH555=MIN(B2:CH555),ROW(B2:CH 555)))) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Suzy" wrote in message ... MIN and MAX return the value. I would like to return the cell reference containing that value. For example MAX(B2:CH555) returns 7580. I would like it to return M350. Or better yet, the value in A350, the player! Application: Scores for our group. Names appear in ColumnA. Dates we played in Row1. Individual scores in B2:CH555. A couple of extra rows and columns for averages. I want to know WHO had the highest score for the year, who had the lowest, etc. The tedious way is to use MIN and MAX, then CNTRL-F to find the returned value. Thanks for solving the puzzle. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Return cell reference rather than value
Excellent! Worked great. And I can adapt it for lowest score with MIN.
As to who came second, I can delete the max score, then rerun. Thanks. "Bernard Liengme" wrote: Silly typos: =INDEX(A2:A555,MATCH(MAX(CJ2:CJ555),CJ2:CJ555,0)) sorry! -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Bernard Liengme" wrote in message ... In CJ2 enter =MAX(B2:CH2) Copy this down to row 555 To get the name use =INDEX(A2:A555,MATCH(MAX(CJ2:CI555),CI2:CJ555,0)) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Suzy" wrote in message ... MIN and MAX return the value. I would like to return the cell reference containing that value. For example MAX(B2:CH555) returns 7580. I would like it to return M350. Or better yet, the value in A350, the player! Application: Scores for our group. Names appear in ColumnA. Dates we played in Row1. Individual scores in B2:CH555. A couple of extra rows and columns for averages. I want to know WHO had the highest score for the year, who had the lowest, etc. The tedious way is to use MIN and MAX, then CNTRL-F to find the returned value. Thanks for solving the puzzle. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| return the reference of a cell with certain text in it | Excel Discussion (Misc queries) | |||
| Return cell reference as text | Excel Discussion (Misc queries) | |||
| Return cell reference instead of value | Excel Discussion (Misc queries) | |||
| Return value in cell above the reference | Excel Discussion (Misc queries) | |||
| GETPIVOTDATA - return cell reference, not value | Excel Worksheet Functions | |||
Linear Mode
