RANK function

[Guest]

Hello ladies and Gentlemans,

I'm a application develloper and I have been
working with excel for about 4 years (I got excel 2002
now ) now and I got a problem with ranking a serie of
number as everytime I try to get the specific rank of one
of my value (between 45 000 values ) it doesn't give me
all the ranking (I mean 1, 2, 3, 4, 5,etc) even though my
values are rarely duplicated. I even tried while the list
was already rank using data sort and it worked properly
but as soon as I tried changing the order of my list, the
rankings were no good anymore. Can somwbody please tell me
how do I do to make this ranking function work on my
unsorted lists ?

Best Regards to you all

 

[hgrove]

Anonymous wrote...

. . . now and I got a problem with ranking a serie of number as
everytime I try to get the specific rank of one of my value
(between 45 000 values ) it doesn't give me all the ranking (I
mean 1, 2, 3, 4, 5,etc) even though my values are rarely
duplicated. I even tried while the list was already rank using
data sort and it worked properly but as soon as I tried changing
the order of my list, the rankings were no good anymore. Can
somwbody please tell me how do I do to make this ranking
function work on my unsorted lists ?

I haven't been able to duplicate this problem. I enter =RAND() i
A1:A50000, convert them to values, select B1:B50000 with B1 active an
type the formula

=RANK(A1,A$1:A$50000)

press [Ctrl]+[Enter], then do something else for a long time becaus
Excel takes a LONG time to calculate all these. Then I enter th
following formula in C1.

=SUMPRODUCT(1/COUNTIF(B1:B50000,B1:B50000))

I've done this 3 times, and C1 gave 50000 each time.

What's your *EXACT* RANK formula

 

[jpendegraft]

Is it possible you have hidden some rows

 

[Guest]

The exact function is =RANK(AG6,$AD$6:$AD$10044,1) for a
list of dollar values.

 

[Guest]

there are no rows hidden and maybe its the dollar format
even thought I never heard of a dollar format that blocks
the rank function.

 

[hgrove]

Anonymous wrote...

The exact function is =RANK(AG6,$AD$6:$AD$10044,1) for a
list of dollar values.

What return value do you get for the formula

=COUNT($AD$6:$AD$10044)
-SUMPRODUCT(1/COUNTIF(AD6:AD10044,AD6:AD10044)