rank question

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
willwest22@yahoo.com


"Bill_S" <BillS@discussions.microsoft.com> wrote in message
news:DAFF0210-F5AF-4B50-B985-6EA384940345@microsoft.com...
>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.

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

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" <willwest22@yahoo.com> schreef in bericht
news:ObG1sq4RFHA.2664@TK2MSFTNGP15.phx.gbl...
> 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
> willwest22@yahoo.com
>
>
> "Bill_S" <BillS@discussions.microsoft.com> wrote in message
> news:DAFF0210-F5AF-4B50-B985-6EA384940345@microsoft.com...
>>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" <willwest22@yahoo.com> schreef in bericht
> news:ObG1sq4RFHA.2664@TK2MSFTNGP15.phx.gbl...
> > 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
> > willwest22@yahoo.com
> >
> >
> > "Bill_S" <BillS@discussions.microsoft.com> wrote in message
> > news:DAFF0210-F5AF-4B50-B985-6EA384940345@microsoft.com...
> >>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 <6D43035E-9110-49EA-9AFD-6E66DD61E77F@microsoft.com>,
"Bernd Plumhoff" <BerndPlumhoff@discussions.microsoft.com> 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" <willwest22@yahoo.com> schreef in bericht
> > news:ObG1sq4RFHA.2664@TK2MSFTNGP15.phx.gbl...
> > > 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
> > > willwest22@yahoo.com
> > >
> > >
> > > "Bill_S" <BillS@discussions.microsoft.com> wrote in message
> > > news:DAFF0210-F5AF-4B50-B985-6EA384940345@microsoft.com...
> > >>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

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

In article <FFBE978C-519D-437D-B1A1-59089F771F89@microsoft.com>,
"Bernd Plumhoff" <BerndPlumhoff@discussions.microsoft.com> wrote:

> Hi Domenic,
>
> Right. Insert a dummy cell into the lookup list which refers to your ranked
> cell.
>
> Regards,
> Bernd

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

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

In article <F8601A32-2D4C-43FD-8147-30432342532B@microsoft.com>,
"Bernd Plumhoff" <BerndPlumhoff@discussions.microsoft.com> wrote:

> 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

Hi Domenic,

Oops - should read

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

Regards,
Bernd

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.

In article <1C9DAF88-AACB-400B-AEF8-9B1642E05439@microsoft.com>,
"Bernd Plumhoff" <BerndPlumhoff@discussions.microsoft.com> wrote:

> Hi Domenic,
>
> Oops - should read
>
> =RANK(LOOKUP(B1,A:A),A:A,FALSE)
>
> Regards,
> Bernd

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 !
--------------------------------------------------------------------

"Domenic" <domenic22@sympatico.ca> wrote in message
news:domenic22-995AE0.13515023042005@msnews.microsoft.com...
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.

In article <1C9DAF88-AACB-400B-AEF8-9B1642E05439@microsoft.com>,
"Bernd Plumhoff" <BerndPlumhoff@discussions.microsoft.com> wrote:

> Hi Domenic,
>
> Oops - should read
>
> =RANK(LOOKUP(B1,A:A),A:A,FALSE)
>
> Regards,
> Bernd

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!

In article <OyOHi6CSFHA.3140@tk2msftngp13.phx.gbl>,
"RagDyeR" <ragdyer@cutoutmsn.com> wrote:

> 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 !
> --------------------------------------------------------------------