Rank Question

I need to figure out how to rank by manager  ex:

Processor    Manager    Score   Rank 
Albert          Scott         80.9       2
Brian           Scott         91.5       1
Cathy          Scott         79.3       3
Mable         Delores      99.9       1
Elaine         Delores      95.6       2
Tony          Delores       73.5      3

I have 100 or so managers so I want a formula to calculate it automatically 
without any manual intervention.

Thanks
0
Utf
2/1/2010 7:38:04 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
692 Views

Similar Articles

[PageSpeed] 51

In EXCEL 2007, assuming your data is in range A 1 to D 7 inclusive. 

1. Gives cells C 2 to C 7 inclusive a Range Name of, for example, RangeName. 

These 6 cells contain your Score column. 

2. In cell E 2 type the following:-

=RANK(C2,RankRange) 

 - and copy the above down and including cell E 3 to and including cell E 7. 

3. Your automatic rankings will now be in the E column. 

The highest score will be ranked first (99.9) and the lowest score will be 
ranked 6th (73.5). 

Please hit Yes if my comments have helped. 

Thanks. 

"dchristo" wrote:

> I need to figure out how to rank by manager  ex:
> 
> Processor    Manager    Score   Rank 
> Albert          Scott         80.9       2
> Brian           Scott         91.5       1
> Cathy          Scott         79.3       3
> Mable         Delores      99.9       1
> Elaine         Delores      95.6       2
> Tony          Delores       73.5      3
> 
> I have 100 or so managers so I want a formula to calculate it automatically 
> without any manual intervention.
> 
> Thanks
0
Utf
2/1/2010 8:04:02 PM
I need it to rank by Manager, the manager could change  (see the example 
under the Rank column)- and I am using Excel 2003

"trip_to_tokyo" wrote:

> In EXCEL 2007, assuming your data is in range A 1 to D 7 inclusive. 
> 
> 1. Gives cells C 2 to C 7 inclusive a Range Name of, for example, RangeName. 
> 
> These 6 cells contain your Score column. 
> 
> 2. In cell E 2 type the following:-
> 
> =RANK(C2,RankRange) 
> 
>  - and copy the above down and including cell E 3 to and including cell E 7. 
> 
> 3. Your automatic rankings will now be in the E column. 
> 
> The highest score will be ranked first (99.9) and the lowest score will be 
> ranked 6th (73.5). 
> 
> Please hit Yes if my comments have helped. 
> 
> Thanks. 
> 
> "dchristo" wrote:
> 
> > I need to figure out how to rank by manager  ex:
> > 
> > Processor    Manager    Score   Rank 
> > Albert          Scott         80.9       2
> > Brian           Scott         91.5       1
> > Cathy          Scott         79.3       3
> > Mable         Delores      99.9       1
> > Elaine         Delores      95.6       2
> > Tony          Delores       73.5      3
> > 
> > I have 100 or so managers so I want a formula to calculate it automatically 
> > without any manual intervention.
> > 
> > Thanks
0
Utf
2/1/2010 8:16:02 PM
Assuming Manager name is in column B and are grouped together, score in 
column C...
In D2:
=RANK(C2,OFFSET(INDEX(Sheet1!$C:$C,MATCH(Sheet1!$B2,Sheet1!$B:$B,0)),,,COUNTIF(Sheet1!$B:$B,Sheet1!$B2)))
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"dchristo" wrote:

> I need to figure out how to rank by manager  ex:
> 
> Processor    Manager    Score   Rank 
> Albert          Scott         80.9       2
> Brian           Scott         91.5       1
> Cathy          Scott         79.3       3
> Mable         Delores      99.9       1
> Elaine         Delores      95.6       2
> Tony          Delores       73.5      3
> 
> I have 100 or so managers so I want a formula to calculate it automatically 
> without any manual intervention.
> 
> Thanks
0
Utf
2/1/2010 8:24:05 PM
This is perfect!!!! Thank you very much.

"Luke M" wrote:

> Assuming Manager name is in column B and are grouped together, score in 
> column C...
> In D2:
> =RANK(C2,OFFSET(INDEX(Sheet1!$C:$C,MATCH(Sheet1!$B2,Sheet1!$B:$B,0)),,,COUNTIF(Sheet1!$B:$B,Sheet1!$B2)))
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "dchristo" wrote:
> 
> > I need to figure out how to rank by manager  ex:
> > 
> > Processor    Manager    Score   Rank 
> > Albert          Scott         80.9       2
> > Brian           Scott         91.5       1
> > Cathy          Scott         79.3       3
> > Mable         Delores      99.9       1
> > Elaine         Delores      95.6       2
> > Tony          Delores       73.5      3
> > 
> > I have 100 or so managers so I want a formula to calculate it automatically 
> > without any manual intervention.
> > 
> > Thanks
0
Utf
2/1/2010 8:39:56 PM
Chapeau !!!
Micky


