If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to Rank Name?
I have a Power Ranking Excel Book for my partner Restaurants that lists us
from 1st - 14th in different categories through the year. ------- Example 1 ------- Month Jan Category (Labor) Rank Name 1 Athens 2 Baytown 3 Walden 4 Hugo Month Feb Category (Labor) Rank Name 1 Athens 2 Hugo 3 Baytown 4 Walden ------- End Example 1 ------- I am using =SUMIF to grab each store's rank and add them up (A) then dividing (B) them by how many months we are into the year to give them each stores Rank for the Year To Date. So it looks like this.. YEAR TO DATE Labor Total(A) Rank(B) Athens 2 1 Baytown 5 2.5 Hugo 6 3 Walden 7 3.5 (all of the above Year to Date information will be hidden and it will only show the Ranks and Stores that are below on the YTD sheet so it will be cleaner to look at and fax to each store) My QUESTION is: I am trying to find out how to make the sheet automatically insert the STORE NAME by the RANK they are listed on the same sheet from the totals. Rank Store 1 blank (unknown formula to insert) 2 blank 3 blank 4 blank I am not sure what formula to put in the Blank to automatically insert the store who is ranked 1st-4th. The 1st - 4th will should stay in numerical order, and the stores should change throughout the year depending on their ranks for each category as they progress. Any help would be greatly appreciated. Thanks! |
#2
|
|||
|
|||
How to Rank Name?
Assume this table is in sheet: x, cols A to D
with stores' data running in row3 down YEAR TO DATE Labor Total(A) Rank(B) Athens 2 1 Baytown 5 2.5 Hugo 6 3 Walden 7 3.5 Assume your ranking = lower is better Try this in your results sheet In B2 (below "Store"): =IF(ISNA(RANK(x!C3,x!$C$3:$C$100,1)),"",INDEX(x!$A $3:$A$100,RANK(x!C3,x!$C$3:$C$100,1))) Copy down as far as required. Success? hit the YES below -- Max Singapore --- "Matlock" wrote: I have a Power Ranking Excel Book for my partner Restaurants that lists us from 1st - 14th in different categories through the year. ------- Example 1 ------- Month Jan Category (Labor) Rank Name 1 Athens 2 Baytown 3 Walden 4 Hugo Month Feb Category (Labor) Rank Name 1 Athens 2 Hugo 3 Baytown 4 Walden ------- End Example 1 ------- I am using =SUMIF to grab each store's rank and add them up (A) then dividing (B) them by how many months we are into the year to give them each stores Rank for the Year To Date. So it looks like this.. YEAR TO DATE Labor Total(A) Rank(B) Athens 2 1 Baytown 5 2.5 Hugo 6 3 Walden 7 3.5 (all of the above Year to Date information will be hidden and it will only show the Ranks and Stores that are below on the YTD sheet so it will be cleaner to look at and fax to each store) My QUESTION is: I am trying to find out how to make the sheet automatically insert the STORE NAME by the RANK they are listed on the same sheet from the totals. Rank Store 1 blank (unknown formula to insert) 2 blank 3 blank 4 blank I am not sure what formula to put in the Blank to automatically insert the store who is ranked 1st-4th. The 1st - 4th will should stay in numerical order, and the stores should change throughout the year depending on their ranks for each category as they progress. Any help would be greatly appreciated. Thanks! |
#3
|
|||
|
|||
How to Rank Name?
I am not completely sure how to put the formula together.
If the cells are listed like this (All on the same sheet) +....A........B..............C.............D 1....Rank..Stores.....YTD......... 2....1........Blank.......Athens....2 3....2........Blank.......Baytown..1 4....3........Blank.......Hugo.......2.5 5....4........Blank.......Walden....3.5 How would I put the formula.. =IF(ISNA(RANK(x!C3,x!$C$3:$C$100,1)),"",INDEX(x!$A $3:$A$100,RANK(x!C3,x!$C$3:$C$100,1))) Together in the Blank under stores to make it work. (not familiar with some of the code listed) |
#4
|
|||
|
|||
How to Rank Name?
Looks much simpler if it's not across sheets (sheetname is dropped)
Put this in B2: =IF(ISNA(RANK(D2,$D$2:$D$100,1)),"",INDEX($C$2:$C$ 100,RANK(D2,$D$2:$D$100,1))) Copy down to B100. -- Max Singapore --- "Matlock" wrote: I am not completely sure how to put the formula together. If the cells are listed like this (All on the same sheet) +....A........B..............C.............D 1....Rank..Stores.....YTD......... 2....1........Blank.......Athens....2 3....2........Blank.......Baytown..1 4....3........Blank.......Hugo.......2.5 5....4........Blank.......Walden....3.5 How would I put the formula.. =IF(ISNA(RANK(x!C3,x!$C$3:$C$100,1)),"",INDEX(x!$A $3:$A$100,RANK(x!C3,x!$C$3:$C$100,1))) Together in the Blank under stores to make it work. (not familiar with some of the code listed) |
#5
|
|||
|
|||
How to Rank Name?
On 20 Mai, 00:06, Matlock wrote:
I have a Power Ranking Excel Book for my partner Restaurants that lists us from 1st - 14th in different categories through the year. ------- Example 1 ------- Month Jan Category (Labor) Rank * Name * 1 * * * Athens * * 2 * * * Baytown * 3 * * * Walden * 4 * * * Hugo Month Feb Category (Labor) Rank * Name * 1 * * * Athens * * 2 * * * Hugo * 3 * * * Baytown * 4 * * * Walden ------- End Example 1 ------- I am using =SUMIF to grab each store's rank and add them up (A) then dividing (B) them by how many months we are into the year to give them each stores Rank for the Year To Date. So it looks like this.. * * YEAR TO DATE Labor * * *Total(A) *Rank(B) Athens * * * 2 * * * * * *1 Baytown * * 5 * * * * * *2.5 Hugo * * * * *6 * * * * * *3 Walden * * * 7 * * * * * *3.5 (all of the above Year to Date information will be hidden and it will only show the Ranks and Stores that are below on the YTD sheet so it will be cleaner to look at and fax to each store) My QUESTION is: I am trying to find out how to make the sheet automatically insert the STORE NAME by the RANK they are listed on the same sheet from the totals. Rank * Store 1 * * * * blank (unknown formula to insert) 2 * * * * blank 3 * * * * blank 4 * * * * blank I am not sure what formula to put in the Blank to automatically insert the store who is ranked 1st-4th. The 1st - 4th will should stay in numerical order, and the stores should change throughout the year depending on their ranks for each category as they progress. Any help would be greatly appreciated. Thanks! Hello, I suggest to use this as an example: http://sulprobil.com/html/sorting.html Regards, Bernd |
Thread Tools | |
Display Modes | |
|
|