Ranking query

[Marie Bayes]

Hi
I would like to know if it's possible to change RANK so that it doesn't
increment if it finds duplicates, ie, all the duplicate highest ranks are 1 ,
then no matter how matter how many there are all the next highest numbers
will be 2, etc.
Thank you

 

[Mike H]

Hi

with your numbers in column A1 down put this in b1 and asjust the a100 to
the length of your range and drag down

=SUMPRODUCT(--(A1>A$1:A$100),1/COUNTIF(A$1:A$100,A$1:A$100&""))+1

Mike

 

[Marie Bayes]

Hi Mike H

That's great thanks, I just swapped the > for a < to get my ranking in the
right order, but it's working - thanks again.