Ranking - Not Sorting Properly.

[Matlock]

I previously revived a formula (Thanks Max) to Sort and Rank my Stores by the
Strongest Store to the Lowest Store. It did exactly what it was supposed to
do with my sample layout I made with just Four stores and scores. But when I
added all 14 stores to the list (each store as a rank like 2.45 or 3.56 or
11.75) it will not sort them properly.

I am using

=IF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1)))

for the ranking system. It works when the stores Ranks are SINGLE digit
numbers, but when they go into the tenths or hundredths the formula can't
sort the Stores in the correct order.


Example (This is all on the same sheet)

___This is what we will see when we print the sheet out.___
___The RANK formula is inside the cells with the stores names___
___It is not ranking them properly___

B--------C--
RANK___FOOD COST

1_______Hwy 105
2_______Lumberton 96
3_______Palestine
4_______Lumberton 69
5_______Mauriceville
6_______Baytown
7_______M L K
8_______Walden
9_______Kountze
10______Hugo
11______M L K
12______Loop 256
13______Athens
14______Silsbee

-------------------------------------------

__The stores are permanent and are sorted alphabetically__
__The Total is added together from 12 other sheets representing JAN-DEC - I
am using =SUMIF(JAN!D720,O7,JAN!B7:B20) to grab each section of code__
__The Rank is divided by whatever Month we have currently finished. At the
moment I have a cell off to the side that I will put 4 in to represent April
and the Rank will divide the Total by 4 to give me an average rank for the
store (=P7/R3)__

O-------------P------R
Food Cost____Total__Rank

Athens_______21___5.25
Baytown______30___7.50
Hugo_________44 ___11.00
Hwy 105______25 ___6.25
Kountze_______32___8.00
Loop 256______17___4.25
Lumberton 69___39___9.75
Lumberton 96___48___12.00
Mauriceville____23___5.75
M L K__________18___4.50
Mont Belvieu____39___9.75
Palestine ______24___6.00
Silsbee________13___3.25
Walden________47___11.75


-------------------------

What I have written down is exactly what my sheet is showing me. I am
dumbfounded on how to correct the formula to show the stores in the order
they are supposed to be in. It should look like this..

1_______Silsbee
2_______Loop 256
3_______M L K
4_______Athens
5_______Mauriceville
6_______Palestine
7_______Hwy 105
8_______Baytown
9_______Kountze
10______Lumberton 69 * 9.75
11______Mont Belvieu * 9.75
12______Hugo
13______Walden
14______Lumberton 96

Also.... I noticed when two stores have the same Rank.. Like Lumberton 69
and Mont Belvieu with 9.75, it will show only one store in both the 10 and 11
spot. Whichever store is first in the Alphabetic list is the one it puts on
both spots.


Thanks for reading through the mess. Any suggestions would be very helpful.

 

[p45cal]

I don't understand how that formula was going to work, could yo
provide a reference to the other thread?

Attached (to this post at thecodecage.com - there's usually a lin
lower down this message) is a workbook as I guess your sheet is set up
It results in the order you want. It doesn't highlight equal scores.
595


+-------------------------------------------------------------------+
|Filename: 2010-05-22_203101.png |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=595|
+-------------------------------------------------------------------+

 

[Bernd P]

I previously revived a formula (Thanks Max) to Sort and Rank my Stores bythe
Strongest Store to the Lowest Store. It did exactly what it was supposed to
do with my sample layout I made with just Four stores and scores. But when I
added all 14 stores to the list (each store as a rank like 2.45 or 3.56 or
11.75) it will not sort them properly.

I am using

=IF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1)))

for the ranking system. It works when the stores Ranks are SINGLE digit
numbers, but when they go into the tenths or hundredths the formula can't
sort the Stores in the correct order.

Example (This is all on the same sheet)

___This is what we will see when we print the sheet out.___
___The RANK formula is inside the cells with the stores names___
___It is not ranking them properly___

B--------C--
RANK___FOOD COST

1_______Hwy 105
2_______Lumberton 96
3_______Palestine
4_______Lumberton 69
5_______Mauriceville
6_______Baytown
7_______M L K
8_______Walden
9_______Kountze
10______Hugo
11______M L K
12______Loop 256
13______Athens
14______Silsbee

