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!

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!

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)

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)

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