I've got an excel spreadsheet full of data, and I wanted to create a column
that would tell me what the second highest value was, given a list of cells.


For example, I've got 8 columns (columns K through R), with headers "Team 1"
through "Team 8".

Below that, on the following MANY rows, are numbers, associated with scores
for the team in that column.


I'd like to have a column that tells me what score was 1st place, which was
2nd place, and which was 3rd place.

Finding the 1st place one is easy, using the =MAX(K4:R4).

But I'm at a loss to determine the 2nd place, and then the 3rd place one.



Then, once I determine that, can I use that value to do a lookup (for a new
column) and return the header?


For example, let's say we have the following row:

Team1 Team2 Team3 Team4 Team5 Team6
Team7 Team8
100 200 300 400 250
350 150 450


I'd like to have a "1st place score" column that would report 450 (for this
row)
I'd like a "1st place team" column that would report "Team8"
I'd like a "2nd place score" column that would report 400
I'd like a "2nd place team" column that would report "Team4"
I'd like a "3rd place score" column that would report 350
I'd like a "3rd place team" column that would report "Team6"


Then I'd be able to reproduce those formulae for every row through the
entire list of data.


Is there an easy way to do this?


In fact, now that I think about it, if there's a way to get the team names
without the scores, that would be acceptable too, but I figured I'd probably
need the scores first.



Thanks!
-Scott

Look up the "Rank" function in the help files. That can get you what you
want.

HTH

Alan P.

"Scott M. Lyon" wrote in message
...
I've got an excel spreadsheet full of data, and I wanted to create a
column that would tell me what the second highest value was, given a list
of cells.


For example, I've got 8 columns (columns K through R), with headers "Team
1" through "Team 8".

Below that, on the following MANY rows, are numbers, associated with
scores for the team in that column.


I'd like to have a column that tells me what score was 1st place, which
was 2nd place, and which was 3rd place.

Finding the 1st place one is easy, using the =MAX(K4:R4).

But I'm at a loss to determine the 2nd place, and then the 3rd place one.



Then, once I determine that, can I use that value to do a lookup (for a
new column) and return the header?


For example, let's say we have the following row:

Team1 Team2 Team3 Team4 Team5 Team6
Team7 Team8
100 200 300 400 250 350
150 450


I'd like to have a "1st place score" column that would report 450 (for
this row)
I'd like a "1st place team" column that would report "Team8"
I'd like a "2nd place score" column that would report 400
I'd like a "2nd place team" column that would report "Team4"
I'd like a "3rd place score" column that would report 350
I'd like a "3rd place team" column that would report "Team6"


Then I'd be able to reproduce those formulae for every row through the
entire list of data.


Is there an easy way to do this?


In fact, now that I think about it, if there's a way to get the team names
without the scores, that would be acceptable too, but I figured I'd
probably need the scores first.



Thanks!
-Scott

Here's a formula system that will take into consideration ties for 3rd
place...

Assumptions:

K1:R1 contains the team name

K2:R2 contains the score

Formulas:

S2, copied across to Column Z:

=RANK(K2,$K$2:$R$2)+COUNTIF($K$2:K2,K2)-1

AA1: enter 3, indicating that you want a Top 3 list

AB1:

=MAX(IF(K2:R2=INDEX(K2:R2,MATCH(AA1,S2:Z2,0)),S2:Z 2))-AA1

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER

AC1, copied across to Column AJ and down to the next row:

=IF(COLUMN()-COLUMN($AC1)+1<=$AA$1+$AB$1,INDEX($K1:$R1,MATCH(CO LUMN()-COL
UMN($AC1)+1,$S$2:$Z$2,0)),"")

Hope this helps!

In article ,
"Scott M. Lyon" wrote:

I've got an excel spreadsheet full of data, and I wanted to create a column
that would tell me what the second highest value was, given a list of cells.


For example, I've got 8 columns (columns K through R), with headers "Team 1"
through "Team 8".

Below that, on the following MANY rows, are numbers, associated with scores
for the team in that column.


I'd like to have a column that tells me what score was 1st place, which was
2nd place, and which was 3rd place.

