Position/Level of statistics

[Guest]

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

 

[T. Valko]

Try this:

JOSEPH 200
MARY 1300
DAVID 1300
ADAMS 1000

=IF(B1="","",SUMPRODUCT(--(B1<B$1:B$4),1/COUNTIF(B$1:B$4,B$1:B$4&""))+1)

Copy down as needed.

 

[Bob Phillips]

Lookup RANK in help.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

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.

 

[Guest]

Thanx Biff

But where should I place this formula?

I want it to be placed on the range B1:B4, i.e. against the names, so as to
rank the corresponding statistics in A11:B14.

 

[Guest]

Thanx John & Bob,
But ranking I guess would not help as the names are in other range and I
want to lookup SUM of the party's corresponding amounts in another range to
determine its position/rank.

 

[T. Valko]

You should use an intermediate step of getting the totals and ranking
against those totals.