I have a list of clients with their corresponding amount outstanding.
I want to rank them;
Then use lookup with cells containing 1-10 so as to list the top 10 clients;
Only problem is that when the 7th and the 8th values are same both are
ranked 7;
Finally #N/A appears in the vlookup for "8"?

Anyway, how to overcome this?

--

Best Regards,
FARAZ A. QURESHI

Data in column A
=SUMPRODUCT(--(a1<$a$1:$a$10),1/COUNTIF($a$1:$a$10,$a$a:$a$10&""))+1
Modify the range suits to your data

Best wishes
Sreedhar
"FARAZ QURESHI" wrote:

I have a list of clients with their corresponding amount outstanding.
I want to rank them;
Then use lookup with cells containing 1-10 so as to list the top 10 clients;
Only problem is that when the 7th and the 8th values are same both are
ranked 7;
Finally #N/A appears in the vlookup for "8"?

Anyway, how to overcome this?

--

Best Regards,
FARAZ A. QURESHI

The problem with top n lists where there can be ties is that a top 10 isn't
limited to just 10 items. A top 10 can be almost any number =10 depending
on the distribution of the ties. A top 10 could actually be 15, 20, ????.
For example, suppose you want the top 3 (highest) from this list:

10
10
7
7
7
5
5
2

How many comprise the top 3?

It depends on what *your* definition of top 3 is. The top 3 could be either:

10,10,7,7,7

Or, it could be:

10,10,7,7,7,5,5

--
Biff
Microsoft Excel MVP


"FARAZ QURESHI" wrote in message
...
I have a list of clients with their corresponding amount outstanding.
I want to rank them;
Then use lookup with cells containing 1-10 so as to list the top 10
clients;
Only problem is that when the 7th and the 8th values are same both are
ranked 7;
Finally #N/A appears in the vlookup for "8"?

Anyway, how to overcome this?

--

Best Regards,
FARAZ A. QURESHI