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.

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.

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.

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.

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.

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.