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
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.
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).
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.
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.
I've got data for different regions of our company. For this example, what I need to do is count the number of jobs in the "South Atlantic" region (Column T) that are NOT "Wal-Mart Stores, Inc" jobs. In order to do this, I put a "1" in Column F so that the formula would have something to sum:
The problem is that some job numbers, which are located in Column G, are duplicated. As a result, this number is higher than it should be.
So what I need to do is only count each job number once. How do I do that? Is there a way to modify the existing formula? Or do I need something completely different?
I am trying to get a list of all names found in a column but exclude duplicates... I know this can be done in a pivot table but I need the names in a drop down box... if I use the pivot table as the source it will pick up "Grand Total" as a name.
The number of people can increase from week to week...
removing duplicate names. Students were allowed to take a quiz as many times as they wanted. I need to remove the duplicate entry by keeping the highest grade.
Here is the setup of my excel file. Column 1 has surnames, Column 2, has first name, and column 3 has grade.
I can't figure out how to filter them based on first and last name because some students have the same name. with the grade as the criteria
I have the below table of data and what I am looking for is a formula that will count the number for unique numbers (col E) depending on a several criteria. So far I have got this formula but it's not working!
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.
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?
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.
Ranking values and ranking duplicates the same rank I'm looking to rank a group of numbers that will possibly have duplicate numbers that I want to show as a tie but have no break in the rankings.
I have a worksheet that I want to export to a csv file. However, there are multiple rows that I want excluded in the export. These lines are recognized by the word "No" in column E. Is this possible?
I'm trying to set up a diet/training spreadsheet so that when a client enters foods and exercises they don't want/can't do, those foods and exercises are made unavailable in dropdown menus (on my programming sheet) so that I can't inadvertently put them into their plans. I have a webform they fill out, which automatically puts the data into a spreadsheet and I know how to pull data from there into the clients' workbooks...
I have an excel file linked to a sharepoint 2007 network space. Everytime I open the file, my pivot tables will not refresh, because the source of the pivottable seems to update automatically to include the network drive name on them which somehow causes a problem. Is there anyway to solve this? I put a snippet of the source in the pivottable as an example.
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.
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.
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
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.
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.
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
I need to do a sum with multiple criteria and that can account for duplicate entries and select the correct duplicate to add. I have a sheet with 6 columns of data: TripDate, Company, DepartureTime, BookedTravelers, TripNumber, DataEntryDate. It's just a running data dump from multiple sources and times and can therefore have duplicate entries for a specific trip but where the number of people booked has changed and I need the sum to use the most recent number (dataentrydate is newest).
Example: TripDate Company DepartureTime BookedTravelers TripNumber DataEntryDate 10/31/13 A 15:30 10 111 10/01/13 10/31/13 B 15:30 11 999 10/01/13 10/31/13 C 15:30 12 999 10/01/13 10/31/13 A 16:00 20 222 10/01/13 10/31/13 A 17:00 30 333 10/01/13 10/31/13 A 15:30 50 111 10/15/13 10/31/13 A 15:30 40 111 10/25/13
Currently I have this in a seperate worksheet to break down the hourly loads where A2 is a date reference I can change to what date I need, and each company is listed in a different column with hours running down Column B from 0400-2200:
