Points Ranking Formulas
Oct 17, 2005
I have a spread sheet in which I enter players names and the place they
finished for a certain event. I would like the sheet to calculate their
points according to where they finish and assign it to them along with a
formula that calculates a bonus point value according to the number of
players in a certain event.
1st place-450 pts + 30% bonus
2nd place-300 pts + 20% bonus
3rd place-180 pts + 12% bonus
4th place-120pts + 8% bonus
5th place-105 pts + 7% bonus
6th place 90 pts + 6% bonus
7th place 75 pts + 5% bonus
8th place 60pts + 4% bonus
9th-16th place 15 pts + 1% bonus
16th place 15 points
Bonus point formula is (# players X 5)x %
View 14 Replies
ADVERTISEMENT
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
Apr 27, 2006
I am trying to edit some formulas Under the Mon-D15 worksheet in this file. The # NUM is where I am having trouble. Basically, what I want to do is only rank the names in C15:C18. I was successful in doing this with C10:C14. But, for some reason it will not work with the lower values.
Question #2
Is there a way to make Columns I:O stay static through Rows 1:24 (width=3)?
I want Rows 25:40 to be different widths (width=2) in Columns I:O.
View 3 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
May 2, 2007
Is there a way of amending the following line of code so that it selects multiple data points in a data series in a chart (e.g points 14,15,16)?
ActiveChart.SeriesCollection(2).Points(14).Select
View 2 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
Dec 3, 2013
I am using a lot of linked reports that have to be rewritten each month. For example smaller formulas look like this:
=('S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$228*2)+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$262+'S:PUBLICProductionJob CardsMOLDING201311 November[440A SIDE SPOILER JOB CARD.xls]Production Parts'!B$292
What I want to do is extract the file path from the above formula and make it a composite of several cell references.
So what I need is to have a cell where they can change the month and another where we can change the year. So I set up several named cells that look like this:
_MONTH =11 November
_YEAR =2013
_JOBCARD ='S:PUBLICProductionJob CardsMOLDING
_PATH =_JOBCARD & _YEAR &"" &_MONTH
I tried several versions, I am hoping for something like this:
=('_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$228*2)+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$262+'_PATH &"[440A SIDE SPOILER JOB CARD.xls]"Production Parts'!B$292
View 4 Replies
View Related
Dec 15, 2008
I've found a few macros that will automate changing cell references from absolute to relative and they work great. However, when I run the macros on formulas that have references to another worksheet or workbook, the macro will not work correctly.
View 9 Replies
View Related
Dec 11, 2013
I'm trying to automate creating certain keyword combinations I need, based off of the values I input into reference cells in columns A - E; the goal is to compile a list of keywords which I will then use to track my rankings in search engines.
I'm looking to only output 500 keywords, so some of the cells in columns A, B, C & E will not contain data (column D will always have a primary Geo-target listed). This results in some of the concatenate formulas I've created outputting partial data (i.e. if there is no data in cell A10, and cell D2 contains the word "Knoxville", then cell I10 will output the data, "Knoxville "). How can I setup conditioning formatting or a formula so that these auto-generated cells appear blank if one of the reference cells has no data within it?The reason why I need the above to work is because I want to setup a formula that automatically counts the # of keyword combinations created by the data entered into any of the reference cells. With the partial combinations being listed, it skews my data. Which leads me to my next question: what is the best formula for counting the # of cells containing a full keyword combination from any of the cells listed in columns G - O (minus the data in the header cells; i.e. G1, H1, etc...)?Lastly, is there a formula I could use that would then aggregate all of the full keyword combinations within the "Complete Keyword List" column (column P)?
View 11 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
Jan 10, 2013
I am running Excel 2007 on Windows Vista Business 32 bit. Recently I have noticed that if I enter a formula into an empty, unsused cell, it is recognized as a formula. If I modify that formula, it is then recognized as text and does not work as a formula. The only way I can get the cell to recognize a formula is to delete the cell and start over. This same scenario does not occur on previously stored workbooks. I have checked all of the flags that I know about, including the Options function.
View 3 Replies
View Related
May 13, 2008
I have a macro running this code to strip out unwanted formulas and formatting.
Sub Quote_Wrapup()
'To stop screen flicker
Application.ScreenUpdating = False
Range("CDandC").ClearContents
Range("qdata5,qdata6").Font.ColorIndex = 2
'To delete delivery address lines if 1st line empty
If IsEmpty(Range("deliver_line1")) _
Then Sheets(1).Range("deliver_rows").EntireRow.Delete
'No End If required as only one action as a result of the If
Range("Item_Nos").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Columns("A:E") = Columns("A:E").Value .........................
A spreadsheet based on my template has been sent to me because the macro won't run properly. When I try to run the macro I get a Runtime Error '1004' Method 'Range' of object '_Global' failed on the following line. Columns("A:E") = Columns("A:E").Value.
View 4 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 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
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
View Related