-------------------------------------------

__The stores are permanent and are sorted alphabetically__
__The Total is added together from 12 other sheets representing JAN-DEC -I
am using =SUMIF(JAN!D720,O7,JAN!B7:B20) to grab each section of code__
__The Rank is divided by whatever Month we have currently finished. At the
moment I have a cell off to the side that I will put 4 in to represent April
and the Rank will divide the Total by 4 to give me an average rank for the
store (=P7/R3)__

O-------------P------R
Food Cost____Total__Rank

Athens_______21___5.25
Baytown______30___7.50
Hugo_________44 ___11.00
Hwy 105______25 ___6.25
Kountze_______32___8.00
Loop 256______17___4.25
Lumberton 69___39___9.75
Lumberton 96___48___12.00
Mauriceville____23___5.75
M L K__________18___4.50
Mont Belvieu____39___9.75
Palestine       ______24___6.00
Silsbee________13___3.25
Walden________47___11.75

-------------------------

What I have written down is exactly what my sheet is showing me. I am
dumbfounded on how to correct the formula to show the stores in the order
they are supposed to be in. It should look like this..

1_______Silsbee
2_______Loop 256
3_______M L K
4_______Athens
5_______Mauriceville
6_______Palestine
7_______Hwy 105
8_______Baytown
9_______Kountze
10______Lumberton 69 * 9.75
11______Mont Belvieu * 9.75
12______Hugo
13______Walden
14______Lumberton 96

Also.... I noticed when two stores have the same Rank.. Like Lumberton 69
and Mont Belvieu with 9.75, it will show only one store in both the 10 and 11
spot. Whichever store is first in the Alphabetic list is the one it puts on
both spots.

Thanks for reading through the mess. Any suggestions would be very helpful.

Hello,

You can sort with worksheet functions with:
http://sulprobil.com/html/sorting.html

Regards,
Bernd

 

[Max]

The problem you face is because of tied scores. The earlier, simple
expression doesn't handle ties, an event which I had thought/presumed would
not happen with your depiction of sample scores in D2 down.

Here's a simple way to handle it with tiebreakers
You have your scores in D2 down, store names in C2 down
Put in E2: =IF(D2="","",D2+ROW()/10^10)
Copy down to cover the max expected extent of data in col D, say down to
D200. Minimize/hide col E. This is the tiebreaker col.

Then drop this in B2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROWS($1:1)),E:E,0)))
Copy down to B200. Col B will now return the full ascending list of store
names, sorted by the scores in col D. Stores with tied scores, if any, will
appear in the same relative order that they appear within the source data.

 

[Matlock]

Max,

I'm getting there. Slowly. lol

Alright, I've added

=IF(Q7="","",Q7+ROW()/10^10)

to the sheet. as well as

=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROWS($1:1)),E:E,0)))

I am not sure if I am inserting the cell codes into the correct spots.

