# Ranking With Tie Breaker

Nov 21, 2006
I'm tring to solve an issue with ranking with a tie breaker. I don't know how to explain it without sounding confusing so I'm attaching a sample, but basicly I want to be able to rank people with certain points and a set tie breaker value.

View 2 Replies
ADVERTISEMENT
Jan 15, 2010

I've got 3 columns of info.. Name, Org, #Sales, $$..

I need to rank the list by:

Org, $$, #of Sales (as the tie breaker)

So, I need it to look for a specific Org Dept in Column B, rank the Dollar Amounts in column C, and if there are any ties, go to #Sales in column D and use the biggest number there to break the tie.

I've found 2 formulas..

This one was awesome for looking for the specifc Org Dept and only ranking those numbers that matched the designated Org Dept. (but this can't break any ties)..

IF(ISERROR(MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0)),"",MATCH($O3,LARGE(IF($P$3:$P$53=R$2,$O$3:$O$53),ROW(INDIRECT("1:"&COUNTIF($P$3:$P$53,R$2)))),0))

Then there's this one that broke ties, but can't recognize a specific Org Dept that I need..

=RANK($O3,$O$3:$O$53)+SUMPRODUCT(($O$3:$O$53=$O3)*($F$3:$F$53>$F3))

View 9 Replies
View Related
Aug 11, 2014

I want to be able to rank different IDs for a certain name based on $$$. For example, if person A has several IDs, I want to be able to tell the top 5 IDs based on $$$. Also, there are two problems. One is, there are several duplicate IDs and I want to rank "None" for those which already have rank. Second, there are different IDs with the same $$$, I want to be able to tie break those.

View 3 Replies
View Related
Sep 22, 2009

I have a word document with sentences which has to be broken down with length of 65 characters (words should not be broken). This has to be stored in consecutive cells.

Example

sample text input

This is a sample sentence format from a document which has to be broken down with length of 65. This is created by 'VENOM' on 22-09-2009 for sampling it into a excel sheet named as 'The sample.xls'.

Output

This is a sample sentence format from a document which has to be

broken down with length of 65. This is created by 'VENOM' on

22-09-2009 for sampling it into a excel sheet named as

'The sample.xls'.

REQ:

1. words should not break

2. words with special characters should not break(like.. 22-09-2009)

3. words in quotes has to come in full (like.. 'The sample.xls'.)

View 9 Replies
View Related
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

View 2 Replies
View Related
Apr 18, 2014

Basically it is another sports modelling experiment that I have had (I get the day off work and come up with things like this ), where I just had a curiosity to play out the last games for the top 3 teams in the English Premier League (just to see if I could get some percentages).

The attached, is just the values from one run of my model (I have taken the workings out to reduce the size and for other reasons).

Basically everything to the left of column K, is the model and I am happy with it (and I know goals have to be integers - this is basic);

This plays the games out, and feeds the data into the table at M36 (highlighted yellow), which feeds the data tables at M42 (highlighted green), which are counted to get percentages in the tables (highlighted yellow at M6).

