RANKING alters when data is filtered

[Mantis]

Have spread sheet with list of percentages - adjacant column displays numeric
ranking of these percentages - when i filter the percentage column (eg sort
ascending) the formulas (cells that are being ranked) and hence the numeric
ranking are changing - each row is ranking different cells within the same
column.
eg.
A B C
1 Rank
2 ABL 4% 4 (rank by cells C2-C6)
3 ING 5% 5 (rank by cells C2-C6)
4 CBA 1% 1 (rank by cells C2-C6)
5 AMP 3% 3 (rank by cells C2-C6)
6 ANZ 2% 2 (rank by cells C2-C6)

after sort ascending

 

[trip_to_tokyo]

I have tried to replicate what you are seeing in EXCEL 2007 and have put up a
file for you at:-

http://www.pierrefondes.com/

It is item number 46 towards the top of my home page (called RANK & Filter).

In the above Workbook if I click on the filter button in cell F7 and select
Sort Smallest to Largest result is:-

CBA 1.00% 1
ANZ 2.00% 2
AMP 3.00% 3
ABL 4.00% 4
ING 5.00% 5

This appears to be correct.

If my comments have helped please hit Yes.

Thanks.

 

[מיכ×ל (מיקי) ×בידן]

In cell C2: =RANK(B2,$B$2:$B$6) > copy down and Sort
Micky

 

[JLatham]

I'm a bit confused as to why you're trying to sort by column C which I
presume contains a formula such as =RANK(B2,B$2:B$6) in cell C2. I would
think you'd be sorting either by column B (to get them in order by % value)
or by A to get them into alphabetical order. In either of those cases, the
values in column C will adjust automatically.

 

[Luke M]

Make sure that your formula uses a simple cell reference, and not a same
sheet reference.
Good:
=RANK(C2,C$2:C$6)
Bad:
=Rank(Sheet1!C2,C$2:C$6)

Even though the two refernces behave (and look) almost exactly alike, they
sort differently. See link for further detail:
http://spreadsheetpage.com/index.php/oddity/sorting_oddity_bug/