Finding the 1st place one is easy, using the =MAX(K4:R4).

But I'm at a loss to determine the 2nd place, and then the 3rd place one.



Then, once I determine that, can I use that value to do a lookup (for a new
column) and return the header?


For example, let's say we have the following row:

Team1 Team2 Team3 Team4 Team5 Team6
Team7 Team8
100 200 300 400 250
350 150 450


I'd like to have a "1st place score" column that would report 450 (for this
row)
I'd like a "1st place team" column that would report "Team8"
I'd like a "2nd place score" column that would report 400
I'd like a "2nd place team" column that would report "Team4"
I'd like a "3rd place score" column that would report 350
I'd like a "3rd place team" column that would report "Team6"


Then I'd be able to reproduce those formulae for every row through the
entire list of data.


Is there an easy way to do this?


In fact, now that I think about it, if there's a way to get the team names
without the scores, that would be acceptable too, but I figured I'd probably
need the scores first.



Thanks!
-Scott

=LARGE(K4:R4,1)
=LARGE(K4:R4,1)


"Scott M. Lyon" wrote in message
...
I've got an excel spreadsheet full of data, and I wanted to create a
column that would tell me what the second highest value was, given a list
of cells.


For example, I've got 8 columns (columns K through R), with headers "Team
1" through "Team 8".

Below that, on the following MANY rows, are numbers, associated with
scores for the team in that column.


I'd like to have a column that tells me what score was 1st place, which
was 2nd place, and which was 3rd place.

Finding the 1st place one is easy, using the =MAX(K4:R4).

But I'm at a loss to determine the 2nd place, and then the 3rd place one.



Then, once I determine that, can I use that value to do a lookup (for a
new column) and return the header?


For example, let's say we have the following row:

Team1 Team2 Team3 Team4 Team5 Team6
Team7 Team8
100 200 300 400 250 350
150 450


I'd like to have a "1st place score" column that would report 450 (for
this row)
I'd like a "1st place team" column that would report "Team8"
I'd like a "2nd place score" column that would report 400
I'd like a "2nd place team" column that would report "Team4"
I'd like a "3rd place score" column that would report 350
I'd like a "3rd place team" column that would report "Team6"


Then I'd be able to reproduce those formulae for every row through the
entire list of data.


Is there an easy way to do this?


In fact, now that I think about it, if there's a way to get the team names
without the scores, that would be acceptable too, but I figured I'd
probably need the scores first.



Thanks!
-Scott

=LARGE(K4:R4,1) Largest
=LARGE(K4:R4,2) 2nd largest
=LARGE(K4:R4,3) 3rd largest
=LARGE(K4:R4,4) 4th largest

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Scott M. Lyon" wrote in message
...
I've got an excel spreadsheet full of data, and I wanted to create a
column that would tell me what the second highest value was, given a list
of cells.


For example, I've got 8 columns (columns K through R), with headers "Team
1" through "Team 8".

Below that, on the following MANY rows, are numbers, associated with
scores for the team in that column.


I'd like to have a column that tells me what score was 1st place, which
was 2nd place, and which was 3rd place.

Finding the 1st place one is easy, using the =MAX(K4:R4).

But I'm at a loss to determine the 2nd place, and then the 3rd place one.



Then, once I determine that, can I use that value to do a lookup (for a
new column) and return the header?


For example, let's say we have the following row:

Team1 Team2 Team3 Team4 Team5 Team6
Team7 Team8
100 200 300 400 250 350
150 450


I'd like to have a "1st place score" column that would report 450 (for
this row)
I'd like a "1st place team" column that would report "Team8"
I'd like a "2nd place score" column that would report 400
I'd like a "2nd place team" column that would report "Team4"
I'd like a "3rd place score" column that would report 350
I'd like a "3rd place team" column that would report "Team6"


Then I'd be able to reproduce those formulae for every row through the
entire list of data.


Is there an easy way to do this?


In fact, now that I think about it, if there's a way to get the team names
without the scores, that would be acceptable too, but I figured I'd
probably need the scores first.



Thanks!
-Scott