Rank On Two Different Criteria?
Dec 8, 2012
I have a list of names that come from another sheet. I need to rank these by score and have used this formula:
[Code] ........
These names compete within their class but also within the class I need to consider (rank) a second category for example No or Yes. Also all names could be competing in the Yes category or for example 7 in the Yes and 3 in the NO. This will vary and is based on entry in another sheet.
The format is Class (column B), category (Yes or NO, Column C), Name (Column D), Score (Column S) and Rank (Column T)
If I include an IF statement =IF(S33="Yes", at the beginning of the above formula then I get all the Yes ranked perfectly. Is it possible to rank the others with the NO consideration?
View 4 Replies
ADVERTISEMENT
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
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
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 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
Oct 27, 2012
Need 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] .....
View 5 Replies
View Related
Nov 20, 2012
How can I rank with 4 criteria that are ranked differently? (Greater/ lesser etc)
Ranking - Online Spreadsheets - EditGrid
I had a look into this, but I got a bit confused with how isnumber, big, small and rank actually work
Using Excel 2003.
View 2 Replies
View Related
May 8, 2009
I'm racking my brains as to how I can structure a formula to conditionally rank a value in an array against only those values in the array whose corresponding criteria cell includes a specific letter.
So for example I have a list of 12 values, say 126; 239; 0; 171; 162; 157; 130; 199; 122; 153; 0; 15.
Each of those values corresponds to a heading, say: CDE; DFE; FGE; DFE; ERD; DEA; BDF; DFB; CDE; CEF; CAB; FAB. As you will note some of the headings may or may not be the same and may or may not include the same letters in different orders.
How can I write a formula that ranks in ascending order a given value drawn from the above list (which will be in another cell but which in this case is, let's say, the first value: 126) only against those values whose heading includes a specific character, for example the character C (the character in question will vary and be defined in a specific cell).
As an added complication I need the ranking calculation to exclude any zero values. So in the above example what the formula needs to do is rank the value 126 against a sub-set of the whole array comprising only the values 126; 122; 153; 0.
The answer I need is 2 because, discounting the zero value, 126 is the second highest value.
View 9 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
Oct 28, 2009
I am stumped on this one. I a spreadsheet with 5 columns (Quarters, Months, Level or Rank, Name, Sales). I need to have a formula to pull in the new Level 1 (rank) after Q4 sales are summed up across months for each name. See the attachment for detail.
View 4 Replies
View Related
Feb 22, 2009
Is it possible to 'eliminate' the zero values when using =RANK? For instance, if you have data with values from +100 to -100 with some lines have '0' value, can they be ranked disregarding the zeero values.
View 3 Replies
View Related
Apr 2, 2009
I have and table that gets the average performance of couriers as a %. so.
courier 1...courier 2...courier 3...courier 4,
98.16.......100...........99.7...........99.72
i want is a formula (hoping note to have to code this) that will show which order these couriers should be ranked in by looking at the range of averages. So courier2(100) would be ranked 1st, courier 4(99.7) would be ranked 2nd etc. the end table will look something like.
courier 1...courier 2...courier 3...courier 4,
98.16.......100...........99.7...........99.72
4..............1...............3................2
View 2 Replies
View Related
Jan 31, 2012
I have a spreadsheet of raw data organised in columns and rows. one of my columns is called SERVERNAME, and each row contains the data about an incident which is related to that server.
What I need to be able to do is to firstly calculate via a formula (cannot be a pivot table) the number of instances of SERVER X, and then I need to be able to rank the top 10 servers for which records have been logged.
As an example here is some raw data:
INCIDENT
SERVERNAME
LOCATION
OS
OWNER
0001
Server 1
Japan
Windows 2008
KERLEJ
[Code] ........
Based on the above data I would like to have an Excel formula that searches through the records (Rows) and determined that there are the following number of instances of servers:
server 1 2
server 2 1
server 3 3
And then a further formula (again I cannot use pivot table) to calculate the ranking:
Server 3 3 - ranked first
server 1 2 - ranked second
server 2 1 -space ranked third
View 9 Replies
View Related
Mar 9, 2012
here is some code that I have,
Dim zima As Range
Dim Col As Long
On Error Resume Next
Set zima = Application.InputBox("Select column to paste to", Type:=8)
On Error GoTo 0
If zima Is Nothing Then Exit Sub
[code]....
Where in the formula C[-7], I need this to line up with the column that I selected via the InputBox. If I select column L then the -7 needs to change to +2. OR would there be a better way to write the formula in VBA?
View 7 Replies
View Related
May 4, 2013
trying to use the rank function but unable to use different ranges, even if I use named ranges
=rank(F1,F1:F30,F50:F60,F90:F115,0)
=rank(F1,F1:F30,F50:F60,F90:F115,0)'
It says entered too many arguments
View 5 Replies
View Related
Nov 21, 2013
I have created a sheet (call it sheet2) with 5 columns as per below snapshot. There's quite a lot of data as much as 10k rows.
Rank
Total
city
[Code]....
What am trying to do is create a summary table which shows the top 5 cities per region based on the population. This summary table will be on a separate sheet in something like the table below. formula that can lookup a region say Europe and show the top 5 cities based on the rank already calculated on sheet2 and also show the total in a separate column.
Rank
Region
City
total
1
Europe
[Code] ........
View 9 Replies
View Related
Jul 23, 2006
I have 10 sales figures in C2:C11.
In D2:D11, I want the rank of those values, with one twist.
If the value in B2:B11 is X, then that value is excluded from the ranking.
I am trying to basically do:
=RANK(C2,IF(B2:B11="X",0,C2:C11))
View 9 Replies
View Related
Apr 9, 2007
I deal with Income statements of varying length. What I'm trying to do is figure out a way to rank only the expenses.
The account names change from sheet to sheet, and the number of accounts always changes.
However, Expenses always START after a cell in the "A" column marked "OPERATING EXPENSES" and always ENDS after a cell in the "A" column marked "TOTAL OPERATING EXPENSES". However, I don't want to rank "TOTAL OPERATING EXPENSES" because it's the sum of all the indivicual expenses.
View 9 Replies
View Related
Oct 28, 2007
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.
View 9 Replies
View Related
Mar 25, 2008
I know I can go about this using a pivot table, but I would rather not. However, if it really is the best solution, then I’ll go that route.
************************************************************************>Microsoft Excel - School Cases - Assigned.xls___Running: xl2002 XP : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEFGHIJ1Case NumberOpened DateClosed DateAccount NameCase ReasonStatusSpecific issueOpenClosedAge (Days)2243301/02/0801/02/08Vincennes UniversityDisbursingClosedDisbursing Agent Process Issue0103243801/02/0801/02/08The Culinary Institute of AmericaDisbursingClosed-0104244801/02/0801/02/08Southern Methodist UniversityCertificationClosed-0105245101/02/0801/04/08Regent UniversityProcessingClosed-0126246001/03/0801/03/08Vincennes UniversityDisbursingClosedReturned Funds0107246901/03/0801/04/08Pennsylvania State University (The)DisbursingClosed-0118247001/03/0801/14/08Vincennes UniversityCertificationClosedAccount Issue01119247701/03/0801/04/08Michigan State UniversityDisbursingClosedLoan Level-Change amount01110249701/04/0801/04/08Illinois Institute of Technology - Main CampusProcessingClosedRequest for loan status change01011249801/04/0801/04/08Southern Methodist UniversityCertificationClosedDidn't receive01012251101/04/0801/07/08Rutgers UniversityDisbursingClosedLoan-Level Change date01313251301/04/0801/04/08Capella UniversityDisbursingClosed-01014251401/04/0801/07/08Saint Johns UniversityDisbursingClosedLoan-Level Change date01315251501/04/0801/07/08Regent UniversityDisbursingClosedLoan-Level Change date01316253301/07/0801/07/08Purdue UniversityDisbursingClosedLoan Level Check Status01017254301/07/0801/15/08Saint Johns UniversityProcessingClosed-01818254701/07/0801/08/08Saint Johns UniversityProcessingClosedRequest for loan status change01119256401/07/0801/18/08Clark Atlanta UniversityProfileClosedReceived revision to profile011120258101/08/0801/08/08Saint Johns UniversityDisbursingClosedLoan Level-Change amount010Jan [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
What I basically need it to do is count the number of times a school is listed in column D then rank the top ten.
View 9 Replies
View Related
Jul 29, 2008
I recently made a formula of net sales contribution (sales per product category/total sales). For weeks 1.2008 to 30.2008. But I have several product categories. I would like near the value of the formula (not in another cell!) to insert a rank of these contributions. I d love to have the rank inside a parenthesis() near the percentage.
Here is the format
NET SALES CONTRIBUTION PER PRODUCT HIERARCHY 239.200740.200741.2007
COM RETAIL MEDIA0,03%0,73%0,22%
DTM STORAGE MEDIA0,00%0,06%0,00%
Of course I dont know how to do it.
View 9 Replies
View Related
Feb 4, 2009
Trying to figure out how to write a formula to rank values in B if the values in A are the same? I tried rank(if(A2:A17=A2,B2:B17))but this did not seem to work.
View 9 Replies
View Related
Mar 31, 2009
i rank some thing
stock 25
A B c D
rank style qty
1 st1 10 for d i need go to rank 1 first then qty 10
2 st2 5 then 2 then 3 till 25 (stock ) after it comes 0
5 st3 4
4 st4 3
3 st5 6
it can posible with rank and min formula
View 9 Replies
View Related
Aug 19, 2009
Which formula can I use to rank the people below.
Consider that the lower the value on attendance and cancellations is, the better this number will be.
In the other cells the highest the value, the better the number is.
NameAttendanceUtilizationProductivityQualityCancellationsCharles089.38%26.60NA0Nick587.69%56.921001Brian384.51%51.32800Joe080.71%36.151000
View 9 Replies
View Related
Dec 9, 2009
I have two columns of data - weights recorded during an angling competition. I currently have a rank set (in C) for the first column (A - pounds) to give a position for the highest weight recorded during the competition, the second column (B) records the ounces.
I need to be able to include the second column in the rank to cover the eventuality of two people recording the same number of pounds but different ounces.
View 9 Replies
View Related
May 10, 2005
I have a list of scores for people in one column and their groups listed in another. I want to rank their scores within their groups. how to do this? For example:
Score----Group----Rank by group
345--------A-----------1
367--------B-----------1
234--------A-----------2
123--------B-----------2
232--------A-----------3
View 7 Replies
View Related
Jun 19, 2006
I want to find the rank of a year that falls between the start date and end date. For e.g.
Start date : 1/1/2005
End date : 10/31/2014
Target Date: 12/20/2009
Using year(start date)-year(end date), I get the number of years but how to evaluate the answer in a cell to get the result = 5 (my desired answer as 2005 through 2009 = 5 years).
View 2 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 10, 2008
I created a automated football league with 5 teams in it that updated the team positions
(moved them up and down)as the new data was added in the secondary table and i used this type of formula in the secondary table on a rank system to rank positions according to points/wins/goal/difference/ect this also places the appropriate teams in alphabetical order in case the teams have equal stats
=RANK(O4,$O$4:$O$13)+RANK(G4,$G$4:$G$13)/10+RANK(N4,$N$4:$N$13)/100+RANK(F4,$F$4:$F$13)/1000+RANK(E4,$E$4:$E$13)/10000+1/100000
and this formula in the primary table that updates its self
=SMALL($A$4:$A$13,1) in the ranking
and this type of formula to transfer the data to the appropriate destination from the secondary table to the self adjusting primary table
ie points/ goals/ect
=INDEX($A$4:$O$13,MATCH($A22,$A$4:$A$13,0),4)
my question is i now want to extend my table to 24 teams so can i still use this ranking system as the range of 10/10000 only seems to let me use 10 teams( not tested yet)not sure how it works
View 10 Replies
View Related