I have put some example output from runs on the second worksheet (so you can see how it works - I like that it suggests Chelsea can win even though it won't be very likely )

Basically you can probably see the problem in cell O22. The percentage chance of finishing first should equal 100% when totalled (e.g. the sum of Liverpool, Chelsea and Man City finishing first, as it has to be one of them should equal 100%). I tracked this back to the fact that I had no way of splitting teams level on points. As a result I added Goal Difference to the model (crudely - e.g. decimal) and here's the question;

How would I go about using Goal Difference to break tie's effectively?

I am thinking I could do some kind of IF function in Q37 (and then have a tie-breaked rank in column R to feed the data tables), on the basis of if anything in N37 to N39 was equal but I am not sure how I would go about applying this.

Example Values_EPL.xlsx

View 10 Replies
View Related
Aug 12, 2014

I am facing some problem with ranking formulae. Attached is a file illustrating them.

Problem 1: Ranking without zero I have a set of data for products. They have a number next to it. Some of the products have a ZERO value next to them. I want to rank the data in an ascending order while ignoring the products with ZERO value next to them.

Problem 2: Ranking on multiple factors I have a set of data for 50 products. There are 5 factors based on which I want to pick the best and the worst. Each factor has a value which can be a negative number, zero or positive number.

This is what I am doing presently: Ranking the products on each factor in a separate column. Points are assigned based on the ranking, i.e. if the ranking of "product A" on Factor 1 is 32, then it gets 32 points It is assumed that equal importance [weight] is given to each factor, thus average points is calculated based on ranking of each product on each factor. Average points are again ranked in descending order The top & bottom 10 are picked from this ranking.

Number of products and number of factors can be different for each analysis. Presently I am giving equal importance to the rankings for each of the factors, what if I have to give varied importance [weight] to the factors.

View 1 Replies
View Related
Jan 7, 2009

I have a sheet name "GP", and the range "A1:A5".

I need your kind help with a code that if the cells in the range become 10, then the cell across in column B will rank the order of the cell that scored 10.

ie: if A2=10 then B2=1st, A4=10 then B4 = 2nd, A5=10 then B5=3rd, A1=10 then B1=4th, A3=10 then B3=5th.

The ranking is placed according to the which cell results to 10 first.

View 9 Replies
View Related
May 4, 2006

i need to rank database and get an output of 1, 2, 3, 4, and ect. even if the first 2 had a tie of 1; 1;. So if i have 500 records and the first 3 are tie at 1st place than the next rank should NOT be 4 but instead should be 2 and so on. Is it possible?

# Rank

65 - 1

65 - 1

65 - 1

60 - 2

58 - 3

58 - 3

50 - 4

View 6 Replies
View Related
May 6, 2014

Formulas for ranking by column "PTS" and then by "DIF" if the PTS are equal for two or more teams...

View 5 Replies
View Related
Feb 9, 2009

The Excel function RANK allows one to automatically rank a number within a given range of numbers. Can anyone think of a way to do the same thing with words?

For example, imagine a list of 20 words in a column, with space to add ten more words (complete range B2 to B31, which include the last ten blank cells). If these were numbers, then in the previous column one could simply put in the RANK formula covering the full range, which would then auto update as you added more numbers to the spare ten cells of the range. Assuming that all the values being ranked are unique, and you would therefore get a unique rank number, to order them in rank order, one would simply have the list of possible rank values a column or two removed and VLOOKUP the ranks and their appropriate values.

But what if these numbers were WORDS? The first 20 words are FIXED and will not change, the next ten cells of the range could have any other words typed into them. There is no RANK function for words so how would you suggest that ranking by words is achieved, that does not require any intervention, and as new words were added to the spare ten cells in the range, then the rank would automatically update? This list of ranked words would then be VLOOKUP’d in the same fashion as above with the final list being used to fuel a drop down box, so the user would have an alphabetical listing of all the potential words in the range.

View 7 Replies
View Related
Apr 27, 2009

I need the folwing formula to ignore any 0 in column Q, and rank only values of 1 or greater.

=RANK(Q6,$Q$6:$Q$20,1)

View 3 Replies
View Related
Apr 30, 2009

I have a spreadsheet which we use to rank scores for our golf society.

At the start of our season it was decided that if anyone of the top 3 scores were tied we would separate them using a second criteria(back nine score). Any scores from position 4 down would still be tied.

From searching this forum i have managed to separate ties using the 'back nine' second criteria(using a hidden helper column) but only for all the ranking positions not just the top 3.

I have attached the file.

View 9 Replies
View Related
Jun 10, 2009

I need to rank sales with Nation, Area and Division. I figure how to calculate National rank, Area but have a bit of an issue with Divisional Rank. I need to re-calculate division rank by looking at Area rank and if this individual already .....

View 9 Replies
View Related
Jun 17, 2009

I've a problem with scoring using the rank formula.

Example: four players score, 28,17,39,6. The 39 score wins, gets 4 points, 28 = 3points, 17 = 2 points, 6 = 1 point.

The rank formula will work this out fine.

However if you get two scores tied, for example 28, 28, 39, 6, using rank the points score for each 28 returns 2 when I actually need it to be 2.5

(Thats 3points +2points / 2).

View 11 Replies
View Related
Oct 13, 2009

i was looking for some assistance in regards to a formula i have used for ranking. i have attached a sample spreadsheet of what i have done so far but struggling to work something so thot id try here.

(Sample WB) Currently i have managed to rank in accordance to column E but as i have alot of 0% i would like to be able to rank the 0% in accordance to the number of work done (column C).

For example i have 62 0% answers and Username Q has actioned 316 jobs with 0 Failures so should be ranked 1st as he has the highest number of jobs.

View 12 Replies
View Related
Feb 3, 2010

Is there an equivalent of =rank for non-numerical data? If not, is it possible to sort data (into another column if necessary) alphabetically without resorting to a macro?

View 12 Replies
View Related
Jan 21, 2006

I have a list of values that I need to rank in descending order. Usually

the RANK function is OK, but here is my problem. I need the list to use

'generous' rules. If there is a tie for 2nd, lets say, then I need the next

place to be 3rd, NOT 4th as the RANK function does.

eg

Scores Rank(correct) Rank(wrong)

14.04 1 1

12.665 2 2

12.665 2 2

11.725 3 4

0.000 4 5

View 9 Replies
View Related
Jan 29, 2010

A number of columns (B to U) contain names, details and scores of players. The A column contains the ranking, e.g. 1 to 100.

If a player (row) is deleted, that ranking number is also gone. Is there a way to make the A column always maintain the integrity and completeness of the numbering, e.g. if rank (row) 16 is deleted, the range of B17:U100 moves one row up and the former 17 is now 16? And if a new row is inserted, the ranking numbers after the insertion move up one row and a new number is added at the end?

2. There are 6 columns of scores, P to U. But out of these 6 scores, only the best 4 count for the ranking. The two worst (highest) scores are dropped. Is there a way to automate the process by selecting only the 4 best (lowest) numbers to be summed into the Total column (C), which in turn determines the rankings in the A column?*

And then can the whole range B2:U100 automatically be sorted according to the ranking in column (A)?

*It is possible that there are 3 or 4 bad scores, all with the same value. In that case only 2 could be discarded, and 1 or 2 would be summed into the ranking cell.

View 8 Replies
View Related
Jan 22, 2014

I have a data base of numbers Columns C to K with values from Row 2 to 3766. I want to rank each of these columns by value 1 to 100. However, some of the columns will assign 1 to the highest value and 100 to the lowest. In other columns it will be reverse, 1 is given to the lowest value and 100 to the highest. I can do a rank by percentile but that's not the scoring I'm looking for. Also don't know how to reverse assignment of high or low values. One further note, some of the cells are blank in which case I want to assign a neutral score of 50.

View 2 Replies
View Related
Feb 21, 2007

im doing a excell sheet for a fantasy league that i ahve created. I have a "total" cell to combine the total points for each player. I then used the "rank" formula to rank the highest points to that player. My question is...can i show like how many points behind everyone else is from who ever the "rank 1" is in?

Not sure if you can do 2 formuals in same cell or not. maybe i could have rank and how many points are behind in same cell

Example. lets say

Player points rank

Tim 5000 1

Jimmy 4000 2 -1000

Sara 3500 3 -1500

View 9 Replies
View Related
Feb 2, 2009

i have two col A and B

In col A name of the employee

In col B there production for the Month

I need in c col to rank them that according to there production for the month like 1,2,3, etc.

View 9 Replies
View Related
Feb 12, 2009

I need to do a final ranking for the employees those who have been already ranked on three different segments. And there will be also employees who are not qualified for the ranking in any one of the three different segments. So in the final ranking need to find the final qualified employees who are ranked in all the segments and we need to rank them, and the employees who are not qualified also has to be mentioned.

View 9 Replies
View Related
Jun 19, 2009

I am trying to create a time sheet:

I need to mearsure the time in atleast MM:SS:MS. (Minutes:Seconds:Milli Seconds). what is the best way to do this?

Then once that is sorted out I need to rank the times? e.g. lowest time 1st, second lowest 2nd and so on.

View 9 Replies
View Related
Oct 17, 2009

[data] ...

I need to be able to rank the numbers in the bottom row above based on the criteria in the yellow above. e.g.

Amoung all m's Phil = _____

Amoung all m's red's Phil = _____

Amoung all m's washer's Phil = _____

Amoung all m's washer's Team1 Phil = _____ etc etc

I began using a series on concatenations and found a nice sumproduct formula that worked well going up and down the column, but how can i do the same going across the columns.

View 9 Replies
View Related
Jul 28, 2006

I have a column of events,there are 7000 in my sheet.The scores for each contestant in the event are in col.2. In col 4 I have the rank printed for each contestant in each event in ascending order.I want to know how to print them in opposite order i.e highest in each event is ranked 1 etc into column 5.

View 9 Replies
View Related
Jun 23, 2007

I have a sheet where each month, dependent on results I rank the players, 1,2,3. For months not yet reached I want the cell to be blank, until the data is input, and then rank. I thought the following formula would work but the cells are showing all the players with a ranking of 1 until I input data.

=IF(ISNA(RANK(L23,$L$23:$L$25)),"",RANK(L23,$L$23:$L$25))

What am I doing wrong.

I don't want the players to show a ranking of 1, I want them cell to be blank until I input data to the other cells.

View 6 Replies
View Related
Jun 24, 2014

I have a workbook of data that I created a pivot table with. From there I need to rank each value of the NES, column C, within the corresponding Reporting Structure, column A. The table that I created will be update with data on a monthly basis and I don't want to have to redo the rankings every month and just have it refresh.

View 2 Replies
View Related
Apr 4, 2014

I am trying to rank via data in two columns to eliminate any tied rankings.

I need to use the data in columns "AW" & "AX" to compile the rankings.

The ranking needs to be calculated by the highest number in column "AW" being ranked #1 and the second highest ranked #2 etc etc......any ties as a result of this ranking need to be broken by the lowest number in column "AX", the lower number being the higher in value and thus being the higher ranked.

For example:

AW AX RANK

13 89 7

13 63 4

14 89 2

14 81 1

13 90 8

13 66 5

13 58 3

13 87 6

I have attached the spreadsheet that I am using.

View 7 Replies
View Related
Aug 19, 2014

here is an example of what my data looks like:

Book1.xls

the numerical data under the colors is the cost to make an item that color. i need a way to say that i have "n" of each color available to make and then a way to tell me which items to make which color for the lowest cost.

View 5 Replies
View Related
Jan 20, 2014

Running a NFL playoff pool and just actually used a previous post to finally correct my Rank formula which was great.

Basically I want to rank based on the POINTS column, and then based on the WINS column, and then based on two other things. The final tiebreakers are as follows.

1. Most points

2. Most wins

3. Picking the Super Bowl game correctly

4. Closest to the total points of the Super Bowl game (everyone has to pick what they think the total score of the game will be).

This is the formula used to Rank Points and Wins:

=RANK(M8,$M$8:$M$190)+SUMPRODUCT(--($M$8:$M$190=$M8),--(N8<$N$8:$N$190))

Can I add IF Statements to RANK formulas?

View 1 Replies
View Related
Sep 16, 2005

I am ranking set of numbers (# of days worked) in a 10 cell column but the numbers of persons I rank differs from day to day.

If I enter "0"(zero) on one of the cell, it ranks the whole ten cells. I have to enter Zero because that is the number of days worked.

How can I enter the value of zero that does not activate the other blank cells?

Here is my formula: =IF(ISNA(RANK(B5,$B$5:$B$14,1)),"",RANK(B5,$B$5:$B$14,1))

View 11 Replies
View Related
Sep 21, 2008

Hey there needs some help in creating a formula that will list the top 5 men & women scores onto my master sheet.

Work Sheets go from: P1 to P64

Person name are listed in Cell: C1

Person *** in Listed in Cell: T10

Scores listed in Cell: E3

View 6 Replies
View Related
Sep 30, 2008

Which formula do you recommend I can use to rank the below fields in yellow. I need to find the following:

1 - Which sales advisor (their name) has got the highest MPPI conversion and place it in cell K32.

2 - Which sales advisor (their name) has got the highest HC conversion and place it in cell K34.

3 - Which sales advisor (their name) has got the highest Highest Apps conversion and place it in cell K36.

View 5 Replies
View Related
Jan 16, 2009

I have a customer spread sheet that uses a “Ranking” to establish the next “Contact Date” based on the “Ranking” and the “Last Contact Date”.

An example is – a “Customer” with an A “Ranking” would be contacted 21 days after the “Last Contact Date”, with a B “Ranking”, 28 days after the “Last Contact Date” etc.

Is there any way that the “Next Contact Date” can be automatically up dated when the “Ranking” is changed and there are multiple rankings (A, B,C, D etc)

View 3 Replies
View Related
Jan 21, 2009

I have a table of data where i want to rank scores. I know the general formula for this would be =RANK(B2,$B$2:$B$7) as an example however the range of data is not next to each other and I have tried this formula =RANK(B2,B2,B4,B6) but this comes up with an error.

View 3 Replies
View Related
Dec 7, 2012

I have to make a hierarchy of my employees, taking in consideration the achievemnt of their target. I'll attach a sample to make it simple to understand. The are 2 classes of factors: Class I and Class II.

- In Class I (witch counts 75% in total results) there are:

- New deposit - count 75% from class I

- Insurance - count 10% from class I

- Investments fund - count 15% from class I

- In Class II (witch counts 25% in total results) trere are:

- Loan A - count 15% from class II, but the number have a weight of 40% in results, and volume 60%

- Loan B - count 75% from class II, but the number have a weight of 75%, while volume have 25%

- New clients - count 10% from class II

Now, I have to rank the employees taking in consideration the Deviation for all of the factors above (columns E, H, K, P, Q, V, W, Z).

View 3 Replies
View Related
Apr 7, 2008

i have a column of numbers. every so often in the column, there is the word 'start'. the amount of numbers between each 'start' varies, but i would like to put a 1 or a 0 in the column next to each number. i would like the second highest ranking number to get a 1 and all the others to get a 0.

if the first and second numbers between two starts are equal, then i would like all numbers between the 'start' to get a 0.

for example:

start

12 0

23 0

45 0

34 1

start

23 0

34 0

45 0

56 1

68 0

45 0

34 0

45 0

start

23 0

23 0

start

I posted on here months ago and someone very kindly did an array formula for me, trouble is some of the sheets are so long now that the array takes a few minutes to work it out and load up

View 10 Replies
View Related
Dec 9, 2008

I am wanting to rank mulitple groups of data and as i do this daily and the size of the groups are constantly changing there is a lot of manual moving cell sizes around to get what i need. i am hopeing there is a simpler way...

An example is say a horse race

info i have

race tab score

2 1 10

2 2 6

2 3 8

2 4 12

2 5 9

3 1 7

3 2 12

3 3 8

3 4 15

i have been using =rank($C2,$C$2:$C6) so i am trying to rank the scores...... ETC and i know this locks sells but if i take dollar signs away the next row doesnt rank A3 from A2:A6 and i also want to be able to drag down so the formula will then do race 3 etc....

is there a way i can do a lookup + rank so my formula ranks all numbers relating to race 2 then goes on to rank numbers relating to race 3 ?

I will also have to add the track name i guess as each individual track has race 1, 2, 3 etc

View 14 Replies
View Related
Nov 9, 2009

I need to add a field to a data set that ranks groups of data based on a date field. Items with the same "Type" & "Vintage" need to be assigned a number based on the date field starting with 1 for newest to x for oldest. I'm looking for a macro or formula that I can use to automatically assign these values. I attached an example. The highlighted green section was completed manually for reference. The real data set is thousands of lines.

View 8 Replies
View Related