Ranking :: What Formula Would Be Used To Rank Scores
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Ranking Scores Formula
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 Replies!
View Related
Ranking Error :: Formula To Rank Employees
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 Replies!
View Related
Ranking Teams Scores
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 Replies!
View Related
Ranking Top 5 Men & Women Scores
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 Replies!
View Related
Multiple Ranking: Rank The Values In Column B And Then Rank The Values In Column C
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 Replies!
View Related
Find Top X Scores Out Of 50 Scores?
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 Replies!
View Related
Map Scores To Scaled Scores
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 Replies!
View Related
Formula To Extract Test Scores
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 Replies!
View Related
Multiple Plus Formula: Add Scores Of Which There Are None
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 Replies!
View Related
Formula: Looks Up A Chart Showing Scores Referenced Against Each Other
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= ABCDE120062007Altofts JnrsCarleton JnrsEmpire ColtsHemsworth Terriers2Altofts Jnrs 3Carleton Jnrs12340 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. 123 the formula is only looking at 23. 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 123 to be shown as a home win............. .
View Replies!
View Related
Complex Array Formula  Scores And Weightings
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 Replies!
View Related
Formula To Show Top Scores  Including Ties
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 Replies!
View Related
Ranking Formula ...
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 recalculate division rank by looking at Area rank and if this individual already .....
View Replies!
View Related
Formula: Automatically Ranking
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 Replies!
View Related
Moving Ranking Formula
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 Replies!
View Related
Formula: Ranking The Data
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 Replies!
View Related
Ranking Formula Not Working Properly
I have 6 categories that need to be ranked 16 in order of highest number of occurances. My ranking formula is showing 17, 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 Replies!
View Related
Amend Ranking Formula For Sports Results
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 partcompleted 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 Replies!
View Related
Rank A Value Next To A Formula
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 Replies!
View Related
Rank Formula
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 Replies!
View Related
Rank And Min Formula
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 Replies!
View Related
Rank Formula Not Working Properly
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 Replies!
View Related
Identifying Varying Data And Rank Formula
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 Replies!
View Related
Rank Calculation (with Correction) In Array Formula
I'm trying to calculate the sum of rank vlaues in an array formula (required for a MannWhitney Utest calc). For example, I have the results of a survey quesiton (15 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)+1RANK('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 Replies!
View Related
Rank Formula Excluding Blank Cells
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 Replies!
View Related
Formula To Rank Text Objects Based On Numbers, Interesting Read
I'm attempting to create a formula that will find the name associated with a value, and return that name on the same column as that value in a later equation. OK OK to illustrate it a little better: There are three people: Bill, Ted, and Andy. Each one is ranked in Points, so my table looks like this: A B 1 Bill 10 2 Ted 20 3 Andy 30 Later on, I want to rank the individuals based on their score, using a formula. Right now, I can rank the scores based using LARGE(B1:B3,1), then LARGE(B1:B3,2), and lastly LARGE(B1:B3,3). That ranks the numbers in descending columns. However, I want the information to automatically populate the name associated with that particular point total. So, I want the system to know that B1 is Bill's score, and rank it, in descending order, later on in the spreadsheet, with Bill's name.
View Replies!
View Related
Top 5 Scores
Is it possible to make a top 5 scores section? Ive been reading up on Arrays and how to use Arrays etc so i believe that it could be done with an array but i could be complete wrong. what i have is players names in A4 and there averages in H4. i have 22 players listed but i want to take the top 5 scores from h4 and display them in a different section with there as so: NAME(A4): AVERAGE(h4) repeated 5 times and updates Automatically.
View Replies!
View Related
Look Up Highest Scores
What is the best way to look up and return the 10 best performers out of a list of say, 40. I have columns representing people's names, their school's name, their age and their total score. The following formula is to add up the 4 highest scores for each school: SUM(LARGE(($A$2:$A$499=Y2)*($C$2:$C$499),{1,2,3,4})) (entered as an array) Now I want to display the 10 best performing schools (who will be determined by calculating their 4 best scores).
View Replies!
View Related
Sheets Contain Different Types Of Scores
some of the functions in this file, and the problem is that I have one bug I can't fix. There are four sheets. The first two sheets contain different types of scores. The fourth sheet ranks each of the different types of scores on both first sheets. The third sheet reports out on the bottom five scores in each category. If one of the scores is missing, the whole thing gets screwed up. I have attached the file and removed a some of the scores to illustrate.
View Replies!
View Related
Working Out Scores In Table
I am having a problem working out scores and games played, won, drawn, lost, etc. Basically I have two sheets one where I have the football table stored, another where I input the scores. I have managed to work out the points total automatically when the scores are inputted but I was wondering if anyone would know how I can work out a teams games played, won, drawn, lost and have that automatically updated in the table when the score has been inputted in the other sheet.
View Replies!
View Related
Looking Up Text And Returning Scores
 item1, item2, item4  Elsewhere I have a set of points for these like this:  item1  4   item2  2   item3  3   item4  1  (the 's represent cell borders, if it isn't obvious) What I want is a formula that can grab the text, then produce a total score. So, for my example, the result would be 7, totalling the scores for item1, item2, and item4. I know that the score lookup is trivial (just a VLOOKUP), but I can't figure out how to do the scan for the data from a single cell. I want the items to remain in a single cell if possible.
