Monday, October 27, 2008

Rank and Count, now together in one formula!

RANK is a nice function. You can use it to see where an item falls on a list. You can get that same information by sorting the list, but there will be times when another sort takes precedence, and RANK will save the day.

Start typing in the formula in the formula bar
and then hit the fx to bring up the Rank Wizard box
(there's other ways to do this. Pick the one you like best.)

Here's the wizard box.

The number is the value that you want to rank. The Ref is the range of numbers that are being ranked. Be sure to make this absolute using the dollar signs or your range will change when you copy and paste your formula down. The order specifies order. If you leave it blank, it assumes 0 which is descending. If you choose 1, it is ascending. In other words, the largest number in the list will be ranked number 1 if you choose 0 or leave it blank. The largest number in the list will be ranked last if you choose 1.

If the list includes values which are the same, they will have the same rank.

Assuming that a rank is only important in relation to the number of values, I like to add a count.

In this instance, I want to count values in a spreadsheet that I will be adding to over time. So, instead of counting an absolute list such as $B$2:$B$67, I'm counting the whole column. My count formula looks like this:

If I wanted to count an absolute list, it would look like this:

When I put the rank together with the count the formula looks like this:
=RANK(B2,$B$2:$B$68)&" of "& COUNT(B:B)

I used the "&" to concatenate the two functions together with the text string " of ".

The spreadsheet looks like this:

Putting the two formulas together means that I can get rid of columns G and H entirely. I just left them in to show my work.

I don't use Rank very often. I do use COUNT pretty often. I rarely use the two together. But, when I've needed them, it sure has been nice...


Chaitanya Sagar, Excel Expert said...

Nice post. I like your blog. Rank of count is a neat function. We can use this in a word document or a page that's getting printed.

chaitanya sagar- Excel Expert said...

good work.using the combination of RANK and COUNT is interesting and useful, work can be done in least time instead of using the RANK and COUNT functions separately.

deligentvishwas said...

Hi my name is vishwas and I am business analyst in a bpo and I want to know how to do automatic ranking for my agents....till today's date I m manually giving them ranks however the ranking goes like this. I choose few people and rank them according to their performance so it depend on the headcount and I have to devide the headcount by 10 and then ranked them however I want to automate it now ....I used rank formula but its not working because I have to give similar ranks to certain people.please help