# Multiple Ranking: Rank The Values In Column B And Then Rank The Values In Column C

Feb 11, 2009

What I am trying to do is give the rank in column D based on the values in columns B and C. Some of the values in column B will have then same rank, and as such I want to add further criteria on which to rank them. I would first like to rank the values in column B and then rank the values in column C, which should give the rank in column D. For example Dog and Frog have the same value of 400 from the Non UK column. Therefore, rather than having these as both rank 1, I want them to be ranks 1 and 2, so want to add another criteria (UK). As Dog is greater than Frog in the UK (i.e. 10>7), I would like to rank Dog as 1 and Frog as 2. Goat will be ranked as 3 because it had the thrid highest value in the Non UK.

ABCD
1Non UKUKRank
2Cat20055
3Dog400101
4Eel200114
5Frog40072
6Goat30023

## Ranking Values And Ranking Duplicates The Same Rank

Mar 30, 2014

Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.

Example:
20
18
18
15
12
11
11
9

The ranking I am looking for is:
1
2
2
3
4
5
5
6

## Rank In Column By Looking At The Values In Other Column

Dec 4, 2008

I have a table with 17 rows that I want to rank in Column L by looking at the values in Column K. How do I do it so that zero values are not factored in. Also, there will ties in value, so how do I account for that?

## Ranking :: What Formula Would Be Used To Rank Scores

Jul 31, 2007

what formula would be used to rank scores if you had the following

name score
participant 1 14
participant 2 27
Participant 3 19

to get this result

Participant 2 27
Participant 3 19
Participant 1 14

## Rank The Values

Jul 23, 2006

I have 10 sales figures in C2:C11.

In D2:D11, I want the rank of those values, with one twist.

If the value in B2:B11 is X, then that value is excluded from the ranking.

I am trying to basically do:
=RANK(C2,IF(B2:B11="X",0,C2:C11))

## Rank Values

Aug 19, 2009

Which formula can I use to rank the people below.

Consider that the lower the value on attendance and cancellations is, the better this number will be.

In the other cells the highest the value, the better the number is.

NameAttendanceUtilizationProductivityQualityCancellationsCharles089.38%26.60NA0Nick587.69%56.921001Brian384.51%51.32800Joe080.71%36.151000

## Use RANK And COUNTIF Combination To Get Unique Ranking

Dec 6, 2013

I used the RANK and COUNTIF combination to get the unique ranking of a range. For example, the formula is:

=RANK(A1,\$A\$1:\$A\$890,0)+COUNTIF(\$A\$1:A1,A1)-1

To my surprise, the failed to produce the results that I want. I attached a sample file here. Basically, the two ranks of 416 and 418 are not there.

A source below really shows the formula should work. [URL] .......

## Ranking Error :: Formula To Rank Employees

Feb 12, 2009

I had tried ranking formula to rank employees on some cirteria it was working, but if there is any tie between two employees then the next rank is not displaying for example in Ach Rank : rank 5 is missing because there is tie is between two employees is rank 4. Request your suggestion and help on this for overcoming the issue.