View Replies!
View Related
High Scores And Tiebreaker
This is for a forestry contest. Students compete in 6 different categories (columns B through G). Teams can have up to four members and the top three member scores are taken to determine a team score. Teams are then ranked. Here is where I need help. I would like to be able to find the high individual score for each category in the contest. (sawlog, pulpwood, tree ID, etc) If there is a tie in the category it is broken using the category in the column to the right. Example – sawlog 1st tie breaker is pulpwood, the 2nd tiebreaker is thinning. The last column, Compass, uses sawlog as 1st tie breaker and pulpwood as 2nd tiebreaker. Example Will on the RHS team had a score of 112 in Tree ID and a score of 48 in thinning. Michael on the WHS team also had a 112 score in Tree ID and a score of 44 in Thinning. So Will is the high individual in that event – How can I create a formula that will bring the winners name down to row 6671 and also bring the score down to row 6671 to show who won each event? example spreadsheet attached.
View Replies!
View Related
Average Of Lowest 3 Of 5 Scores 0
Needed for a golf league where the average of the best three of five games is needed  so, low score is better but score must be greater than zero. Each row is a player, column A is the players name, BF 5 different games and the average will be in column G. I tried this formula but if someone didn't play at least 3 games SMALL returns the #NUM! error value. If I enter 0 for games not played then SMALL picks up the 0 as the lowest score. =(SMALL(B3:F3,1)+SMALL(B3:F3,2)+SMALL(B3:F3,3))/3. How to nest the different functions (if>0, small, and avg would be good too)
View Replies!
View Related
Define Number Of Scores
The following formula is to add up the 4 highest scores for members belonging to each group: = SUM(LARGE(($A$2:$A$499=Y2)*($C$2:$C$499),{1,2,3,4})) (entered as an array) I only want to return results for groups that have four or more members. I am not interested in groups that say only have 3 members. How can I modify this formula to do this?
View Replies!
View Related
Keep Tally Of Team Scores
I need to create a sheet probably involving macros where I enter a numerical value into a cell (say B5) I want the sheet to assign a score to the value I enter and then add the score it generates to a table of values. Once ths is done I need it to automatically clear the original value I entered ready for me to enter a new value  rinse repeat.. e.g. into B5 I enter 15  on the right in the results table there are headings v w x y and z say 15 scores one point for w and one point for z I want the sheet to add 0 to the totals under v,x & y and add 1 to the totals under w and z. Then I want the sheet to clear cell B5 so I can enter a new value without manually deleting the previous entry. If I can also copy the value I enter into a "history" which simply lists all the values I enter as I go along so much thebetter.
View Replies!
View Related
Statistics From Golf Scores
I am creating a workbook to manage golfer scores, teams, winnings, handicap, ect. I am having trouble with the statistics sheet. I Need A Sub To: 1. Column "B" , take the average of the lowest 5 numbers in columns "AB" though "AU" 2. Column "C" , take the average of the lowest 10 numbers in columns "G" through "Z" divided by 0.96 3. Column "D" , take the average of the lowest 10 numbers in columns "AW" through "BP" this needs to happen for each row where there is a name. (names added daily) I have included a similar sheet as the one i am working with along with some command buttons typically not included. Should show some of the problems I am having.
View Replies!
View Related
Golf Scores: Average X Lowest Of Last X
I would like to set up a Excel spreadsheet for golf handicaps where it will use the 4 lowest scores of the last 5 entries. It must be able to work even if there are missed entries (someone didn't show up/blanks ignored). I would like it to work if there are only three scores used at the beginning of the season, but when there are 5 or more scores, use only the 4 lowest scores out of the last 5. I want it to average these scores. The scores would start in column d. The following is an example of scores with an underscore being a blank: Golfer 1 45,50,42,43,46,37,45,45,46,44 Golfer 2 41,43,42,_,41,_,_,47,42,39 Golfer 3 44,43,45,54,_,45,43,40,42,_
View Replies!
View Related
Plugin Scores To A Results Sheet
I have two spreadsheets. One is a listing of football scores from the previous week. The other is the schedule for that week. I would like to be able to populate one from the other. Even if someone could point me in the direction of which functions to use that would be helpful. Based on the data in the results spreadsheet in columns M, N and P ... I need to look in the scores spreadsheet and compare to columns A, F, H and I. I would need to retrieve columns G and J for their scores. I've been looking at SEARCH, LEFT AND VLOOKUP so far!
View Replies!
View Related
Stop Excel From Converting Football Scores Into Dates
http://www.excelforum.com/excelgene...cognition.html and im copying and pasting data from a website ( football scores ) and when i get what should be 11 it returns 01jan and this i dont want i have tried formatting all cells to text beforehand but that makes no difference and i cant put an apostrophe before each one as that would take ages wondered if anyone could work out some syntax to use as a macro button? claymation had a go but it doesnt work.
View Replies!
View Related
Rookie Convert The Scores To A Simple Number Value Additionally
I am doing a club handicap type of scoring awards for a waterski club the info I get off the internet from each event is like this example below. I need to convert the scores to a simple number value additionally if possible choose only the highest score since I only use one score from each event. some events have 2 or 3 scores per skier. Then I have a vlookup table setup and functioning to bring the handicap to my main sheet. John M44.00/1200 3.50/1200 2.00/1200 MARK M41.50/1125 2.00/1125 2.50/1200 DAVID M43.50/1425 1.50/1425 .50/2300 KEN M42.50/1300 5.50/1425 2.00/1425 basically if you replace the / with @ you have your score of 4@12 meters this being 6 more points than say 4@13 since the 12 meter rope is shorter and more difficult to get around the bouy. So can or should I use a replace "/" with"@". but then what I ultimately need is like 4/12 need to return a number like 96 and 4/13 should give me 90. Would a vlookup be easiest? Does excel have a function like this? Additionally it would be great if it can choose the highest score and bring only that into the main sheet. is these event do have non club members in then as well, can I using my existing vlookup sheet return only those names?
View Replies!
View Related
Displaying Message Box For Top Scores In Football Pool
I have a football pool I am doing with my family. I would like a macro that displays a message box that tells me the leaders of the pool using the grand total number. So in my attachment, the message box would say something like: Sue is in first place with 12 points, Bob and Dave are in second place with 9 points, Larry is in third place with 3 points It doesn't need to be exactly like that, but you get the gist of what I am looking for. The catch here is that the grand total row changes each week as I add games in, so the row moves down every week. I need the macro to stay with the grand total row from week to week.
View Replies!
View Related
Ranking  Ties
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 Replies!
View Related
