How to determine second (and then third) highest value in a list #2

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 


0
9/12/2005 7:10:51 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
677 Views

Similar Articles

[PageSpeed] 3

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

HTH

Alan P.

"Scott M. Lyon" <scott.RED.lyon.WHITE@rapistan.BLUE.com> wrote in message 
news:%23ECMD38tFHA.256@tk2msftngp13.phx.gbl...
> 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
> 


0
9/12/2005 7:33:13 PM
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:Z2))-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(COLUMN()-COL
UMN($AC1)+1,$S$2:$Z$2,0)),"")

Hope this helps!

In article <#ECMD38tFHA.256@tk2msftngp13.phx.gbl>,
 "Scott M. Lyon" <scott.RED.lyon.WHITE@rapistan.BLUE.com> 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
0
domenic22 (716)
9/12/2005 7:49:56 PM
=LARGE(K4:R4,1)
=LARGE(K4:R4,1)


"Scott M. Lyon" <scott.RED.lyon.WHITE@rapistan.BLUE.com> wrote in message 
news:%23ECMD38tFHA.256@tk2msftngp13.phx.gbl...
> 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
> 


0
ken.wright (2489)
9/12/2005 7:50:46 PM
=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" <scott.RED.lyon.WHITE@rapistan.BLUE.com> wrote in message 
news:%23ECMD38tFHA.256@tk2msftngp13.phx.gbl...
> 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
> 


0
ken.wright (2489)
9/12/2005 7:51:56 PM
Reply:

Similar Artilces:

Selected dropdown value pushes to other cells
Am currently working up a workflow checklist with various drop down cells for each task. One column the worksheet is a risk level with Data Validated options of High, Medium, Low or N/A. Other columns indicate item status, action by, etc. What I want to happen is that when someone selects N/A in the risk column (eg E24) the other columns (eg G24, H24 and I24) default to N/A as well otherwise they allow selction from other dropdown lists. Don't have much experience with VB but willing to give it a go if needed. Hi Matthew The following should get you started. I have ass...

Currency values set to 2 decimal places
Is it possible to set a currency value fixed to 2 decimal points a opposed to just rounded up or down. for example if 33.3% discount was applied to �10 it would be �6.66666 which would display as �6.67 Therefore if you had 3 x �6.67 it woul work it out as �20.00 and not �20.01. Pau -- clappu ----------------------------------------------------------------------- clappus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1613 View this thread: http://www.excelforum.com/showthread.php?threadid=27569 Yes, set the format to Currency, and that will work. -- HTH R...

filtering a mailing list
Operating System: Mac OS X 10.5 (Leopard) How do you filter a mailing list by zip code? Autofilter. If you can't be bothered to type a detailed question, we can't be bothered to type a detailed answer :-) On 7/03/10 12:23 PM, in article 59bb412f.-1@webcrossing.JaKIaxP2ac0, "tcmetrock@officeformac.com" <tcmetrock@officeformac.com> wrote: > Operating System: Mac OS X 10.5 (Leopard) How do you filter a mailing list by > zip code? This email is my business email -- Please do not email me about forum matters unless you intend to pay! -- J...

How to create a scatter chart with 2 "X" values with common "Y"s
I Need to create a chart with lots of horizontal curves at different Y values. there are 128 to be precise and I would prefer not to create each as an individual series unless ... is there a way to copy a series mapping it tonew cells? (There are also a few diagonal curves so a bar chart wont work.) Thanks! Just copy the cells from your spreadsheet that contain your data series, then select the chart, Edit/ Paste Special, and choose the relevant options, including "new series". -- David Biddulph "M_LeDuc" <M_LeDuc@discussions.microsoft.com> wrote in message...

Open form based on combo box value w/error message for blank combo box
I am working in an Access 2000 database that I've inherited. I'm trying to clean up some of the non-working functions. I am trying to open a form using the OnClick property for a button. On a job order form, there is an unbound combo box [CmbContactID] that gets its list from querying Table!ContactInformation.[CustomerCodes]. When populated, [CmbContactID] should provide the value to open FrmContactInformation when [CmdOpenContactForm] (a button) is clicked. I want an error message to display if the field is blank otherwise it will open the form for the customer code that is displaye...

Mercury to stop EBT support, Need a second supplier
Mercury just called me two days ago and told me they would stop using TPI and would no longer offer the value-added support for peripherals and EBT! Where else can I got for a processor that supports EBT and RMS? david Go to TPISoft.com and use any of the processors their RMS client (which Mercury uses) supports. If you can't decipher the info on the web site, call them. They'll get you set up. Mercury uses Global Payments as their processor, so could you. Email me if you'd like to be put in contact with a Global rep. Mercury is switching from TPIsoft to the "prefe...

