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.
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 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
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'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].....
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.
Score1
Serial #
Rank
90
146168
1
[Code] .....
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.
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.
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
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 RelatedI 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 RelatedI 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.
View 2 Replies View RelatedI 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
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
I am using the following formula to calculate due dates based on the rank i assign an issue.
Note: a similar formula is used for the due date of section one, but if it is not complete, i do not want this formula to kick in a determine the due date for section 2. The formula itself is working fine, but is giving a 'due date' of 1/30/1900. I was wondering if there is a way to not display anything if it has no number to calculate from.
=IF(ISNUMBER(SEARCH("S",D11)),F11+30,IF(ISNUMBER(SEARCH("B",D11)),F11+30,
IF(ISNUMBER(SEARCH("A",D11)),F11+30,IF(ISNUMBER(SEARCH("QNM",D11)),F11+30,""))))
I am trying to rank stores based on a quantity of run outs they have. I have a list of stores in order with the run out quantities. I use the large function to figure out the rank but when you have two stores with the same quantity it only finds the first on in the list.
View 2 Replies View RelatedI'm attempting to create a formula that will find the name associated with a value, and return that name on the same column as that value in a later equation.
OK OK to illustrate it a little better:
There are three people: Bill, Ted, and Andy. Each one is ranked in Points, so my table looks like this:
A B
1 Bill 10
2 Ted 20
3 Andy 30
Later on, I want to rank the individuals based on their score, using a formula. Right now, I can rank the scores based using LARGE(B1:B3,1), then LARGE(B1:B3,2), and lastly LARGE(B1:B3,3). That ranks the numbers in descending columns. However, I want the information to automatically populate the name associated with that particular point total. So, I want the system to know that B1 is Bill's score, and rank it, in descending order, later on in the spreadsheet, with Bill's name.
1. I would like to be able to select a month from a drop down ( cell C4), and for Column B ('Cumulative Performance') to reflect the sum for each name between Jan and the month selected.
2. In Column D I would like to rank the relative position of the sum total; such that if I selected 'Dec', John would display '13' in D7, Anne '3' etc.
3. In Column E I would like to show by way of a coloured arrow (or even a smilie icon) the relative change in ranking of the sum totals evaluated for my chosen month with those calculated up until the previous month (e.g. for Anne, if I select June, the Jan to June total is 36 (rank 2 in the June total's), the May to Jan total for Anne is 32 (rank 1), therefore her relative rank movement between the June and May cumulatives moves down and cell E8 would show a red-down arrow (amber horizontal for no change and green up-arrow for an improvement in rank).
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies View RelatedWhat 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
The Table below outlines a scenario i have..
I am looking for a sum that looks at Colum A: to determine if it is an old version and new. So G2 should If it is marked with the word "New" give me the sum to column F2: otherwise give me the the sum of B2:E2.
Was looking at Sumif but not can't seem to get the formatting right.
A
B
C
D
E
F
G
[Code] .....
I've got a sheet called DATA with a series of columns. Column Q has a series of numbers throughout the rows. I need to input a formula in a cell that says, everytime column Q is = 2, calculate the sum of those rows in column N.
The second one is a bit more challenging. A few cells in column F contain the number 1 in them. I need a formula that calculates the average of the cells in column C wherever there is a 1 in column F.
I am trying to create a sliding fee scale for a medical practice. Essentially it will categorize patients by family size and income level. The table which the scale is based off of is as follows: The far left column is family size (1-10) followed by 4 monthly income levels (ie. 1000, 1200, 1400, 1600). The table is based in the federal poverty line (FPL). I need to create a lookup formula which will reference this value and generate the appropriate category based on the income and family size of the patient. For example, according to the table a 3 person family which earns less than 1000 is in category A, but between 1000 and 1200 is in category B.
View 9 Replies View RelatedI have 1 worksheet which consist of few products for 6 months which I want to
sum up the individual product cost for certain period ie. YTD for 3 month, YTD april, and Q2 (april to June). The result is to appear in another worksheet. Try to use sumproduct, but unable to get what i want..
I have a complex workbook that now requires me to use different assumptions based on the label of the assumption (a "year" label) and the relevant year of the month in question (probably using the "Year(A1)" function. However, I do not know how to write a formula that will change assumptions based on whether the year of the relevant cell matches the year of the relevant assumption set. I attached a super-simple spreadsheet as a sample.
View 3 Replies View RelatedI have two workbooks. I'll call them wkbk1 and wkbk2.
I am looking at three cells in the same row in wkbk1.
I need to identify which row in wkbk2 contains those values and then return a value from a cell in the same row in wkbk2.
How do I structure this look up?