# 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

View 9 Replies
ADVERTISEMENT
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
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,"

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
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
May 20, 2007

The team score is made of of 3 scores, the best from each age group. So the issue is to find the best (=lowest) for each club and for each age group. Add them & rank. Sometimes there may not be 3 scores to add up ( if they didn't have a team member in all three age groups or if their rider was eliminated. I'm easy about how to show who the team members are, could be as I've shown with the rider numbers in a cell or could be by "bolding" the score or anything else thats easy....

View 9 Replies
View Related
Aug 13, 2014

I have attached an Excel 2010 spreadsheet to show the question.

Essentially, I need to have a formula to indicate the numeric rank of each grade for each student.

I could sort the columns by NAME and SCORE.

However, I would prefer to have a formula that ranks each student's subject grades - some students have multiple numbers that are the same.

View 11 Replies
View Related
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] .......

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

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

View 4 Replies
View Related
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).

View 4 Replies
View Related
May 24, 2007

I have a grid set up to add scores of which there are none. The boxes each have a formula, ie: =IF(FIXTURES!D1="","",FIXTURES!D1 ) this produces a blank square until a score is registered. At the end of each column, down and across there is a formula for adding up the scores. Downwards is: = SUM(C2:C25), this works fine.

Across however is: =B3+D3+F3+H3+J3+L3+N3+P3+R3+T3+V3+X3+Z3+AB3+AD3+AF3+AH3+AJ3+AL3+AN3+AP3+AR3+AT3+AV3 This doesn't work, just gives the answer TRUE. It did work before the original formula was added.

View 4 Replies
View Related
Apr 11, 2008

I teach history to four different groups of students that, in my spreasheet, I have labeled "1", "2", "3", and "4." The number of the group to which each student belongs is listed in CK2:CK100

Each quarter, I give all students a test and list the corresponding score next to each student's name. The scores are listed in CM2:CM100.

The name of each student appears in CL2:CL100.

I am looking for the formulas to extract the highest five scores in each group starting at CS2. So CS2 would contain the value of the highest score obtained by a student in group 1, CS3 the second highest score in that group and so on through group 4.

View 9 Replies
View Related
Oct 10, 2008

I'm basically creating a scoring worksheet, where there are a list of questions. Each question has a weighting as some are more impotant than others. Each question also has a range of answeres as some may not be clear yes/no answers. Also not all questions apply to all assessments so some may be left blank.

I basically need a formula which does the following to produce a total score:

[Total awarded score] / [Total available score of questions scored]

which equals

[(score <> "" / range)*weighting] / [if score <> "" sum weightings]

I've attached an example document where I think I'm getting close, just not sure if its possible.

View 4 Replies
View Related
Apr 20, 2007

I have a formula that looks up a chart showing scores referenced against each other as shown below. (Scaled down version)

******** ******************** ************************************************************************>Microsoft Excel - Table Template.xls___Running: 11.0 : OS = Windows Windows 2000 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1B1C1D1E1=

ABCDE12006-2007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs- 3Carleton Jnrs12-3-4-0 4Empire Colts - 5Hemsworth Terriers -Results

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The problem is when the score is i.e. 12-3 the formula is only looking at 2-3. Hence this actually then shows as a defeat. The formula causing this problen is an array and shown as

=SUM(IF(NOT(ISERROR(LEFT(resultsHome,FIND("-",resultsHome)-1))),

--(LEFT(Results!$B3:$K3,FIND("-",Results!$B3:$K3)-1)>MID(Results!$B3:$K3,FIND("-",Results!$B3:$K3)+1,9))))

Question. How can the formula be changed to reflect scores like 12-3 to be shown as a home win.............

.

View 9 Replies
View Related
Jul 17, 2008

I have a list of team members and their scores for team building events. I need to create a list of the top ten scores (including any ties). The list will have to show the top ten scores as well as any additional names whose score ties with one of the top ten scores.

I tried large and also a pivot table using the advanced but cannot get it to show the top 10 unique scores as well as any duplicate scores.

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
Aug 17, 2009

There are scores in B1:B10 (10 scores). I want to take the top 8 scores. So I used this formula which works just fine.

=SUM(LARGE(B1:B10,1),LARGE(B1:B10,2),LARGE(B1:B10,3),LARGE(B1:B10,4),LARGE(B1:B10,5),LARGE(B1:B10,6) ,LARGE(B1:B10,7),LARGE(B1:B10,8))

