rank question

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