"Luke M" wrote:

> Assuming Manager name is in column B and are grouped together, score in 
> column C...
> In D2:
> =RANK(C2,OFFSET(INDEX(Sheet1!$C:$C,MATCH(Sheet1!$B2,Sheet1!$B:$B,0)),,,COUNTIF(Sheet1!$B:$B,Sheet1!$B2)))
> -- 
> Best Regards,
> 
> Luke M
> *Remember to click "yes" if this post helped you!*
> 
> 
> "dchristo" wrote:
> 
> > I need to figure out how to rank by manager  ex:
> > 
> > Processor    Manager    Score   Rank 
> > Albert          Scott         80.9       2
> > Brian           Scott         91.5       1
> > Cathy          Scott         79.3       3
> > Mable         Delores      99.9       1
> > Elaine         Delores      95.6       2
> > Tony          Delores       73.5      3
> > 
> > I have 100 or so managers so I want a formula to calculate it automatically 
> > without any manual intervention.
> > 
> > Thanks
0
Utf
2/1/2010 8:43:01 PM
Try this...

Entered in D2 and copied down as needed.

=SUMPRODUCT(--(B$2:B$7=B2),--(C2<C$2:C$7))+1

-- 
Biff
Microsoft Excel MVP


"dchristo" <dchristo@discussions.microsoft.com> wrote in message 
news:7776CAFF-574D-42B7-B2E2-C1DD27F935D1@microsoft.com...
>I need to figure out how to rank by manager  ex:
>
> Processor    Manager    Score   Rank
> Albert          Scott         80.9       2
> Brian           Scott         91.5       1
> Cathy          Scott         79.3       3
> Mable         Delores      99.9       1
> Elaine         Delores      95.6       2
> Tony          Delores       73.5      3
>
> I have 100 or so managers so I want a formula to calculate it 
> automatically
> without any manual intervention.
>
> Thanks 


0
T
2/1/2010 10:13:44 PM
Reply:

Similar Artilces:

