Ranking Question

I am dealing with a dataset that has a many ties in it. This is a list of
sales from 2008 to current, by customer and I need to rank on total sales.
In many cases, our smaller customers will buy the same items in the same
quantity month after month so it is not uncommon to have 100+ customers with
the exact same total sales amount.

How would I write a formula to ignore multiple ties, e.g.:

Total Amount Rank
1,000,000 1
900,000 2
300,000 3
5,000 4
5,000 5
5,000 6
5,000 7
5,000 8
2,500 9
2,500 10
50 11
50 12
50 13
25 14
and so on.

Thanks!
PJ

Try this...

Salses amounts starting in cell A2. Enter this formula in B2 and copy down
as needed:

=RANK(A2,A$2:A$15)+COUNTIF(A$2:A2,A2)-1

--
Biff
Microsoft Excel MVP


"PJFry" wrote in message
...
I am dealing with a dataset that has a many ties in it. This is a list of
sales from 2008 to current, by customer and I need to rank on total sales.
In many cases, our smaller customers will buy the same items in the same
quantity month after month so it is not uncommon to have 100+ customers
with
the exact same total sales amount.

How would I write a formula to ignore multiple ties, e.g.:

Total Amount Rank
1,000,000 1
900,000 2
300,000 3
5,000 4
5,000 5
5,000 6
5,000 7
5,000 8
2,500 9
2,500 10
50 11
50 12
50 13
25 14
and so on.

Thanks!
PJ

That did it.

Thanks!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"T. Valko" wrote:

Try this...

Salses amounts starting in cell A2. Enter this formula in B2 and copy down
as needed:

=RANK(A2,A$2:A$15)+COUNTIF(A$2:A2,A2)-1

--
Biff
Microsoft Excel MVP


"PJFry" wrote in message
...
I am dealing with a dataset that has a many ties in it. This is a list of
sales from 2008 to current, by customer and I need to rank on total sales.
In many cases, our smaller customers will buy the same items in the same
quantity month after month so it is not uncommon to have 100+ customers
with
the exact same total sales amount.

How would I write a formula to ignore multiple ties, e.g.:

Total Amount Rank
1,000,000 1
900,000 2
300,000 3
5,000 4
5,000 5
5,000 6
5,000 7
5,000 8
2,500 9
2,500 10
50 11
50 12
50 13
25 14
and so on.

Thanks!
PJ

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"PJFry" wrote in message
...
That did it.

Thanks!
--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.



"T. Valko" wrote:

Try this...

Salses amounts starting in cell A2. Enter this formula in B2 and copy
down
as needed:

=RANK(A2,A$2:A$15)+COUNTIF(A$2:A2,A2)-1

--
Biff
Microsoft Excel MVP


"PJFry" wrote in message
...
I am dealing with a dataset that has a many ties in it. This is a list
of
sales from 2008 to current, by customer and I need to rank on total
sales.
In many cases, our smaller customers will buy the same items in the
same
quantity month after month so it is not uncommon to have 100+ customers
with
the exact same total sales amount.

How would I write a formula to ignore multiple ties, e.g.:

Total Amount Rank
1,000,000 1
900,000 2
300,000 3
5,000 4
5,000 5
5,000 6
5,000 7
5,000 8
2,500 9
2,500 10
50 11
50 12
50 13
25 14
and so on.

Thanks!
PJ