But I'm trying to make the formula a bit more dynamic. Is there anyway to have a set of data in B1:B50 (50 scores will probably be the most) and take the top x scores? X will be set in a separate cell (lets just say A1)?

View 5 Replies
View Related
Feb 13, 2008

I have a raw score for each case. I need to map those raw scores to scale scores. For example, a raw score of 0 to 0.49 maps to a scale score of 120, a raw score of 0.5 to 0.99 maps to a scaled score of 110, etc. There are a total of 13 possible scaled scores (120, 110, 100, ... 20, 10, 0). Is there a way to do this in Excel2003? (Note, if anyone on the list is 'bilingual' (Excel and SPSS), I'm looking for the Excel equivalent of the SPSS command RECODE raw (lo thur .49=120), (.5 thru .99=110), etc. INTO scale.

View 2 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
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
May 9, 2007

Would someone be so kind as to look at the attcahed sheet. There are boxes with data entered. Depending on the achievement the 3 parties are automatically ranked 1,2 or 3 as you can see.

All except for cells j23:j25, although I've used exactly the same formula as the other boxes, the ranking doesn't seem to work for some reason I cannoot fathom.

I know there is an issue with merging, but all the others work and are merged (to get them to work I created the formula before merging).

View 9 Replies
View Related
Oct 25, 2009

I have 6 categories that need to be ranked 1-6 in order of highest number of occurances. My ranking formula is showing 1-7, missing number 3. I have attached a sample worksheet further showing what I am trying to explain. I need cell A3 to show a ranking of "3". Currently it shows "4". What am I doing wrong??

View 4 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
Oct 15, 2009

I am attempting to upgrade a scoring system in excel 2003 which I use to rank wins in a sports results grid and i have to amend a formula based on a new version. The current formula in the attached part-completed worksheet Draw9 of 1 to 9, which I wish to amend, is in cells AM42:53 one of which is:

=IF(AL44="","",RANK(AL44,AL44:AL53)&CHOOSE(AND(RANK(AL44,AL44:AL53)<>{11,12,13})*MIN(4,MOD(RANK(AL44 ,AL44:AL53),10))+1,"th","st","nd","rd","th"))

I have now added an extra column of data in cell AN44:53 under “LSD” and I would like to use this to improve the accuracy of the original ranking. In other words I wish to Rank teams using the “Wins” in column AL42:53 plus the “LSD” data in columns AN44:53 and this will ensure that where teams are tied on the same number of wins then the numbers in the “LSD” column will enable a clear ranking i.e. say 3 teams on 3 wins ranked 6th = will now be ranked 6th,7th & 8th.

View 4 Replies
View Related
Mar 9, 2012

here is some code that I have,

Dim zima As Range

Dim Col As Long

On Error Resume Next

Set zima = Application.InputBox("Select column to paste to", Type:=8)

On Error GoTo 0

If zima Is Nothing Then Exit Sub

[code]....

Where in the formula C[-7], I need this to line up with the column that I selected via the InputBox. If I select column L then the -7 needs to change to +2. OR would there be a better way to write the formula in VBA?

View 7 Replies
View Related
Jul 29, 2008

I recently made a formula of net sales contribution (sales per product category/total sales). For weeks 1.2008 to 30.2008. But I have several product categories. I would like near the value of the formula (not in another cell!) to insert a rank of these contributions. I d love to have the rank inside a parenthesis() near the percentage.

Here is the format

NET SALES CONTRIBUTION PER PRODUCT HIERARCHY 239.200740.200741.2007

COM RETAIL MEDIA0,03%0,73%0,22%

DTM STORAGE MEDIA0,00%0,06%0,00%

Of course I dont know how to do it.

View 9 Replies
View Related
Mar 31, 2009

i rank some thing

stock 25

A B c D

rank style qty

1 st1 10 for d i need go to rank 1 first then qty 10

2 st2 5 then 2 then 3 till 25 (stock ) after it comes 0

5 st3 4

4 st4 3

3 st5 6

it can posible with rank and min formula

View 9 Replies
View Related
Jun 19, 2006

I want to find the rank of a year that falls between the start date and end date. For e.g.

Start date : 1/1/2005

End date : 10/31/2014

Target Date: 12/20/2009

Using year(start date)-year(end date), I get the number of years but how to evaluate the answer in a cell to get the result = 5 (my desired answer as 2005 through 2009 = 5 years).

View 2 Replies
View Related
Jan 6, 2008

I am using the following formula to rank a list of data:

=SUMPRODUCT(--($A$2:$A$140=A2),--($B$2:$B$140=B2),--(M2

View 9 Replies
View Related
Jan 8, 2008

I would like to rank the values without the Zero's. Something like the example below. Where the digits are being ranked and the negatives without ranking the 0's....

View 9 Replies
View Related
Apr 3, 2013

a formula to rank a set of data ? The smallest data will be rank 1 and the highest will have the last rank. this range of data has some spaces in between, that needs to be ignored but should not be deleted. I have attached a spreadsheet stating the case.

View 4 Replies
View Related
Oct 7, 2009

I have a rank formula that is seemingly erroring out on certain cells - indiscriminately. I have tried and tried to figure this out for myself but this is beyond my knowledge.

Only on some cells is the rank not working and returning a "-" (the error result).

I want the rank to use absolute numbers, so that it will rank according to variance or percentage (regardless of +/-). I am also using another cell (R1) to tell the rank what column to use.

As you can see there are a few entries, both + & -, that are not being included in the rank - for reasons beyond me.

View 8 Replies
View Related
Aug 14, 2014

I have attached an excel 2010 spreadsheet to show the issue.

I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).

However, the ranking figure that get's generated, appears to be counting the cells or something like that.

Ranking Example.xlsxâ€Ž

View 10 Replies
View Related
Mar 12, 2014

I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:

Screenshot 2014-03-12 21.56.18.jpg

So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.

View 3 Replies
View Related
Sep 17, 2009

i am trying to work out how to use the rank formula to rank numbers in column B and keep them in unison with Column A.

So Column A has say 5 1's with column B having different scores then continuing under 1 in A is 2 and so on is there a way to continue the ranking formula without manually changing the cell ranges?

so =rank(B1,$B$1:$B$7,1) but can i do that if A =1 and then A=2 etc ?

so if A=1,rank(B1,$B$1:$B$7,1)

View 7 Replies
View Related
Nov 9, 2007

I'm trying to calculate the sum of rank vlaues in an array formula (required for a Mann-Whitney U-test calc). For example, I have the results of a survey quesiton (1-5 rating) with particpant groups of Sales, Marketing & Other. I want to sum the Ranks of the data points that come from Sales or Marketing (but not Other). The added complexity is in the need to add in the Rank correction value to account for ties.

The conditional arrays are the tricky part. I'm very close, but the array formula is still including the Other values. If I delete those data points the formula works great. For those with strong stomachs, I've copied my latest formula below.

$C$% = "Sales" and $F$5 = "Marketing"...

={SUM(IF('Survey Data'!$D$3:$D$30=$C$5,RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30))+(($M115+$N115)+1-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),0)-RANK('Survey Data'!$W$3:$W$30,IF(OR('Survey Data'!$D$3:$D$30=$C$5,'Survey Data'!$D$3:$D$30=$F$5),'Survey Data'!$W$3:$W$30),1))/2))}

