#### 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.

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).

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).
>
>
> 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

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 ...

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, ...