Rank Bottom And Top Of List
Jul 30, 2007
What I want to do is rank both the top and bottom of a list.
I know how to rank but unfortunately the bottom will be changing (i.e.: the table size may change from month to month and this is where I have a problem.
I am ranking a table, then doing a lookup on the list to return other values based on the ranking.
What excel formula can I use to ensure I get the bottom 5 if the list length/table size is changing from month to month?
(Please note - I need to do this in excel - not as excel VBA)
View 5 Replies
ADVERTISEMENT
May 21, 2008
I am creating a Power Poll Spreadsheet for a Fantasy Football League, and I need to create a code that will take the win/loss columns and in a new column assign rankings so that the team with the most wins gets ranked '1st', then '2nd' etc. I'd like it to, in the case of a tie, rank both teams equally 'the 3rd and 4th best teams both have identical records, so both get ranked '3rd' and the '4th' ranking gets skipped.
I have searched for a formula to allow this, and I can't find it. Can anyone help? As an aside, is anyone aware of a place I can find a listing of formulas that can be used in sports ranking, etc?
View 8 Replies
View Related
Dec 30, 2013
I'm trying to Rank a list and than re-rank the list while excluding certain (or by Criteria) items
Vendor Co
Cost Fee
Rank
Vertox
500
4
BV
1520
3
[code].....
View 4 Replies
View Related
Dec 20, 2013
I am looking to have the items in column K 1 through 16 listed from highest to lowest and at the same time post the corresponding number from column J 1 through 16.
So that it reads as follows:
04 - 18 - 06
02 - 17 - 06
08 - 10 - 03
Item 04 has 18 points and is listed in 6 columns etc.
How can I get Excel to do this?
View 5 Replies
View Related
Jun 5, 2014
Does excel 2013 have a fence way to sort a list of alpha numeric numbers and alphabetize in this order A - Z and then 0 - 9.
Ex) Apple, Greg, Rob, Sand, 123, 126, 1000, 2001
View 2 Replies
View Related
Dec 27, 2009
How do I list selected data by order of appearance in a list?
Suppose I have a named range somewhere in my workbook called "TeamsRanked".
This range contains hundreds of names. The data associated with each of these names appears in a table in the workbook in rank order. The teams are ranked in descending order according to points scored. This means that the ROW() for the HIGHER-Ranked teams will be LOWER than the ROW() for the LOWER-Ranked teams. Also, this named range is constantly being resorted as updated data is introduced.
Now...Suppose I want to monitor just a few of the names??
For example: Cells A1:A3 on a separate worksheet contain the names of the teams I want to monitor.
What formula do I need in Cell A5 to return the HIGHEST ranked of the teams in Cells A1:A3??
...in Cell A6, to return the next highest ranked.
...in Cell A7, to return the LOWEST ranked.
I tried some IF(ROW(MATCH...)) ---"is less than" ---IF(ROW(MATCH...)) formulas...and I keep getting INVALIDs in my formula palette.
View 5 Replies
View Related
Apr 14, 2005
I'd like to have a dynamic report that ranks my lists based on an autofiltered list. In other words, I'd like to toggle the criteria that qualifies the lists content, and have excel generate "ranks" based on the values returned. Currently the rankings are based on the overall list, includeing the records that are filtered out.
View 4 Replies
View Related
Jul 15, 2012
I'm trying to come up with a formula that assigns ranks to an ascending list of integer values where ties are resolved by assigning tied values the mean of the ranks they would have received if there were no ties, e.g., 117, 119, 119, 125, 128 becomes 1, 2.5, 2.5, 4, 5. (If the two 119s were not tied, they would have been assigned the ranks 2 and 3, yielding a mean of 2.5.) My list looks like this:
Column_A Column_B
1
1
1
2
3
4
4
5
...
View 6 Replies
View Related
Nov 17, 2012
I need to rank a list that is not continious. I have used the rank function before but its always been in list of numbers.
How do i get the rank to work for example, I only want to rank on cells: B3, B6, B8, B10
View 3 Replies
View Related
Feb 26, 2014
I have two problems sort of rolled into one. I have a spreadsheet with a list of skills to perform in cells a2:z2. Under those I put in dates where those skills are performed so a3:a100 , b3:b100 and so on. At the moment I have the these dates populating another sheet with the list of skills going a2:a200 and the dates in b2:b200.
Annoyingly I have been asked set it up so it inputs the other way so my problems are: The skills in the second sheet are in a different order than in the first sheet so its not a straight correlation between the two so I would need to search for the title and match it to the second sheet. Then the second problem is as the will be numerous entries I need to find the next available space in the first sheet. So can you add data at the bottom of a list??
View 6 Replies
View Related
Apr 16, 2014
I have a large flat hierarchy table. I am looking for a method to select a parent and return a list of my lowest level children. I am somewhat familiar with VBA but I don't know where to begin coding this.
This has been a great learning source over the years, but this is one of the few instances I cannot find my problem so I am now posting. Example below:
Child
Parent
4
5
3
4
2
3
1
5
40
50
30
40
20
30
10
50
22
100
If I select 5, I want values 2 & 1 returned.
If I select 50, I want values 20 & 10 returned.
If I select 100, the value 100 should return.
If I select 40, the value 20 should return.
View 6 Replies
View Related
Oct 30, 2012
I have designed marksheet in excel and want to list only pass student on the basis of their rank.
View 5 Replies
View Related
Mar 5, 2012
Column A: multiple names of people, almost all names occur multiple times Columns B: counts how many times the name occurs in total
What I want in column C: if there are e.g. 18 times the name 'Peter'. I'd like to see in columns C that the first 'Peter' found has 1, the second Peter found has 2, etc; this offcourse for all names...
View 7 Replies
View Related
Mar 5, 2008
if it is possible to write/record a macro that will automatically add a summary/total row immediately below the data that is created from an advanced filter. (XL00)
View 12 Replies
View Related
Oct 11, 2009
I want to rank a list of teams based on their win/loss record. The list is something as follows.
Team A0-3
Team B0-2
Team C2-0
Team D1-2
Team E2-1
Team F2-1
Team G0-2
Team H3-0
Team I1-1
Team J1-1
Team K2-1
The column with the actual score contains the formula, =INDEX('2009'!$C$3:$BL$3,MATCH($A4,'2009'!$B$1:$BJ$1,0)). I would like to rank the list to something like,
Team H3-01
Team C2-02
Team E2-13
Team F2-13
Team K2-13
Team I1-16
Team J1-16
Team D1-27
Team B0-29
Team G0-210
Team A0-311
View 4 Replies
View Related
Feb 3, 2010
I am getting ready to create a spreadsheet to list vendor's names and emails along with other data. I would like to include at the bottom of the email addresses a quick copy and paste distribution list. I have achieved this in the past by using a similar formula:
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 3, 2009
I'm trying to create a new report and looking to get the top and bottom ten from a list. My original idea was to use the LARGE and SMALL funcions. However, when I use those because some values may be the same, the corresponding lookup values would be incorrect.
If I have Vendor A,B,C
VendorA 100%
VendorB 100%
VendorC 98%
My report would read this:
VendorA 100%
VendorA 100%
VendorC 98%
I'm using INDEX with MATCH to bring back the vendor. I then figured, I could just link the cell to the top 10 cells from where I am pulling from, which wrks for top ten, but won't work for the bottom ten.
I'm pulling the the data from a sheet with a query so I won't know where the ending data is unlike where the starting data begins. I then thought that maybe I could use an option button to control the query and just change the sort order. But, I really don't want to use VBA on this report, which I think I would have to do for that idea to work.
View 9 Replies
View Related
Feb 19, 2010
I need a formula that will give me the bottom most value that is filled in a cell in a certain column.
View 9 Replies
View Related
Sep 15, 2009
I am needing to do a formula based on a range of data that will return the top third middle third and bottom third.
Example Data
W X Y
Gross Gross
Margin Margin% Rank
51,241 36.46%2
25,584 19.56%20
19,914 16.26%40
17,188 14.27%52
19,167 15.55%43
6,828 5.23%84
48,076 29.17%3
18,282 13.78%47
16,896 12.75%58
16,124 12.14%64
16,549 12.27%59....................
I first need it to look in the rank column for highest number and divide by 3
Then I need it to give me the average Gross Margin if rank is between 1-32, 33-64, 65-95
I have this for the top and bottom but can't get the mid range to work(except it doesn't automatically calculate)
SUMIF($Y$8:$Y$102,"<33",$W$8:$W$102)/32
I have tried averageif....
Using rank to calculate this may not be the most efficent way
View 3 Replies
View Related
Aug 10, 2007
number and number 2 is what i have.. I would like it similar to the right side of this sheet where the missing ones are at the bottom of E
sheet: ...
View 9 Replies
View Related
Apr 17, 2009
I have a huge spreadsheet that pulls 5 different types of data based on user statistics and I am trying to come up with a sheet that will rank the top five and bottom 5. I've learned a lot from seach this site and others but I am still running it to some formula problems, so I thought I would tackle a couple here.
The first is the top 5 and bottom 5 of a conversion rate. I use this formula to get the top 5.
=IF(COUNTIF(PersonList!$Z$7:$Z$61,">10"),LARGE(PersonList!$AA$7:$AA$61,1),0)
Column PersonList!$Z$7 is the number of opportunities
Column PersonList!$aa$aa is the conversion percent being ranked
It works, but not like I am intending it too. I'm trying to elimanate those with less than 10 opportunities, and the first 2 people on the list have less than 10.
And for the bottom 5, I am using
=IF(COUNTIF(PersonList!$Z$7:$Z$61,">10"),SMALL(PersonList!$AA$7:$AA$61,ROWS(K29:K$33)))
Column rows k29 to k33 is where the bottom five conversion percent sits, k33 being the lowest, most likely zero but could be negative
Again trying to eliminate those will less than 10 opportunities. Plus when this populates, if more than 1 person has the same score like 0.0, only the first person will be listed for the same multiple value of 0.0
View 9 Replies
View Related
May 2, 2007
you know that part at the bottom toolbar which tells you when you select two or more cells what the sum/average/max/min is? Mine's disappeared.
View 2 Replies
View Related
May 9, 2007
I have some VBA code that connects to a mySQL DB and imports a few 1000 rows of data. One of the columns is an "Amount" field. How do you automaticly set a sum at the bottom of the column when the Macro has been executed withou me going there and do the SUM calc after the import?
View 3 Replies
View Related
Jun 3, 2008
Have a five column worksheet with the column headings run column A1 - E1 and there are currently 112 players filling out the data with many more to come
The 5 Column Headers are as follows:
Player# ... PlayerName ... M/F ... SessionAve ... GamesPlayed
What I need to do I pull out the top 5 male and top 5 female by SessionAve (including ties if possible). Would like the info to look like:
Male Shooter ...Session Ave ...GamesPlayed
Player1 .843% 30
Player2 .837% 35
Player3 .835% 25
etc..
Then Same for Female Players
View 6 Replies
View Related
Sep 30, 2013
I tend to use a lot of For Each loops when I want to go through a range looking for something but this time I'm not so sure it will work.
I have 9 databases each containing over 400 rows with different pieces of client information, these databases are shared between around 40 people (I've also used the 'Allow users to edit ranges' utility to restrict access). What this means however is that the users cannot delete an entire row, instead they can only 'clear contents' on individual cells. This is fine until I try to generate statistics from the databases using autofilters which stop at the first blank row.
So I need to write a backwards for each statement that starts at the bottom and deletes all the blank rows on the way up to the top. I've tried similar things to this before using the For Each but when you use it to delete a row it sometimes skips the next row as it continues at the next one.
I've not managed to get it to work before, I'm just looking for a little guidance really as to how to do this correctly.
VB:
For TopRow = 1 To BottomRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row Step -1
'If is blank delete row.....
Next
View 1 Replies
View Related
Mar 3, 2014
I am trying to do the following:
901 > 0 pts
851 - 900 gets 1 pts
LOWEST 3 scores gets 2 pts
Example of what i have for the top three
=IF(D8="-",0,IF(RANK(D8,D$8:D$34)<=3,2,IF(D8<79%,0,1)))
I have tried to change it but cant get it to work for the bottom 3 scores.
View 1 Replies
View Related
Feb 13, 2009
I would like to have the column titles stay so I have frozen row 1. But I would also like to freeze row 200 so I can keep up with the running totals at the bottom of the form as I fill in information. Is it possible to freeze a pain at the top and the bottom?
View 4 Replies
View Related
May 18, 2009
As we repeat rows at top and coloumn at left while printing using page setup option. can we repeat the rows at bottom and coloumn at right.
View 4 Replies
View Related
Feb 11, 2010
I want to be able to call the bottom number in a set of data that is ever expanding. I don't really know how else to describe it , so I will give an example.
Say I have:
3
6
4
I would want to be able to call 4, but then if I had:
3
6
4
5
I would want to be able to call 5, and so on. Is there any simple way of doing this? I basically want the maximum cell name, and the corresponding data inside of it.
View 4 Replies
View Related
