How to return ranking based on students' score?
STUDENT SCORE Rank MAY 11255 4 JACK 5262 5 TIM 432525 1 STEVEN 352332 2 HUGO 32232 3 FERNANDO 5153 6 SANDY 4556 7 SARAH 2265 8
So, I have a table of scores from players for a category I have. It looks like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------Michael-----------Germany-------------364
2nd--------------Tim---------------USA-----------------364
4th---------------John--------------Norway-------------355
if one of the guys, for example, John were to improve his score to a 369, I'd change the score in Excel, then use the sort feature to put him above Michael and Tim. Only problem is, it would say his place is still 4th place, and it doesn't change to 2nd place.
I want it so the "Place" column automatically changes to the correct rank based on the score the person has. So if I changed John's 355 score to 369 and hit sort by A-Z, Excel would automatically change the "Place" he's in appropriately so it would look like this:
Place-------------Name-------------Country-------------Score
1st---------------Jim----------------USA--------------------376
2nd--------------John--------------Norway--------------369
3rd---------------Michael-----------Germany-------------364
3rd--------------Tim----------------USA-----------------364
I want to see if i can make a formula that works out how a player got a score based on a scoring system. See the attached file for more details. I want this to show that the player scored or didn't score, played a full match or was a substitute, was booked or wasn't booked and was sent off or wasn't sent off.
View 5 Replies View RelatedHow to formulate results of students in excel sheet.
From the attached picture (capture1.jpg) of the excel sheet - The rules of exams are:
1. if candidate scores 50% in all 4 papers, PASS and proceed to next year
2. if candidate scores 50% in 2 or 3 papers + borderline fail in 1 or 2 papers, VIVA VOCE exam for the borderline failed paper (Definition of borderline fail is candidate scoring 45 to 50 marks)
3. if candidate scores 50% in upto 2 papers + borderline fail in more than 2 papers, RE-EXAM
4. if candidate scores 45% in 2 or more papers, FAIL and repeat the year
The rules are in the attached picture flowchart.jpg
I am unsuccessful in writing a formula for such multiple criteria...
I have a workbook with two tabs: Results (containing exam results) and Percentile (containing percentile rankings based on scores). There are different rankings for different exams.
I need the percentile ranking returned (on the Results tab) based on the exam result for each student. e.g. for exam M111 a score of 36 would return a ranking of 95, a score of 18 would return a ranking of 35, etc. I've attached a sample workbook.
I am trying to distribute the total number of sessions (cell H1) across the groups in column A based on the score of each group (column D). The number of sessions a group receives cannot exceed the max (cell H2) or be less than the min (cell h3).
View 7 Replies View RelatedI have a spreadsheet which calculates the score for a particular item.
In cell M5, the answer should be 30 since the rating is 3 and weightage is 10 ( 3 X 10).
How do I automate this task via a formula ?
I have three columns in the spreadsheet.
sheet1
A BC
Santhosh314
Praveen224
Praveen012
Praveen37
Santhosh38
Praveen120
Praveen036
Santhosh19
In another sheet (sheet2) output come like this.
A B
Praveen 3/7
Santhosh 3/8
I'm trying to average out scores in a list based on when the score was given. For example:
Average (of three numbers below)
2/26/2010 99%
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)
I have a table that has multiple products and price points in it and I would like to add a field that ranks the products by price but for only against that product.
Product Price
Result needed is the Rank only by product based on Price
A
150
2
A
155
3
B
130
1
[Code] ........
i would like to rank one table based on two columns, for example world cup group table to be ranked based on points but if two teams have the same no of points, then use goals scored column to break tie between them.
View 9 Replies View RelatedRanking 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
I have a input selection, when i keyin XYZ, the data under column XYZ should be ranked in ascending order. If i select ABC then it should rank ABC data in ascending order.
XYZ
ABC
Input--->XYZ
XYZABC
15
38
26
42
Rank the column by selectionXYZABC
I have data in a number of columns in Excel which I want to sort on column B, then C, (Then D) however the formula I have entered is sorting based on column C below in ascending rather than descending order, how I can change this
=RANK(C2,$C$2:$C$4)+SUMPRODUCT(--($E$2:$E$4
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))
I need a formula that looks at the percentage values of 3 cells and the in the next row ranks them 1st,2nd or 3rd. So for example cells A1, A2 and A3 have 30%, 20% and 10% respectively, so in cells B1, B2, and B3 I need to return 1st, 2nd, 3rd. But next week it could be that A1, A2, and A3's values are 20%, 30% and 10% respectively, and so I'd need cells B1, B2 and B3 to return 2, 1, 3, and so on. Be cool if the rows could automatically re-arrange so that whatever is ranked 1 is always on top, 2 in the middle and 3 at the bottom, but not that important. Be also cool if the 1st ranked row, chnaged colour or flashed or something, again not important though.
View 4 Replies View RelatedHow to do a ranking for productivity based on goals in six categories for my employees. I want to be able to rank them on these key categories and rank them based on the best average. For instance:
Employee #001 Ranked 1st in Sales per Hour, 3rd in Avg Transaction Amount, 2nd in Multiple Sales, 5th in Accessory Percentage
Employee #002 Ranked 2nd in Sales per Hour, 1st in Avg Transaction Amount, 1st in Multiple Sales, 4th in Accessory Percentage
I want to factor against the goals to determine each individual rank for each category and then rank based on the combination of them all.
I haven't gotten to all the additional conditional formatting but recall how to do this with ease, but the sheet in its basic format is present in the attachment below.
Employee Productivity.xls‎
I would like to change the ranking between two rows based on some criteria with macro.
View 6 Replies View RelatedI have a list of research material based off postcodes, travel time in those areas and what I want to do is rank the the travel times in those ares within a third column.
What I have so far in design terms is this:
Origin Base
Time
Time Ranking
AB10
00:10:00
AB10
00:12:00
[Code]...
So basically I'm trying to have column c look at a and check for the lowest value in b against all other rows that have the same constant value e.g. AB10 in column a. Then to distinguish them somehow for example marking the first '1'. Also then to be able to do it for second and thrid ranking value.
What do people think? Is this doable? I think maybe a min if array but keep tripping on the fact that the ranges are all locked in a single column and inconstant sizes.
The data comes in this format and is quite large so manual editing would be a last resort.
I have a speadsheet that has a table containing scores of audits, in the last 3 columns of the tabe I have the two totals (columns AC and AD)
In Column "AF" I wish to show where they will rank based on the results, first I need to rank via the result of column AD and if that is the same as another result use column AC to determine where they rank.
I'm looking to populate tables for specific tasks that my site performs and compare their performance against the other top sites in the company. I need to pull the site # and their performance based on the task, ranking them from first to last.
View 5 Replies View RelatedI 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.
I am trying to calculate players ranking based on their weight categories and attempts total, e.g. Category (50kg, 69kg etc..) Rank should be calculated automatically the highest total value of cat. 50kg is 1st second highest is 2nd etc. However, if tow players or more within the same Category have scored same point ranking will be calculated based on Body Weight less comes first and if both have similar weight calculation will be based on Start # first player comes first etc..
Table:
Start #
Name
Body Weight
Team
Category
Total Point
Rank
1
Player #1
49.2
Team #1
50kg
75
A1 has column heading Name.
B1 has column heading Credits.
C1 has column heading Rank
A2 has student's name and B2 has his Credit value
Row 3,4,5 are blank.
A6 has a student's name and B6 has his credit value.
Row 7,8,9,10,11 are blank.
A12 has a student's name and b12 has his credit value. So on so forth.
All I need to do is rank those students in C2, C6, B12 so on so forth.
Its taking a lot of time and I am not getting any result. I tried reading about rank but it tells about asc/desc order, which I dont understand how it fits in above example. I just need rank of a student at a particular cell.
I have a spreadsheet with my students that needs to find the date a person will be 18. based on their date of birth. I have =B27+(365.25*18) where their DOB is in B27. I have had several instances where it is correct and several instances where it is a day off.
View 4 Replies View Relatedi have a spreadsheet with a list of students in it and next to it i have a column with their current score and the letter equivilant (A*-U)
so say
joe bloggs 615 B
Joe bog 750 A*
etc
is there a way if I list the grading boundaries for it to work out how many marks are required for the student to reach the next grade?
boundaries are
A* 728
A 669
B 610
C 554
D 467
E 383
F 298
G 214
U under 213
I have a problem with counting the number of students assigned to each professor and determining the number of students who have passing grade. Let say that the passing grade is 80.
View 9 Replies View RelatedI have a dataset of students' grades (see sample below) which I need to compute the aggregates for.
Blanks indicate that the student did not take the subject. Aggregate is computed based on 6 relevant subjects, lowest is best.
Rules for computing aggregates:
1st subject: EL or HMT
2nd subject: H1 or H2
3rd subject: M1, M2, S1 or S2
4th subject: H1, H2, M1, M2, S1 or S2 (but not previously used)
5th & 6th subjects: Any other 2 not previously used
I'm guessing this is too complicated to be done in a function, but have no idea how to implement this in VBA.
I am working on an excel master marksheet which will be distributed to many teachers to fill in their students marks. How can I enter an average function that can work out the mean for those different classes. For example I'd like the main average function to be like this =average(d5:d50) but if a teacher has thirty students in his list the mean will be only limited to the marks of those thirty students. Same thing I want for the mode function and the median function.
View 7 Replies View Related