Prev: Average a column containing numbers and text
Next: Automate a Solver solution
From: Matlock on 22 May 2010 09:36
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!D7:D20,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.

From: p45cal on 22 May 2010 15:35

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

Attached (to this post at thecodecage.com - there's usually a link
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|
+-------------------------------------------------------------------+

--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204484

http://www.thecodecage.com/forumz

From: Bernd P on 22 May 2010 16:16
On 22 Mai, 14:36, Matlock <Matl...(a)discussions.microsoft.com> wrote:
> 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!D7:D20,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
From: Max on 22 May 2010 17:59
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.
--
Max
Singapore
---
"Matlock" wrote:
> 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!D7:D20,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.
>
From: Matlock on 22 May 2010 19:51
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.

D7:D20 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.
 |  Next  |  Last
Pages: 1 2 3
Prev: Average a column containing numbers and text
Next: Automate a Solver solution