D720 has the cells where I will be inserting the code (=IF(rows....)
O7:O20 Has the actual name of the stores in ABC order.
Q7:Q20 Has the actual ranks after the division.
R7:R20 Has the cells where I will be inserting the code (=IF(Q7="","",....)

Do I need to keep the previous code

=IF(ISNA(RANK(Q7,Q7:Q20,1)),"",INDEX(O7:O20,RANK(Q7,Q7:Q20,1)))

or discard it?

On the new code (=IF(ROWS($1:1)>...) I am not sure which cells go where.

 

[Max]

Discard the previous formula (the one using RANK)
Now here, you have the criteria formula in R7:R20
In D7:
=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
Copy down to D20 to return the auto full ascending sort

 

[Matlock]

Added it down from D720. It is listing Silsbee as 1st - which is correct.
But Silsbee is listed all the way down to D20.

Where should be $1:1s be corrisponding to? in the formula?

=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))

 

[Max]

I'm not sure what is still tripping you up over there. It should have worked
just as well for you. Check again that you have done this ..
In R2: =IF(D7="","",D7+ROW()/10^10)
R2 is copied down to R20

In D2:
=IF(ROWS($1:1)>COUNT($R$7:$R$20),"",INDEX($O$7:$O$20,MATCH(SMALL($R$7:$R$20,ROWS($1:1)),$R$7:$R$20,0)))
D2 is copied down to D20

Ensure that calc mode is set to auto. ROWS($1:1) is just an incrementer
which returns the series: 1, 2, 3, .. when you copy it down from the start
cell.

 

[Max]

Sorry, this part in the earlier should read as (following your set-up):
In R2: =IF(Q7="","",Q7+ROW()/10^10)
R2 is copied down to R20

 

[Matlock]

I am not sure where I messed the formula at or what I did wrong, but your
Formulas worked flawlessly. Thank you very much, Max. I am definitely putting
you on the source information for the excel book for thanks.

I'll be back when I need help with other formulas!

Thanks!

 

[Matlock]

Thank you very much, Max. The formulas are working perfecly now. Exactly what
I needed. Thanks, again!

 

[Max]

Oops, just realized ... R2 should read as R7. my eyes are no longer as sharp
as the mind still is, humble apologies

In R7: =IF(Q7="","",Q7+ROW()/10^10)
R7 is copied down to R20

The above takes the scores in Q7:Q20, which could contain ties, or even
multiple ties, and for any tied scores, it'll create fractionally different
scores to differentiate these.

It does this through the use of this part: Q7+ROW()/10^10
where the part: +ROW()/10^10
adds a very small number which will be different small number for each row
as you copy down by virtue of the ROW()

These different scores created in R7:R20 are then used as the base to
extract whatever is required (eg the names in O7:O20) via the
index(colO,match(small(colR,incrementer),colR,0)) expression

 

[Max]

Glad to hear that you finally got it going there ...
(do hit the YES button once more for the road!)

I'll be back when I need help with other formulas

Ahh, but I'd suggest that you post in this MS forum henceforth:
http://answers.microsoft.com/en-us/office/default.aspx#tab=4

And if you have vba queries, you can post in this MS vba forum:
http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads

MS has posted a msg that all xl newsgroups (like this one) will be closed in
end May 2010. The above forums are amongst those directed by MS for ng
posters to go to, and is MS' way forward for queries wef Jun 2010. All the
best to you and farewell ..

 

[p45cal]

Doctor, Doctor, I've noticed that people seem to be ignoring me...

Next!

 

[Max]

Doctor, Doctor, I've noticed that people seem to be ignoring me...

No, not really .. as regards your Q to the OP

I don't understand how that formula was going to work,
could you provide a reference to the other thread?

Here is a link to the OP's earlier thread, as requested:
http://tinyurl.com/27qcpt3

and here is my 1st reply to the OP's latest issue (in the other branch):

The problem you face is because of tied scores. The earlier, simple
expression doesn't handle ties, an event which I had thought/presumed would
not happen with your depiction of sample scores in D2 down.

Here's a simple way to handle it with tiebreakers
You have your scores in D2 down, store names in C2 down
Put in E2: =IF(D2="","",D2+ROW()/10^10)
Copy down to cover the max expected extent of data in col D, say down to
D200. Minimize/hide col E. This is the tiebreaker col.

Then drop this in B2:
=IF(ROWS($1:1)>COUNT(E:E),"",INDEX(C:C,MATCH(SMALL(E:E,ROWS($1:1)),E:E,0)))
Copy down to B200. Col B will now return the full ascending list of store
names, sorted by the scores in col D. Stores with tied scores, if any, will
appear in the same relative order that they appear within the source data.

 

[p45cal]

yes, but I supplied a solution and it wasn't even looked at - still
hasn't been.

 

[Max]

yes, but I supplied a solution and it wasn't even looked at - still

hasn't been

I'm afraid that has to be answered by the OP. Don't be discouraged however,
this sort of thing happens all the time in forums. Given the fortune of
receiving a plethora of responses to their queries, some OPs do check-out
each response's value and even reply to each individual responder while
others well, do not practice it to this level of diligence. And some OPs
don't even bother replying at all.