Rank A List Of Teams Based On Their Win/loss Record
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
ADVERTISEMENT
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
Dec 24, 2011
I have a spreadsheet to record profit or loss on a daily basis. The figure for each day can therefore be positive, negative or zero.
I want to add a column to display the total for the last 7 days (NOT the last 7 calendar days), in which either a profit or a loss was recorded (so excluding any cell that is zero).
I would prefer to add (insert), the column for each day as it comes and the range would obviously vary if the new day's figure was not zero.
View 9 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
Jun 15, 2014
I'm trying to design a Profit and Loss template that calculates off an accounting software data dump. I will attempt to explain the problem here but I have included a summary in the linked workbook which would be easier.
DOWNLOAD WORKBOOK: [URL]
I have two problems:
The Formula: Basically, I need to calculate every cell in the Profit and Loss statement based on the below criteria: The Profit and Loss item accounts, e.g. Gross Income has 6 accounts. There is a reference table for these - so incorporate index match/vlookup? User selected data from 4 combo boxes.
I am thinking a SUMIFS formula (using the combo box values) and somehow combining a vlookup to pull the items accounts. I'm starting to think that VBA is the best method?
Tweaking: The attached workbook is a small example. The full data dump contains 60,000 rows.
View 1 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
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
View Related
Apr 24, 2007
I have a list of names and i am using a vlook up formula. The problem i fall into is that it will only find the first Jon. How can i get it to find all of the Jon's and get all of the data?
example
Name Size Lan
a1 b2 b3
Jon 5 9
Jara 6 7
Fish 7 6
Jon 9 5
how can i get all of this info ifi nees the 2nd jons information
View 9 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
Feb 1, 2014
I'm looking for click event code for a list box called lbActiveItemList that finds a match for the selected record in Column 1, to the Sheet ReturnData in Column A from row 6 on (using the G column to find the last row). Upon finding a match it should close the userform, and make the G cell for the matching record the Active Cell.
View 7 Replies
View Related
Jun 1, 2006
I am trying to filter a list where the number of occurences of unique cost codes for each emp No is more than 1. What is needed is the emp no's who have only one costcode to have '0' in the 'no of codes' column. If more costcodes per empno, I need the first costcode to have 1, second code 2, etc in 'no of codes' column. The idea is at the end to be able to filter and delete all those with '0' in, as I only need to work with emp no's that have more than 1 'cost code'. See attached file. The records are in col A, the costcodes assigned to them in B, the formula in C, at the moment is:
=IF(AND( COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)>1),C1, IF(AND(COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)<2),(C1+1),1))
View 4 Replies
View Related
Feb 23, 2010
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.
View 4 Replies
View Related
Oct 14, 2011
Sheet1 *BMNO1GenderAge GroupTotalRank2Male6-72103*3Male6-71437*4Female6-71294*
I will have 200+ records similar to the above. I would like a formula that ranks the totals. The Males in the 6-7 Age Group would be in 1 Ranking while the Females in the 6-7 Age Group would be in another.
I prefer a formula over a macro. My original thought was =rank(n2,offset(?,?,?,?,?),1) but alas I'm missing something.
View 9 Replies
View Related
Mar 18, 2014
I have a list of times in a column. I'm looking for a macro that will go down the column and record all the times that lay between 5 and 7am, 1 and 3pm (13:00-15:00) and 8 and 10pm (20:00-22:00)
How to write it?
View 1 Replies
View Related
Feb 23, 2010
On the attached worksheet I am selecting items from the "Description" dropdown list on the Invoice worksheet. I then would like to find a way to automatically copy and paste the "Item #" and "Unit Price" found associated with the selected item on the database worksheet "Products".
i.e. I select the product and the spreadsheet fills in the rest of the required info.
View 5 Replies
View Related
Dec 12, 2013
how to rank values in one column based on criteria in another column?
For example: How do I rank Cells in Column A that only contain the text "AL" in column B?
COL...A....B
ROW
1......5....NL
2......6....AL
3......7....AL
4......8....NL
View 5 Replies
View Related
Feb 12, 2009
See the attached spreadsheet. I have people's names in cells A2:A5. These names have a rank value in cells B2:B5. What I would like to do is create a formula that sorts/orders the names in cells A2:A5 according to the rank in cells B2:B5. The results I would like to achieve are shown in A8:A11. I am aware of the "Sort" function in the "Data" drop down menu, but need to leave cells A1:B5 as they are.
View 2 Replies
View Related
Oct 25, 2007
I need to return the project codes for the project with the top 5 sales volumes within each Region for each product. If say, I have the following relevant columns (Project, ProdCode, Region, Sales) in columns A:D
View 9 Replies
View Related
May 20, 2007
I want to calculate the sales rank of the customer whose CUSTOMERID is equal to my ID? Note that sales rank is what position the salesperson is in terms of DOLLAR VALUE OF SALES.
Just a bit stuck on this. How do I calculate the rank? Is it a If statement or a Vlookup Question?
The spreadsheet can be found here: http://www.megaupload.com/?d=5D0YCHIG (It's too big to be uploaded to the forum hosting).
View 9 Replies
View Related
Mar 12, 2014
I am building a football stat database which I am looking to put in an individual sheet which will search the other 20 sheets, collate the information and then display a top 5 ranking in each area. For example, this is our setup for the data:
Screenshot 2014-03-12 21.56.18.jpg
So what I want to to is have a separate sheet which will have a section for say over 1.5 goals %. The formula would go and retrieve all of the % results for each individual team. Then it would rank the 5 best teams and display the team name in the first cell and the % in the other cell.
View 3 Replies
View Related
Oct 29, 2009
I need to sort the material data based on the material rank but i can't use the 'sort/filter' function. Therefore, I used the VLOOPUP function. For some reason the vlookup formula is not working could you let me know what is the problem? see attchment.
View 2 Replies
View Related
Jan 24, 2014
I have date data in Column O, which appears like. At any given point in time, i would have such date data for 5 to 10 days, and I want to just assign 1 to first date, and next date to 2 and so on.
What formula (can i use RANK formula, and how) I can use to get the desired result. SEcond part of the query may require macro, so will ask later.
Basic Data Desired Output
Column O Column P
20-Jan-13 1
20-Jan-13 1
20-Jan-13 1
20-Jan-13 1
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
21-Jan-13 2
22-Jan-13 3
22-Jan-13 3
22-Jan-13 3
22-Jan-13 3
26-Jan-13 4
26-Jan-13 4
26-Jan-13 4
26-Jan-13 4
View 1 Replies
View Related
Mar 20, 2014
I have a large spreadsheet where I've successfully ranked a large series of data based on revenue, using product classification as a criteria & summarising this on another page with the top 20 products. The problem I'm having is that within certain criteria's I have some products with the exact same revenue figure, so if the 2nd ranked product within classification "C" for example has revenue of $100, & there's another product within the same classification also with $100 of revenue, on my top 20 report I have results for the 1st & 2nd ranked product, but a blank for the third product & then results again for the fourth
The formula I'm using is
=IF(Y12="NA",0,SUMPRODUCT(--(Y12=$Y$12:$Y$6000),--(AC12
View 2 Replies
View Related
Jan 3, 2007
I need to have excel dynamically rank items based on two criteria’s (category and sales).
For Example:
I have a list of items with its category in column A, column B has the sales information. I need to rank the items by category and sales, the data is not sorted and I cannot use a macros/VB.
View 9 Replies
View Related
May 25, 2007
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
View 2 Replies
View Related
Apr 21, 2009
I run a spreadsheet that manages golf tournaments. i have to group
the # of players evenly as possible on each hole once they are entered into the tournament. since the number of players varies from tournament to tournament the math gets complicated trying to get the groupings right. i can do the math on paper but do not know how to code the math in a macro. this would save me a ton of time if someone can figure this out. i have attached a sample .xls that includes all the info i could think of a person needs to see what is going on. If i am asking to much i understand and will remove the thread.
View 6 Replies
View Related
May 20, 2007
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 9 Replies
View Related
