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
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.
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.
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.
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.
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.
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.
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?
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?
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.
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.
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.
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%
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.
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
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.
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
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).
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.
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
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
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.
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.
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:
