I need to rank a number against a separate list. It appears that the RANK
function in Excel requires your value to actually be "in" the list you're
ranking it against. Is there a formula I could use to work around this
limitation of the built-in RANK function.

Hi Bill

Assuming the range of numbers are in A1:A28 and the number you want a
ranking for is in cell C1 then try...
{=RANK(MAX(IF(A1:A28<C1,A1:A28)),A1:A28)}
This is an array formula so enter with Ctrl+Shift+Enter

-----
XL2003
Regards

William



"Bill_S" wrote in message
...
I need to rank a number against a separate list. It appears that the RANK
function in Excel requires your value to actually be "in" the list you're
ranking it against. Is there a formula I could use to work around this
limitation of the built-in RANK function.

Hi William,

=RANK(MAX(IF(A1:A28<=B1,A1:A28)),A1:A28) as array formula. Otherwise value
is one off if value in list.

Regards,
Bernd

Maybe (?)

{=RANK(MAX(IF(A1:A28<C1,A1:A28)),A1:A28)-1}

Jack Sons
The Netherlands


"William" schreef in bericht
...
Hi Bill

Assuming the range of numbers are in A1:A28 and the number you want a
ranking for is in cell C1 then try...
{=RANK(MAX(IF(A1:A28<C1,A1:A28)),A1:A28)}
This is an array formula so enter with Ctrl+Shift+Enter

-----
XL2003
Regards

William



"Bill_S" wrote in message
...
I need to rank a number against a separate list. It appears that the RANK
function in Excel requires your value to actually be "in" the list you're
ranking it against. Is there a formula I could use to work around this
limitation of the built-in RANK function.

Hi Jack,

No. Put 1, 2 and 3 into your lookup list and rank 2.1 with your formula. It
returns 1 but should result in 2, I think.

{=RANK(MAX(IF(A1:A28<=B1,A1:A28)),A1:A28)} is IMHO a possible array formula
solution.

=RANK(MATCH(B1,A:A,TRUE),A:A,FALSE) is a (IMHO better) normal solution.

Regards,
Bernd


"Jack Sons" wrote:

Maybe (?)

{=RANK(MAX(IF(A1:A28<C1,A1:A28)),A1:A28)-1}

Jack Sons
The Netherlands


"William" schreef in bericht
...
Hi Bill

Assuming the range of numbers are in A1:A28 and the number you want a
ranking for is in cell C1 then try...
{=RANK(MAX(IF(A1:A28<C1,A1:A28)),A1:A28)}
This is an array formula so enter with Ctrl+Shift+Enter

-----
XL2003
Regards

William



"Bill_S" wrote in message
...
I need to rank a number against a separate list. It appears that the RANK
function in Excel requires your value to actually be "in" the list you're
ranking it against. Is there a formula I could use to work around this
limitation of the built-in RANK function.

Hi guys!

Consider the following list...

10
20
30

If we try to rank 5 against this list, all formulas so far would return
a #N/A error value, when in fact the ranking should be 4, if I'm not
mistaken.

Maybe...

=RANK(MAX(IF(A1:A3<=B1,A1:A3,MIN(A1:A3))),A1:A3)+( B1<MIN(A1:A3))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"Bernd Plumhoff" wrote:

Hi Jack,

No. Put 1, 2 and 3 into your lookup list and rank 2.1 with your formula. It
returns 1 but should result in 2, I think.

{=RANK(MAX(IF(A1:A28<=B1,A1:A28)),A1:A28)} is IMHO a possible array formula
solution.

=RANK(MATCH(B1,A:A,TRUE),A:A,FALSE) is a (IMHO better) normal solution.

Regards,
Bernd


"Jack Sons" wrote:

Maybe (?)

{=RANK(MAX(IF(A1:A28<C1,A1:A28)),A1:A28)-1}

Jack Sons
The Netherlands


"William" schreef in bericht
...
Hi Bill

Assuming the range of numbers are in A1:A28 and the number you want a
ranking for is in cell C1 then try...
{=RANK(MAX(IF(A1:A28<C1,A1:A28)),A1:A28)}
This is an array formula so enter with Ctrl+Shift+Enter

-----
XL2003
Regards

William



"Bill_S" wrote in message
...
I need to rank a number against a separate list. It appears that the RANK
function in Excel requires your value to actually be "in" the list you're
ranking it against. Is there a formula I could use to work around this
limitation of the built-in RANK function.

Hi Domenic,

Right. Insert a dummy cell into the lookup list which refers to your ranked
cell.

Regards,
Bernd

Hello,

If column A shows your list and cell B1 your value to be ranked, then

=RANK(MATCH(B1,A:A,TRUE),A:A,FALSE)

will give you the rank (1 if biggest number...).

HTH,
Bernd