Use rank such as
=RANK(B1,$B$1:$B$4)
the first is the number for the individual, then a comma, then the entire
range of values. One exception to rank is that a tie would both be given 1,
the next would be given a 3 not a 2.
--
-John
Please rate when your question is answered to help us and others know what
is helpful.


"FARAZ QURESHI" wrote:

I have a list of 4 clients:

A
1 JOSEPH
2 MARY
3 DAVID
4 ADAMS

Next I have list of transactions:

A B
11 JOSEPH 200
12 MARY 300
13 DAVID 400
14 ADAMS 300
12 MARY 1000
13 DAVID 900
14 ADAMS 700

So the totals of each client come upto:

JOSEPH 200
MARY 1300
DAVID 1300
ADAMS 1000

I want to devise a formula for the range B1:B4 reflecting the position of
each client as follows:

A B
1 JOSEPH 3
2 MARY 1
3 DAVID 1
4 ADAMS 2

Because Mary & David have the highest amount of transactions they should be
numbered 1, Adams is the next so 2 and Joseph, being the least, 3.

Had Mary & David possess different highest amounts they would have been
marked 1 & 2, while Adams & Joseph would have been numbered 3 & 4
respectively.

All the help in this regard would highly be appreciated!

Thanx in advance,

Regards

FARAZ