Rank within Categories
I am trying to work out a way to rank within categories. Column E has different categories. Column i has the data. Column am working on i want to give the ranking where each data point falls within its own category. i have tried to hash something out but am stuck...here is my feeble attempt =IF($E5="SE",IF($E$5:$E$40="SE",IF(I5>0,RANK(I5,$I$5:$I$40,0),"N/ A"), ...... ..... signfies a string of IFs. the Next one being IF($E5="C".... I have also tried an array.....that doesnt seem to work =IF(OR(E6={"C","SE","NE","...

Windows Question..
Hello all. I know this is a Access newsgroup but I don't know where post my question. the problem that I have is: I restarted my PC and when I log on again, it creates a new username for me, before I was using ldiaz but know I have checked that my new user appear as ldiaz.SAFARILAND where Safariland is the Domain name all my documents are still stored as ldiaz's Documents and the main documents that I have is ldiaz.SAFARILAND / My Documents also, all modification that I make are lost after reboot, could you please help or tell me where I could post my question. Thanks -- L...

Attachment question
When double clicking on an attachment I get a message if I would like open or save the file. I would like to open it but I don’t want to answer this question again. There is an option which says “Always ask before opening this type of file” which I want to turn off but can’t since it is greyed out. I was able to turn this off on WIndows XP but I cannot do this on Windows Vista. Any ideas? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-outlook/201005/1 On Tue, 11 May 2010 03:05:49 GMT, "Shawn76 via OfficeKB.com" <u57604@uwe> w...

Question about automating Word within vb6
Hi, I have a program for writing reports that is used by companies whose users are not very good with computers or using various other programs such as Word or Excel. To make sure that the users always saved the reports with the correct name and in the proper place, I have save and close options within the program. To keep them from inadvertently using the Word Save or Close options, I had a toolbar always on top which covered the Word options and would allow the users to save the report within the program properly and in the correct place. Since I use a .dot form if they trie...

List a ranking
I'm a newbie at this, so help is appreciated. I have an Excel sheet like this: Team A .600 =rank(b1,b1:b3) Team B .750 =rank(b2,b1:b3) Team C .300 =rank(b3,b1:b3) I want to get another cell to list how far they are from the #1 rank. So, cell d1 should be equal to .150 and d3 equal to .450. The percentages will change frequently, so it needs to figure out what is #1. I know I could just do =b2-b1 and b2-b3, but only because I can see the b2 is the highest percentage. Am I making sense? Is there a way to do this? TIA. BR =MAX($B$1:$B$3)-B1 etc. -- HTH RP ...

Address book question #4
Any advice on the following would be appreciated: Using OfficeXP on the pc, if I want to use Outlook's address book from Word to add an address to a envelope I am able to see all contact address's On my laptop, trying to do the same above I am only able to see contact address's that have where the contact has an email or fax number What setting do I need to change on the laptop so that I am able to see all contact address's TIA Tom ...

Percentages Question
Column A is formatted as a percentage, with 4 decimal places. When I enter 5 or .05, I get the same result (5.0000%). This is obviously not correct. If the rule is to multiply the number entered by 100, then 5 should be 500.0000% and .05 should be 5.0000%. So what's wrong here, and how do I make sure that users (and me), don't enter the wrong values? Thanks, Bernie Excel likes to help--but you can tell it not to. Tools|options|edit tab| uncheck "enable automatic percent entry" bw wrote: > > Column A is formatted as a percentage, with 4 decimal places. > ...

Basic question about Office 2004 for Mac versions
I would like to know the basic differences between two version of the same software: Office 2004 for Mac. It is available as "Office 2004 for Mac All Languages" and "Office 2004 for Mac English". Can someone kindly outline the differences between these two, so that I can make an informed decision for purchase? On 10/1/04 9:34 AM, in article bdfe6044.0409301634.13626e58@posting.google.com, "Kausik Datta" <datta_kausik@yahoo.com> wrote: > I would like to know the basic differences between two version of the > same software: Office 2004 for Mac. It is a...

CreateObject(), GetObject() question
I have the following sub Sub test() 'it doesn't matter which of these I use. Dim olApp As Object 'Dim olApp As Outlook.Application 'always a (8007007e) error with following line. 'Set olApp = CreateObject("Outlook.Application") 'never get an error as long as Outlook is open Set olApp = GetObject(, "Outlook.Application") Set olApp = Nothing MsgBox "Done" End Sub I have a reference set to Microsoft Outlook Object Library. I commented out the CreateObject() line because I get a ...

Some IMF question
Hi, For those emails marked as ****SPAM**** by IMF, how can I make them go directly to the user's Junk email folder instead of Inbox ? TIA Steven On Mon, 15 Aug 2005 09:53:37 +1000, "Steven Wong" <sazabi75@hotmail.com> wrote: >Hi, > >For those emails marked as ****SPAM**** by IMF, >how can I make them go directly to the user's >Junk email folder instead of Inbox ? > >TIA > >Steven > Lower the store threshold SCL number? You must enable the Junk Email folder through OWA. Junk Email enablement can be a bit tricky check these article...

rank
I would like to know how to get around duplicate issues when using th rank functio -- Message posted from http://www.ExcelForum.com Hi see: http://www.cpearson.com/excel/rank.htm and http://www.xldynamic.com/source/xld.RANK.html -- Regards Frank Kabel Frankfurt, Germany > I would like to know how to get around duplicate issues when using the > rank function > > > --- > Message posted from http://www.ExcelForum.com/ ...

layaway creation question
I'm having a difficult time setting up the layaway section of RMS for customers. When I follow the instructions in the help menu and tender the transaction no money is deducted from the selling price of the item. What am I missing, can anyone help me with this seemingly simple problem? FIL In manager, file - configuration - ordering set the min % deposit if applicable for your layaways....when you create a layaway at POS and go to tender, the percentage will show in the tender screen (ie 10%) finish transaction as usual - those monies will be tracked as a deposit on the layawa...

Trigger question 03-15-10
CREATE TABLE tblA ( Symbol varchar(50) NOT NULL, APIFormat varchar(50) NULL, DataFormat varchar(50) NULL ) I would like to do the following: If a new row is added or APIFormat is edited, and DataFormat is NULL, I would like to set DataFormat like this: If RIGHT(APIFORMAT,1) = '#', then SET DataFormat = LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0' If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT Can I create a trigger to do the above ? Thank you On Mar 15, 10:09=A0am, "fniles"...

Two outlook 2002 questions
1.when i am creating a note, it lets me choose names from the contacts folder or data base. But, the names are alphabetized by first name, not last name. How do I change that. It is no help to have the first names alphabetized. 2.I have two email profiles in outlook 2002. how do i change the setup so that it prompts me as to which account that i want to read emails for? I can get mail form profile#1 but I can't get to profile#2. 1. Go to Tools > E-mail accounts > View or change existing directories or address books > Outlook Address Book > Change. > Set your sort ord...

subtotal rank
I'm working with a filtered list and I'm looking for a rank formula that works like the subtotal formula does. So when I filter the list it ranks by the particular filter. Thanks in advance BW --- Message posted from http://www.ExcelForum.com/ ...

what exactly is the 'alias' for? and a few other related questions.
exchange 2000 sp3 on windows 2000 server sp4 When I make user accounts the username and email naming conventions differ. For example, username is firstname only and email address is first initial of first name followed by a '.' and then the last name. So as I create an account I enter the username, then when I get to the exchange part the username is pre-populated as the alias, which I change to our naming convention. The email address gets created according to the alias. example: username: joe alias: j.schmoe email: j.schmoe@domain.com (by default based on alias) I have never had a...

CPropertySheet question
Does anybody know to to change the position of a button (the OK button for example) in a CPropertySheet ? I wuold like to center it instead of having it right aligned. Thanks in advance Andrea hello Andrea i think you can use MoveWindow function to change the button positions. get the actual position and adjust it as required and change the positions of other buttons also. sreeram "Andrea Palmieri" <andreno_spamalmieri@enmo_spamm> wrote in message news:OHFBwXhQDHA.3768@tk2msftngp13.phx.gbl... > Does anybody know to to change the position of a button (the OK button for...

question from e-learning
Hi! At the end I have a question from e-learning. Because of the question is saying that there will be several writings we need to have an object we can't use the static class File. So we have b and d to choose from. The correct answer accorning to e-learning is d. Here is the motivation If you are going to reuse an object several times, you should use the instance method of the FileInfo class instead of the corresponding static methods of the File class, because a security check will not always be necessary. This will minimize any overhead and improve the performance of ...

SPF record question
Hello! I think I understand SPF records now (thanks to Jim Martin in another group), but I would like to know if anyone can confirm my questions. I found this page http://www.kitterman.com/spf/validate.html that lets one test an SPF record. It does not test version 2 yet. Let's call my domain "mydomain.net" and assume that my mail server is named "mail.mydomain.net." I have my DNS controlled by www.zoneedit.com and I have a backup mail server there to grab any mail if my server is not answering. When I use www.dnsreport.com to check my domain, it shows two MX re...

Crosstab Query Questions
Hey everyone, I've created a crosstab query. My Row heading is CourseName. My Column Heading is County. I have 10 countes in which I'm interested in. And 6 different courses. I am running a monthly report. Not all courses run in each county each month. So in my Value field in my Crosstab Query, I have it set to Expr1: CLng(Nz(Avg([tblCourses]![StartDate]-[tblCourses]![Date Approved]),0)) And I want the Average of these numbers so I'm using the AVG function in the total. When I run the query, it catches the data I want and it calculate the average. However, ...

Question about hyperlinks
What I am trying to do is, through the use of a form and the base url of the company website concatenate the prisoners number [PNumber] field and the base URL of the website. The base URL is as follows - http://www.state.mi.us/mdoc/asp/otis2profile.asp?mdocNumber= The prisoner information is entered into the database via a text file and a macro using the transfertext method. Unfortunately, the website link is not available in the text file. I have tried two different approaches and neither seem to work. In my table I have the following fields, [PrisonerURL] & [PNumber]. One sce...

Ranking in Excel
Hi All I need to rank numbers entered in column A in ascending order while recognising ties but not skipping ranking numbers. The numbers can range from 1 to 1,000 but there are only 40 rows of data in column A. Any help would be much appreciated. Kind Regards Celticshadow To do this with worksheet functions, you need to sort your list in your desired (Ascending) order. If your numbers start in A2, then in B2, enter a 1, and in B3, enter the formula =IF(A3=A2,B2,B2+1) and copy down. IF your numbers cannot be sorted, then you would either need another helper column or VBA. -- HTH, ...

EX2003 Jounaling question- How to journal existing mail?
Hi, I have recently purchased a product from GFI named MailArchiver. What this program does is take all mails from the journaling mailbox and import them into an sql database. Users and admins have their own web interface to view all emails and attachments which were sent/received since journaling was enabled and their program was set up. I enabled Jounaling just before Christmas. I would like to know how I can get all the mail that existed before Christmas to flow through the journal mailbox so that it will be imported into that SQL database and retain the users rights to those ma...

Rank
I would like to know how to get around duplicates when using ran -- Message posted from http://www.ExcelForum.com Hi see your other posts please don't multipost -- Regards Frank Kabel Frankfurt, Germany > I would like to know how to get around duplicates when using rank > > > --- > Message posted from http://www.ExcelForum.com/ ...

UI question regarding checkbox
In a form I would like to give the user the possibility to use the selection filter on a checkbox. The problem is that the user first has to set focus to the checkbox, most of the time done by clicking on it. With this action he also involuntary checks/unchecks the checkbox and he must reset it. How do you guys normally solve this problem? Tabbing to the checkbox doesn't seem very intuitive either escpecially as the checkbox is far away from the current field. Thanks, Lars Personally I prefer to use combo boxes for filters, but Martin Green at www.fontstuff.com has a g...