rank question

[Guest]

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.

 

[William]

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
(e-mail address removed)

 

[Guest]

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

 

[Guest]

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

 

[Jack Sons]

Maybe (?)

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

Jack Sons
The Netherlands

 

[Guest]

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

 

[Domenic]

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!

 

[Guest]

Hi Domenic,

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

Regards,
Bernd

 

[Domenic]

I don't understand, can you explain...

 

[Guest]

Hi Domenic,

Put 10,20,30 into cells A1, A2, A3. Enter =B1 into cell A4. Put your value
you want to be ranked into cell B1 and =RANK(MATCH(B1,A:A,TRUE),A:A,FALSE)
into cell C1.

If you now enter the value 4 into B1 then the correct result 4 will appear
in C1.

This keeps your formula short and simple (and "array-free").

HTH,
Bernd

 

[Domenic]

But any other value would return a #N/A value...

 

[Guest]

Hi Domenic,

Oops - should read

=RANK(LOOKUP(B1,A:A),A:A,FALSE)

Regards,
Bernd

 

[Domenic]

Yes, but that assumes that the list is placed in ascending order.
Unless I missed it, I don't think we know that this is in fact the case.

Also, if A1:A5 contains the following numerical values...

10
25
50
80
100

....and we want to rank the number 2, your formula will return #N/A.

 

[RagDyeR]

If you're ranking 28 cells, the highest rank is 1, and the lowest rank is
28.

If the highest number is 100, and you want to rank 500 somewhere within the
28 cells, the rank is *still* 1.

Conversely, if the lowest number is 10, and you want to rank 9, you only
have 28 "rankings", so 9 would also be ranked 28th ... no? ... meaning the
lowest rank possible, since this *WORLD* only exists between 1 and 28 !

If that premise is acceptable, then try this *array* formula:

=RANK(MAX(IF(A1:A28<=C1,A1:A28),MIN(A1:A28)),A1:A28)

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Yes, but that assumes that the list is placed in ascending order.
Unless I missed it, I don't think we know that this is in fact the case.

Also, if A1:A5 contains the following numerical values...

10
25
50
80
100

....and we want to rank the number 2, your formula will return #N/A.

 

[Domenic]

Actually, my thinking differed from your premise. I assumed, maybe
incorrectly, that if the lowest number is 10, you want to rank 9, and
you only have 28 cells, 9 would be ranked 29th.

Hmmm...interesting!