Formula used for ranking : =IF(B2>=75%,RANK(B2,\$B\$2:\$B\$41,1)-COUNTIF(\$B\$2:\$B\$41,"

## Rank Strings And Values?

Feb 22, 2014

Using formula, how do I rank

CAM100-24
COM98-21
CAM100-18
CAM27-37
COM121-5
COM98-23

so it first sorts in alphabetical order using the string before the "-" i.e. CAM27 comes before CAM100, and then by the biggest 'secondary values' (the value after the "-") so that COM98-23 comes before COM98-21?

## Rank Duplicate Values

Aug 15, 2008

I have a list of values (some are duplicates) which I need to rank.

The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.

Example:

Value Rank Desired Rank
120 1 1
125 2 2
130 3 3
130 3 3
142 5 4

## RANK Question - Top 20 Values

Dec 7, 2007

Got a question about RANK. As you can see from the example below, I want the rank of 1 to be 6, not 7 - same values keep the same rank but the next value takes the next rank number.

Is there a way to achieve this - I need to rank around 4000 lines...

## Rank Range That Contains #N/A Values

Sep 25, 2008

Anyone know of a function that will return a rank of cell C3, in range C3:C800 that does contain a few #N/A values?

## Rank The 5 Lowest Values

May 6, 2009

In a range of 27000 records I dlike to find the lowest 5 values excluding, div0, N/A and other error things like that

## Rank When 2 Values Are Equal

Aug 26, 2006

am currently setting up a fantasy football league.

i have set up the league, with team name overall points and position, my problem is that when 2 teams have the same points one team is omitted and the other team has its name on both entries

the formula's i am using are

vlookup = to find the name of team for the points
large = to find out points of each team

## Rank Values From 2 Sheets

Feb 28, 2008

I have two sheets of data and would like to rank according to the order in which the data fit on both sheets. Rank function only seems to allow one sheet at a time.

## Ranking Visible Cells With Ties Without Skipping Rank

May 19, 2014

I have data that has filters and hidden rows and my current formula ranks with ties which is what I want, but I do not want the ranking to skip numbers.

Value Rank Now Rank I want
508 6
1001 1
696 4
704 3
833 2
557 5
1001 1
704 3
508 6

## Replace All Sheet Values With Their RANK

Jul 16, 2014

Attached is an excel file that contains all possible values in the first column (9238 of them) and a comma separated list of a subset of those values in the second column. I need to find a way to replace the smallest value with 1, the next smallest value with 2, ... , and the largest value with 9238 in every instance. In other words, I need their rank value.

OhioBlockGroup.xlsm‎

## Using Match & Large To Rank Values.

Mar 24, 2009

I'm using a Large function embedded in a Match function to a ranking.

The issue I'm having is that if there are 2 of the same values in the Top 5, it counts the first one twice rather than each one once.

## Rank Function Ignoring Only Zero Values

Sep 12, 2013

I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?

=IF(D94=0,"",RANK(D94,\$D\$94:\$AY\$94,0))

## Rank Values Into Top/Bottom X Scale

May 21, 2008

I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.

I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?

## Getting Duplicated Values Depending On Rank Order

May 28, 2009

So, I have some names and values. Rank function give me order for those values.
Small function gives me ascending order. I want to get first n (let say 5) values back next to each other but can't use VLOOKUP function because sometimes I get duplicates (red numbers).

If there is more same numbersthat small function returns... It need to give me all of them, no matter 5 is limit. how to get back values of rank function that are duplicated. Book1.xls

## Rank Formula Error With Same Values (Ties)

Feb 22, 2008

I'm having problem with ranking results (ascending) with similar values e.g. values 1, 2, 2 and 4 fails by use of Rank-formula (rank: 1, 2, #N/A and 4) as value 2 appears twice.

I've attached a simple example of the problem.

## Rank Of One Selected Value From Column A

Jan 20, 2010

In Range A9:A32 there is a list of names, In range B9:B32 there are values.

I don't want to rank all values just the one I chose. So I need a formula that I can enter in a cell and get the rank of one selected value from column A

## Rank Instances Of Phrase In A Column

Mar 21, 2014

Let's say this is my data. It's already been sorted so that like entries are all grouped together.

Ex1
Ex1
Ex1
Ex2
Ex2

I need to make a macro that numbers my data like below.

1
Ex1

2
Ex1

3
Ex1

1
Ex2

2
Ex2

Essentially, my data is many columns and sorted on a bunch of different levels. I need a quick way to assign a rank to every row in the same group, to see at a glance which performed the best.

## Rank Function In The Top Ranked Column

Jul 23, 2009

I have 3 columns, A B C: Distribution Center, Sales, and Top Ranked. What I am trying to get is a Rank function in the Top Ranked column that looks for the highest value in the Sales column, but returns the Distribution Center associated with this Sales value instead of the actual value. I could get this done with some helper columns and a VLOOKUP, but the report is rather large and the VLOOKUP not only slows it down but also pushes the file size over the limit. Also, I will be using this for metrics other than Sales where I'll be looking for the highest and lowest figures, so if a Max or Min function works better, that would be fine. I just haven't been able to figure out how to have it return the associated data instead of the ranked data.

## Rank Column Range Of Numbers

Jun 1, 2008

Without sorting the original field, I am attempting to compare the values in column 1 and then assign a "place" value in column 2 ( cell) next to each value compared starting with 1 (least value) thru n ....until all values have been compared.

Column 1 Column 2
52.1 7
47.4 3
50.0 4
100.0 9
45.3 1
46.3 2
51.1 6
50.5 5
52.2 8

## Rank Column In Order Of Number Size?

Feb 17, 2013

i would like to rank a column so that when entering a number in a cell it will move up the column so that the greater number falls down the colum and the larger rises up the column

ie

list

6
5
7
8
9
1
3
2
4

------------------------
list

1
2
3
4
5
6
7
8
9

## Sum Year To Date Based On Month Chosen, Rank Values & Compare Rankings

Dec 22, 2008

1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.

2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.

3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).

## Rank Items In Column K From Highest To Bottom With Corresponding Number In J

Dec 20, 2013

I am looking to have the items in column K 1 through 16 listed from highest to lowest and at the same time post the corresponding number from column J 1 through 16.

So that it reads as follows:

04 - 18 - 06
02 - 17 - 06
08 - 10 - 03

Item 04 has 18 points and is listed in 6 columns etc.

How can I get Excel to do this?

## Ranking Greater Than "0": Able To Rank Players Only If They Meet Or Exceed A Specific Number Of Rounds

May 26, 2009

I want to be able to rank players only if they meet or exceed a specific number of rounds. I have been able to get close but I would like to have the ranking start at "1" instead of "0" (see sheet).