i have a list of names in column A and a corresponding score for each name in column S.
on a stats sheet B10:B15 was trying to make a top 5 list using =LARGE(Week1!S15:S46,1) ... with Large i am able to get my top 5 from the week1 sheet but how can i assign the corresponding top5 persons from column A15:A45 in week1 to the stats sheet in column A10:A15
I have been struggling to convert an array of weekly data rankings into monthly rankings using the closest date to month end and cant seem to find a way. see below
spx indexspgsci Indexief us equityspx indexspgsci Indexief us equity
24/01/201212431/12/2011???20/01/201213430/11/201113/01/201213431/10/2011
6/01/201213430/12/201114323/12/201114316/12/20111429/12/20111322/12/201113225/11/2011132
How do I convert them to a monthly rank18/11/2011132using the closest to month end date
11/11/20111324/11/201113228/10/201113221/10/201113214/10/2011132
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 RelatedI have more issues with this spreadsheet, which is beginning to drive me bonkers. Its a rank issue, with a few problems, questions:
Looking at the second table on the page, I want to rank the data for all colums except for column I in a descending fashion. IE - Highest to lowest. But column I needs to be ranked lowest to highest. I think I did this correctly, but I could be wrong.
Is it possable for the rankings to record equals? IE, the TO column has that, but in REB there should be another tie (9th and 10th) but there is not.
Further to this, the third table should have I28, and E35 listed as Ties. How do I do this for the entire table?
I have ranked the times of races for 30 participants. The results are in a
column. I want to display the points given in the next column. The only
points given are for 1st through 6th place. 1st=6pts, 2nd=5pts, 3rd=4pts,
4th=3pts, 5th=2pts, 6th=1pt, 7th place through 30th place =0. Times,
therefore ranks, will change with each race, so this information needs to be
in every cell in that column.
NAME TIME RANK PTS
Joe 12.05 2 5
Mary 13.00 3 4
Sue 10.57 1 6
Sam 15.04 8 0
I figured out the rank formula, but I can't figure out how to assign the
points.
I have a worksheet set up as a draw sheet for a tournament. I have a set of rankings showing all points collated from last tournament. What i want to do is set up a formula/macro so that a cell containing a player eg. the winner will be found on the ranking list and the points won will be added to the next cell on the ranking list.
View 14 Replies View RelatedSuppose I have a table with a collection of "Top Ten" rankings for something. For example, suppose I have rankings from five "sources" for the Top Ten Ethnic Cooking. For simplicity, I'll show the top 5 here:
Rank
Source A
Source B
Source C
[Code]....
I'd like a way to calculate some sort of average ranking. I have two problems: (1) I don't know what the formula should be and (2) I don't know how to implement it in Excel.
How I can use a formula for inputting a persons position in a race for the scores appear automatically in the adjacent column. E.g 1st place 10 pts, 2nd 8pts etc?
View 3 Replies View RelatedNeed to rank by score1 in descending order and Serial # in ascending order. My biggest problem is to get the rank number to be consecutive. I have listed the rank I would like to see.
Score1
Serial #
Rank
90
146168
1
[Code] .....
I am building a country ranking model which ranks several different macroeconomic indicators and applies a weighting to each of the indicators. I have an overall ranking column which is an average of all the ranks. The problem is whenever I change any of the individual indicator's weighting it obviously affects the country's overall ranking. Is there a way that the model can automatically "re-sort" the overall rankings without me having to manually click on the sort by smallest to largest filter each time?
PS I'm using Excel 2007.
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).
im using spreadsheet works which seems to be very similar to excel. i am making a table full of numbers and i want to count how many times the number 1 appears and for that amount to be displayed.
View 11 Replies View Relatedi was assigned to make a computation for our individual scores using this:
EMPLOYEES A-N
inputs:
SURVEY(0-100)
LH(lost hour)
AHT(average handling time)
compute for:
SCORE
RANK
the SCORE has a total of 100%
so to get the SCORE, each input gets a percentage: SURVEY=40% LH=30% AHT=30% then added.
but the thing is LH and AHT has an equivalent.
LH
0 to 1=100
1.1 to 2=90
2.1 to 3=80
3.1 to 4=70
4.1 to 5=60
5.1 above=20......
eXAMPLE:
EMPLOYEE A
SURVEY(0-100) = 33.00
LH(lost hour) = 12.00
AHT(average handling time) = 14.20
SCORE = CODE
RANK = CODE
now for the rank i just use the code =RANK(SCORE A, SCORE A:SCORE N)
im lost on how to compute the SCORE though coz i can just use =((SURVEY*40%)+(LH*30%)+(AHT*30%))
but the one that needs to be on the LH and AHT are the equivalents and not the inputs.
and there is 1st 2nd and 3rd place. the name of the top 3 employees should be shown under the table.
I am currently working on a score sheet with list of question.
I have:
On Sheet 1 (will be hidden later)
List of 14 questions (e.g. question 1 = 6 possible answers, for answer 1 is score -2, for answer 2 is score 7... etc).
In column C are possible answers, in column D are the score values.
On Sheet 2 (will be visible for participants)
Drop-down list based on Sheet 1 questions (Sheet 1; Column C)
I have set Cell link for each question so when you choose the 3rd answer, there is number 3, but I need to set different values based on Sheet 1, could be that 3rd answer is score 12. I do not know which function to use (I tried IF, but to me it seems too long and difficoult).
I've seen a few bowling formulas within mr. excel but nothing in code. so i tried it and the only problem i have is when a player strikes.....then the code should add the next two balls thrown..... it works if the next two are strikes but not if there is no 'mark'. So basically its not adding the second ball. The code looks right unless I'm just missing something. heres the code....
View 9 Replies View RelatedIs there a formula-based way to do the following?
Sheet 1 has last name, first name, sales, and region in columns A through D, respectively.
Sheet 2 is a rankings sheet of sheet 1. Sheet 2 contains 2 headings: Top salespersons for region A, and top salespersons for region B. I'd like for the top 2 salespersons' (in each region) names & sales amounts to appear under their appropriate region in sheet 2. I just don't know which formula to start with that would be able to look at sheet 1, sort the top salespeople in each region, then display those in the right order on sheet 2.
I am using data validation for a drop down list e.g Good, Average, None. What i want is when a user selects for example "Good" i need a score to be automatically entered into another cell so that i can then do some calculations.
View 2 Replies View RelatedI have one excel file with three sheets marked 1,2 & 4
Sheet 1. is the place we add the description and the member number. When a member number is entered it gets the member name from sheet 4.
Sheet 2 is the page I need to change. Currently we can enter any score number in each box between 1 to 5. If we try to enter other numbers we get an error message.
I need to change the form so we can enter 1 to 10. Any other number needs to generate the error message.
Should be an easy fix for experts, but I'm not one of those...
I'm trying to create a "scoresheet" in Excel where a racer has a name and an ID number on the main tally worksheet, with columns relating to their scores for each race. Subsequent worksheets have results from each race (one race per sheet), with the individual referenced by their ID number.
How do I have the main tally sheet "find" that person's score for each event in separate columns so that it can add up their totals? I tried an IF formula to find each score in the subsequent sheets, but that didn't work.
Is there a simple formula to do this? I've been achieving the results by importing the worksheets into a Filemaker database (where the ID number "matches" and updates each score), but that's a pain, and I'd rather do it all in Excel.
If Average Score is:Then return:
Greater than 89A
From 80 to 89B
From 70 to 79C
From 60 to 69D
Less than 60F
Student's Average Score
90
52
75
88
82
53
46
72
99
100
42
53
69
79
84
=IF(A9:A23>89,"A",IF(A9:A23<=89,"B",IF(A9:A23<=79,"C",IF(A9:A23<=69,"D",IF(A9:A23<60,"F")))))
A9:A23 is student's score range
I am getting the correct answer for the first one only...all others are coming back incorrect. I have been working on this one problem for over 3 hours.
i need the formula in colums (l) it needs to find the agents name and the calulate the scores for that agents name
View 8 Replies View RelatedTrying to get a result if I missed 0 hours, I will get a score of 5, if I missed 1 -4 hours, I get a score of 4, if I missed 5 hours, I get a score of 3, etc.
0 Hours Missed5
1 - 4 Hours Missed4
4 - 8 Hours Missed3
8 - 12 Hours Missed2
12 -16 Hours Missed1
>16 Hours Missed0
I have tried a couple of things, but the formulas get way to messy & start to lose cell references.
I'm setting up a scoring system for my rounds of golf. I have four people competing, and i want to be able to put up a page where i can display the best golfer. I have the scores and i want a function that display f.ex "Thomas" if cell d28 is the lowest number of four cells.
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.
Column A has an assement score which reads 1,2 or 3 Columb B should have a value statement based on what it sees in column A
For example
A1 reads "1" ( which is a pass ) , then B1 should auto-populate with the word Pass.
if A1 reads "2" ( neutral ) B1 should read "neutral"
if A1 reads "3" ( fail ) B1 should read "fail"
For the advanced class; I can foresee a situation in the near future whereby the system needs to look at a range numbers, rather then one specific number.
For example
A1 could read "1" which is a pass , or "2", "3" etc are also be a pass.
4,5,6 are neutral
7,8,9 are fail
I am organizing a golf scoreboard for my golf tournaments. Basically, this is what I want to do:
I would like to rank the players that are entered by their score, in ascending order. Basically, I want to take the column for "total strokes" and organize the numbers so that it will rank the numbers that are entered. If there are no numbers entered in a specific row, I don't want that "zero" to be ranked in the list. So for example, if I have five golfers but they are spread out over 10 rows, I would still need to have five numbers in the rank: 1,2,3,4, and 5, not 1 to 10, as if it were ranking the rows that have nothing entered in them.
This is how it will look:
Johnson - Strokes : 70 - Rank :1
Williams - Strokes : 75 - Rank : 3
Patterson - Strokes : 72 - Rank : 2
i can calculate finish "molkky" game scores?
These are rules; you get points after falling sticks (if any falls). Points are 0-?. Goal is to get 50 points. If you get over 50 points, then points go back 25 points.
As you see (attachment) Matts points are 51, so he has now 25 points.
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 RelatedSee attached.PMS 2.xlsx. I have a table with data on PMS sheet and would like to have something like the example on the Example sheet using data from the PMS sheet.
View 2 Replies View RelatedI'm trying to find a way to return a value (score) based on the results in a table. I've been trying to use index and matches but am starting to think this will not be the answer for this one.
In the example attached, I have a section called report. For each project, there are criterias which need to be scored based on the values in the lookup table.
For example, project 1 has a result of 200 for criteria 1. On the lookup table 200 in criteria 1 would receive a score of 2. This result is then populated in column L as the score of this criteria.