Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to determine second (and then third) highest value in a list
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 |
#2
|
|||
|
|||
The process of finding the biggest, the second biggest, the third biggest,
the fourth biggest,... is called sorting. You can sort by either columns or rows. Either ascending or descending. Pull-down Data Sort... -- Gary''s Student "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 |
#3
|
|||
|
|||
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 Hi Scott to find the 1st, 2nd, 3rd etc use the RANK function If your data is in A1 to H20, in J1 enter =RANK(A1,$A$1:$H$20), drag this formula down to J20 and across to Q20, the cells with 1, 2 and 3 in are in the equivalent positions to the highest 3 ranked scores in your data Still working on the other part -- Paul Sheppard ------------------------------------------------------------------------ Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783 View this thread: http://www.excelforum.com/showthread...hreadid=466899 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|