Count Then Rank Top 10
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
ADVERTISEMENT
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 31, 2008
A few days ago I used the function from a good member here:
=TEXT(B43/B$42;"0,00%")&" ("&RANK(B43;B$43:B$68)&")"
Can I use a function that counts how many times the rank value (1) for example appears in a row.Different row underneath will be OK.
View 9 Replies
View Related
Aug 14, 2014
I have attached an excel 2010 spreadsheet to show the issue.
I was shown a formula to rank a series of numbers =RANK(A2,$A$2:$A$19775,0).
However, the ranking figure that get's generated, appears to be counting the cells or something like that.
Ranking Example.xlsx‎
View 10 Replies
View Related
Sep 13, 2006
I would like to have the duplicate numbers in position one ( cell D9:D18), position two (cell E9:E18) and position three (cell F9:18) all counted and then ranked based upon date range in column A.
The number order of precendence is 0-9 with zero being the lowest.
Column (I9:I18) counts the duplicates, draws and no draws of digits 0-9. Column (J9:J18) converts results from column I9:18 into a percent.
I need to add a sort button at the bottom of position 1 (columns H & I 22), position 2 (columns L & M 22) and position 3 (columns P & Q 22)
When the sort button is selected it will sort the data by # of repeats, draws, and no draws as illustrated in (columns I, M & Q) or sort data by Position number from 0-9 as illustrated in (columns H) which will then show data of repeats and percentages out of rank order, because it is in order of number precedence.
Last but not least a graphic bar chart that shows the results ranked highest to lowest from when the sort button is selected with the number at the top of the bar colored black. Bar graph colors to be: top 3 red, middle 4 yellow and bottom 3 blue. This should show a vertical cascade effect.
View 3 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
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
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
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
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
Apr 7, 2009
I have a list of "weights" in column H. They can all be ranked from 1 to 30 (none are exactly the same). I want to write 1 formula that sums the top 10 (biggest) weights without having to make another column for "weight rank". I tried the following as an array formula, but it didn't work.
=SUMIF($H$3:$H$32,RANK($H$3:$H$32,$H$3:$H$32)&">=10")
View 2 Replies
View Related
Sep 18, 2009
How do I rank filtered results? I do not wish to use VBA if possible.
View 4 Replies
View Related
Dec 22, 2009
Have a series of tables that are put in order ascending and desending using rank and index. the problem is the last 3 tables. short calls ranking and hang ups which puts #n/a instead of the persons name. long calls ranking which doesnt put them in any sort of order at all.
View 4 Replies
View Related
Dec 3, 2012
I have alphanumeric characters and I have to rank them. Previously these characters were only digits (Using Rank function) and now some alphabets mixed with them. What is the function to rank Alphanumeric characters? They are like:
1A2240E01
210003004U
216000001U
499630001
463630001
400650001
View 3 Replies
View Related