determine mail flow problem!!!!!!!!
Help! :-( Our mailflow from 2 backend 2003 exchange servers keeps queuing up in the outbound queue (which is set to always run delivery). Messages stay in there from 10 to 120 minutes before being sent to our gateway MTA and then sent off site (to hotmail.com for example). However when I telnet from a backend server to the gateway over port 25 and send a test message it goes right away. I can't for the life of me figure out why. One example when viewed in message tracking history says: 10:16am - smtp: store driver, message submitted from store 10:16am - smtp: message submitted to ...

Initial Account Sync with Fifth Third Bancorp
Program: Microsoft Money 2007 Deluxe OS: XP Bank: Fifth Third Bancorp I just purchased and installed the software. I added a credit card account (Chase), which synced fine on the first try. I then added my banking information (checking and savings). That account is with Fifth Third Bancorp. I have tried several times to sync and it always gives me the following error: Microsoft Money could not receive information from your bank or brokerage. Please set up this account again. if the problem continues contact customer support. Money was unable to complete the opreation. The Internet, bank...

need to create a list box based on the selectin of another list bo
I have a list of departments in the first list box, but depending on which departement the user selects, the second list box will offer a different selection of sub departments. EG Schools , then the lsecond list of all schools would be displayed but if Education Management had been selected, then a different second list would be displayed to choose from I currently do this in Excel by naming the ranges and using the =indirect in the data validation. Can something like this be done in access Any help on this would be extremely helpful Thanks You'll find a sample at: http://www.ac...

Invoices #2
Is there a way to default different invoices based on the customer? What do you mean by default different invoices based on the customer?! "Christina" wrote: > Is there a way to default different invoices based on the customer? Christina, If you mean default different invoice type ID's or invoice formats based on the customer, I don't think you can do that without a customization. -- Victoria Yudin Dynamics GP MVP Flexible Solutions, Inc. "Christina" <Christina@discussions.microsoft.com> wrote in message news:B00608ED-3BEE-4A5C-96AD-81A8DC7EA6EE@...

Outlook hangs up #2
I keep getting this error. It hangs up everytime I try to exit out of Outlook and sometimes when I am going from one task to another. Has anyone had this and know how to fix it? Jan Event Type: Error Event Source: Application Hang Event Category: (101) Event ID: 1002 Date: Time: User: N/A Computer: Description: Hanging application OUTLOOK.EXE, version 11.0.5510.0, hang module hungapp, version 0.0.0.0, hang address 0x00000000. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 41 70 70 6c 69 63 61 74 Applicat 0008: 69 6f ...

Hiding Pie Slice When Value is Zero
My data labels contain the Value and Percentage. I can hide the Value of a slice (when its value=0) when I set its format to ;000. But I can't figure out how to not display the slice since the data label Percentage still shows up as 0%. Any help would be greatly appreciated. Thanks, Bob Why not just filter the data (showing only >0) and use the entire (unfiltered) range as source data. The chart will not show hidden values - only the filtered values. Sandy "Bob" <Bob@discussions.microsoft.com> wrote in message news:91337249-7D87-4CCC-8B06-D1174A5AF212@microsoft...

How can I default a lookup field to a particular value.
How can I default a lookup field to a certain value, but still allow the user to modify the field if necessary? Thanks, Bob Johnson you mean a drop down or picklist? "Bob" <rjohnson@reveregroup.com> wrote in message news:1130947400.655495.115390@f14g2000cwb.googlegroups.com... > How can I default a lookup field to a certain value, but still allow > the user to modify the field if necessary? > > Thanks, > > Bob Johnson > i have the same question in crm 3.0. i want a default value in a lookup filed. i also want to create a lookup field by my self....

Disaster Recovery #2
I have been tasked with developing a disaster recovery plan with a remote site. My thinking is that if something happens to our main office, we can use Terminal Services to get to all of apps. I want to have installations of all our apps set up and waiting. My concern is licensing. Will I have to buy extra licenses for GP, or is there some kind of loophole for DR sites? Any help or thoughts would be greatly appreciated. Jason Hello Jason, Dynamics is a concurrent user license. So it doesn't matter if you have one installation or five installations of the Dynamics GP pointing to...

Return Maximum value
Hi Looking to find a formula that will calculate a maximum bid figure from an array. Example data. Products Bidder Amount Product 1 John �50 Product 2 David �50 Product 1 William �55 Product 1 Jill �45 Product 3 Tom �60 Product 3 Gwen �30 So when I put s table together of all Product I get the highest bidders for each e.g. Products Bidder Amount Product 1 William �55 Product 2 David �50 Product 3 Tom �60 Any ideas? Thanks 1)List your products from E1:E3 2)Put this formula in Cell D1: =MAX(IF($A...

Adding data to dropdown list?
Hi I want a dropdown list to work like this: I start to write in the cell and if the value is in the Name-list i will open the list to choose from, if else let me type in a new valu which is added to my name-list and stored. Someone? /bibe ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Create a Category List --Type your list of categories --Select the cells in the list --Cl...

Folder List Not Sorted
Had to rebuild a hard-drive and reinstall Office 2000 SP3. My Folder List is now NOT sorted, for example my "Personal Folders" entry is BEFORE "Outlook Today", instead of AFTER, as it should be. I've seen a handful of postings on this problem, but never a response. Bug? What is the work-around to this one? Gerald <gerald.rice@lmco.com> wrote: > Had to rebuild a hard-drive and reinstall Office 2000 > SP3. My Folder List is now NOT sorted, for example > my "Personal Folders" entry is BEFORE "Outlook Today", > instead of AFT...

Display total value on subform
I have a job-costing page for our orders, which is a subform on a tab control linked to all other parts of the database by Ref. The aim is to show all costs, with total, then all revenue, with total, then the profit. The incoming costs are entered here and then stored in a table, no problem. Sales invoices are generated via other forms and I have a query that displays just the basic details for each invoice filtered by Ref so that we see only the invoices raised for that particular current order. These are displayed on a nested subform based on the query. When there are several sales invo...

degrees minutes and seconds
how can i make an angle in decimal show as an angle in degrees minut and seconds -- grandfilt ----------------------------------------------------------------------- grandfilth's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2824 View this thread: http://www.excelforum.com/showthread.php?threadid=48665 On Sun, 20 Nov 2005 08:36:14 -0600, grandfilth <grandfilth.1ysv4m_1132497601.1679@excelforum-nospam.com> wrote: > >how can i make an angle in decimal show as an angle in degrees minuts >and seconds? A1: Decimal Angle B1: =A1/24 Format/Cells/N...

crm list of values of picklist
Hi, I would like to have a list of all values of a picklist from CRM, I know I can do this with an export of an entity, but then I also get <value><description=""> and so on, I was trying to copy a list of this values from SQL but I cannot find the table/column where these values are shown by id, does anybody know where in SQL I can find the picklist values? Regards, Dirk It's the StringMap table in the _MSCRM database. -- Michael H�hne, Microsoft Dynamics CRM MVP Web: http://www.stunnware.com/crm2 Feed: http://www.stunnware.com/crm2/atom.aspx Custom Lookup Dia...

Track Changes #2
Dear all, Plz help me out in this problem. I have a excel file that has been kept on a network (we call it G drive). Access to this file is restricted by the system administrator. Only one person uses this file at a time - since if any other opens it, it shows a read only notification. My problem is how do i know that : 1. what changes were made 2. by whom it were made 3. when they were made 4. Also i want a listing of these changes made so that in future if they are required then it can be extracted easily, instead of going to all the persons and asking for what changes have been made. ...

how to customize pick list such as industry
I would like to know how to customize pick list such as industry code? We have seen that the pick list of the industry (branch) is two times in CRM. Once in the Leadmanagement and once in the Accountmanagement. They have to contain the same data, because when we convert a Lead in to an account, CRM takes the id of the industry from the Lead and puts it to the new account. If the id doesn't excist in the industry pick list, crm will not convert the lead correctly. In the formsetting i can't set the id of an industry code. How can I adapt this two Lists and how can I recover the old Da...

Count Formula #2
Greetings, I have a column of data that looks similar to the following: 0.24 0.75 1.24 0.13 0.98 1.36 0.43 0.57 Could someone please help me with a formula that could look at all values in the column and then return the number of values that lie within specified ranges. For example, I want to count the number of data points that are < .50,the number of data points that are > or equal to .50 AND <1.00, and the number of data points that are > or equal to 1.00. Any help would be greatly appreciated. Thanks. Jason use countif for one condition =countif(myrng,1) use sump...

Web Outlook #2
How do I add another account into Web Outlook? I need to add a hotmail accopunt and another suer that belongs to the same group and I have full rights to see the e-mails. Are you talking about Outlook Web Access? if yes, then I would ask my Exchange admin about this. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, VCanello asked: | How do I add another account into Web Outlook? I need to add a hotmail | accopunt and another su...

Exchange 2003 Service Pack 1 #2
from Exchange 2003 server, how to check that it has been upgraded to Exchange 2003 service pack 1? On Sat, 4 Feb 2006 04:36:34 +0800, "Emyeu" <cmchong20@yahoo.com> wrote: >from Exchange 2003 server, how to check that it has been upgraded to >Exchange 2003 service pack 1? > By skipping it and applying Sp2. Properties of the server object in ESM is the quickest way - or run ExBpa against the server. In the properties, you'll see the build number 6.5.xxxx. Use this to translate: http://support.microsoft.com/kb/158530/en-us "Andy David - MVP" ...