View 9 Replies
View Related
Jun 4, 2009

I am currently working on a football spreadsheet ranking players based on their fitness performance scores. For example, there are 14 persons on Offensive Line. I need all their test scores ranked out of 14. However, if there is a blank, I still need the test scores to be ranked out of 14.

As of right now, I have cheated, and inputted a random number to bypass the blank:

=IF($G12>10, 0,RANK($G12, $G$12:$G$25,0))

But, this now affects my average of the fitness testing scores for that position. This could be avoided if I could find a way around my first problem.

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

View 9 Replies
View Related
Oct 13, 2011

I need to rank the top 3 names for each location based on gender (see sample data table below).

My spreadhseet has been setup using dynamic named ranges ("Size", "Name", "Gender", "Location" and "Score") using the formula "Size"=MATCH("zzzzz",'Sheet1'!$A:$A) and then "Name" =OFFSET('Sheet1'!$A$2,0,0,Size), and so on for "Gender", "Location" and "Score".

Is there a way to design an array rank formula using the dynamic named ranges to return the name associated with the highest score for "M"s in location "N"? I want to be able to edit the formula so I can chose which rank to display (e.g. 3rd, 7th, etc).

I've been playing with the following but can't get it to work

{=RANK(IF(ISNUMBER(MATCH(Gener,{"M"},0)),IF(Location{"N"},0),Score)}

NameGenderLocationScoreTimMN89

RebeccaFN45DanaFN85TerryMN15

RachelFN78BruceMN31BryanMS15

DonnaFS80JakeMS67MelissaFS66AdamMS48KateFS90

View 3 Replies
View Related