Count and Rank?

H

hce

Dear All

Say there are 100 records in Column A and the records are all number
ranging from 1-5. The numbers are not equally represented... for eg
there are 10 "1", 20 "2", 50 "3", 5 "4" and 15 "5".

Hence my question is.. is it possible to count how many "1", "2", "3"
"4" and "5" and then put a corressponding rank beside the number? Fo
eg. as there are 50 "3" which is the biggest out of the other
numbers, its ranking is 1. So is it possible to create a macro/functio
to get this rank "1" and then put it in Column B and in all th
corresponding Column A rows... hence the rank "1" should appear i
Column B beside all the "3" in Column A.

I would really appreciate any advice or suggestion...

Cheer
 
B

Bob Phillips

In B1: =COUNTIF($A$1:$A$100,ROW(A1))
and copy down however many numbers you want to test

In C1: =RANK(B1,$B$1:$B$10)
the 10 will depend upon the number of rows in B. Again copy down

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
H

hce

hi thanks... really appreciate the prompt response... your suggestion
worked... but this is also the same method i tried b4 posting... i
didn't make myself clear in my first post... the problem with the rank
formula is that in the event of equal no of count, it will put it as
the same... i.e. rank 1 2 2 2 5 6 7...

i would need the ranking to be 1 2 3 4 5 6 7... do u have any
suggestions?
 
J

jeff

Hi,

You need to decide what criteria is the tie-breaker
and add that into another helper column. Excel
cannot decide that.

jeff
 
H

hce

Hi Jeff

I cannot think of anything as a tiebreaker... do u have any
suggestions? Say there is a tie between "2" and "3", I would need the
"2" to be given the rank first then the "3" so that the numbers would
be in order by rank then followed by "number"... Hope I'm clear on
this... Can this b the tiebreaker criteria...?

cheers
 
B

Bob Phillips

Here is one way.

in C1: =RANK(B1,$B$1:$B$10)+COUNTIF($B$1:B1,B1)-1

and copy down

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
M

Myrna Larson

But you DO have a "tie breaker". You are saying that the item's name or
identifier is the tie-breaker. Is that a reasonable assumption?

In a list of test scores, sorted alphabetically by the person's last name,
that would mean that if Adams and Zimmerman both had a score of 100, Adams
would have a higher rank. Not reasonable for this situation, IMO.
 
Top