Rank formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Daniel Bonallack - 12 Sep 2007 17:36 GMT

I need a formula that will rank a given value against a set of other values.

For example, in column B, the numbers are 100, 90, 80, 70, 60 etc - i.e.
sorted in descending order.

I enter the number 63 in cell D1. In cell E1 I want a ranking formulat that
returns the number 5, as this would be the rank of this number if it were in
the array in column B.

Can you help? I do know that if I sort column B ascending, then I could
just use a vlookup formula, but this is not possible for a number of reasons.

Thanks!

Daniel

Reply to this Message

Toppers - 12 Sep 2007 18:16 GMT

try:

=MATCH(D1,B1:B10,-1)+1

Assumes D1 <100

> I need a formula that will rank a given value against a set of other values.
>
[quoted text clipped - 11 lines]
>
> Daniel

Reply to this Message

Daniel Bonallack - 12 Sep 2007 19:26 GMT

Thanks to all for the answers - these work fine.

> try:
>
[quoted text clipped - 17 lines]
> >
> > Daniel

Reply to this Message

T. Valko - 12 Sep 2007 18:19 GMT

Maybe this...

Assuming your lookup array goes from 100 to 0 and the number to rank will
not be greater than 100.

=MATCH(D1,A1:A11,-1)+(COUNTIF(A1:A11,D1)=0)

Signature

Biff
Microsoft Excel MVP

>I need a formula that will rank a given value against a set of other
>values.
[quoted text clipped - 15 lines]
>
> Daniel

Reply to this Message

Bernard Liengme - 12 Sep 2007 18:19 GMT

With the B numbers in B1:B5, I used =MATCH(ROUND(D1,-1),$B$1:$B$5,-1)
This gives 5 when D1=63 and 4 when D1=68
Any modification needed?
best wishes

Signature

Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

>I need a formula that will rank a given value against a set of other
>values.
[quoted text clipped - 15 lines]
>
> Daniel

Reply to this Message