Pass an array to Rank

[Biff]

Hi Folks!

Anyone know how to pass an array of values as the ref argument of the Rank
function without hardcoding or using a range reference?

Assume I have this array of values generated by another formula:
{71;66;83;71;84}

How do I pass that array to Rank?

I can get the array passed but then the formula #VALUE! errors:

=RANK(71,{71;66;83;71;84})

I've tried using a name for the array, Indexing, Indirect ???

I've never seen this done but I'm not certain that it can't be done.

Biff

[Domenic]

Hi Biff!

Unfortunately, I have no idea. Does the solution have to involve the
RANK function or are you willing to use another alternative to get
ranking?

In article <[email protected]>,
"Biff" <[email protected]> wrote:

> Hi Folks!
>
> Anyone know how to pass an array of values as the ref argument of the Rank
> function without hardcoding or using a range reference?
>
> Assume I have this array of values generated by another formula:
> {71;66;83;71;84}
>
> How do I pass that array to Rank?
>
> I can get the array passed but then the formula #VALUE! errors:
>
> =RANK(71,{71;66;83;71;84})
>
> I've tried using a name for the array, Indexing, Indirect ???
>
> I've never seen this done but I'm not certain that it can't be done.
>
> Biff

[Biff]

Hi Domenic!

I'm try to extract TEXT values sorted in ascending order. I can do it easily
but it takes 2 helper columns. So what I'm trying to do is put it all
together in a single formula and eliminate the need for the helpers. The
list has dupes and that's what's proving to be a real bear.

green
black
grass
blue
green

I want to extract sorted based on the first letter. It doesn't matter if
"black" or "blue" is listed first. So the extracted list would look like
this:

black
blue
green
green
grass

Right now I use 2 helpers, 1 returns the code for the first letter:

=CODE(UPPER(A1)

The other helper is the Rank that break ties:

=RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1

Then a simple INDEX/MATCH.

Trying to put that all together in one formula!

Good challenge for someone!

Biff

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Hi Biff!
>
> Unfortunately, I have no idea. Does the solution have to involve the
> RANK function or are you willing to use another alternative to get
> ranking?
>
> In article <[email protected]>,
> "Biff" <[email protected]> wrote:
>
>> Hi Folks!
>>
>> Anyone know how to pass an array of values as the ref argument of the
>> Rank
>> function without hardcoding or using a range reference?
>>
>> Assume I have this array of values generated by another formula:
>> {71;66;83;71;84}
>>
>> How do I pass that array to Rank?
>>
>> I can get the array passed but then the formula #VALUE! errors:
>>
>> =RANK(71,{71;66;83;71;84})
>>
>> I've tried using a name for the array, Indexing, Indirect ???
>>
>> I've never seen this done but I'm not certain that it can't be done.
>>
>> Biff

[Alan Beban]

If you simply copy the list to another range and then perform Data/Sort,
Ascending, it will return

black
blue
grass
green
green

Is that satisfactory?

Alan Beban

Biff wrote:
> Hi Domenic!
>
> I'm try to extract TEXT values sorted in ascending order. I can do it easily
> but it takes 2 helper columns. So what I'm trying to do is put it all
> together in a single formula and eliminate the need for the helpers. The
> list has dupes and that's what's proving to be a real bear.
>
> green
> black
> grass
> blue
> green
>
> I want to extract sorted based on the first letter. It doesn't matter if
> "black" or "blue" is listed first. So the extracted list would look like
> this:
>
> black
> blue
> green
> green
> grass
>
> Right now I use 2 helpers, 1 returns the code for the first letter:
>
> =CODE(UPPER(A1)
>
> The other helper is the Rank that break ties:
>
> =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1
>
> Then a simple INDEX/MATCH.
>
> Trying to put that all together in one formula!
>
> Good challenge for someone!
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
>
>>Hi Biff!
>>
>>Unfortunately, I have no idea. Does the solution have to involve the
>>RANK function or are you willing to use another alternative to get
>>ranking?
>>
>>In article <[email protected]>,
>>"Biff" <[email protected]> wrote:
>>
>>
>>>Hi Folks!
>>>
>>>Anyone know how to pass an array of values as the ref argument of the
>>>Rank
>>>function without hardcoding or using a range reference?
>>>
>>>Assume I have this array of values generated by another formula:
>>>{71;66;83;71;84}
>>>
>>>How do I pass that array to Rank?
>>>
>>>I can get the array passed but then the formula #VALUE! errors:
>>>
>>>=RANK(71,{71;66;83;71;84})
>>>
>>>I've tried using a name for the array, Indexing, Indirect ???
>>>
>>>I've never seen this done but I'm not certain that it can't be done.
>>>
>>>Biff
>
>
>

[Vasant Nanavati]

Hi Biff:

If I understand your problem correctly, RANK (for some reason) doesn't work
with literal arrays, only with range references that translate to arrays,
despite what the Help files say.

Regards,

Vasant




"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi Folks!
>
> Anyone know how to pass an array of values as the ref argument of the Rank
> function without hardcoding or using a range reference?
>
> Assume I have this array of values generated by another formula:
> {71;66;83;71;84}
>
> How do I pass that array to Rank?
>
> I can get the array passed but then the formula #VALUE! errors:
>
> =RANK(71,{71;66;83;71;84})
>
> I've tried using a name for the array, Indexing, Indirect ???
>
> I've never seen this done but I'm not certain that it can't be done.
>
> Biff
>
>
>

[Biff]

That could be done but it would have to done every time the data changes. A
macro could also be used but the macro would have to run every time the data
changes. I'm looking for a completely automated operation and the formula
route satisfies that requirement. It's just a matter of trying to eliminate
the need for the helper columns. I can live with what I have!

Biff

"Alan Beban" <[email protected]> wrote in message
news:%[email protected]...
> If you simply copy the list to another range and then perform Data/Sort,
> Ascending, it will return
>
> black
> blue
> grass
> green
> green
>
> Is that satisfactory?
>
> Alan Beban
>
> Biff wrote:
>> Hi Domenic!
>>
>> I'm try to extract TEXT values sorted in ascending order. I can do it
>> easily but it takes 2 helper columns. So what I'm trying to do is put it
>> all together in a single formula and eliminate the need for the helpers.
>> The list has dupes and that's what's proving to be a real bear.
>>
>> green
>> black
>> grass
>> blue
>> green
>>
>> I want to extract sorted based on the first letter. It doesn't matter if
>> "black" or "blue" is listed first. So the extracted list would look like
>> this:
>>
>> black
>> blue
>> green
>> green
>> grass
>>
>> Right now I use 2 helpers, 1 returns the code for the first letter:
>>
>> =CODE(UPPER(A1)
>>
>> The other helper is the Rank that break ties:
>>
>> =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1
>>
>> Then a simple INDEX/MATCH.
>>
>> Trying to put that all together in one formula!
>>
>> Good challenge for someone!
>>
>> Biff
>>
>> "Domenic" <[email protected]> wrote in message
>> news:[email protected]...
>>
>>>Hi Biff!
>>>
>>>Unfortunately, I have no idea. Does the solution have to involve the
>>>RANK function or are you willing to use another alternative to get
>>>ranking?
>>>
>>>In article <[email protected]>,
>>>"Biff" <[email protected]> wrote:
>>>
>>>
>>>>Hi Folks!
>>>>
>>>>Anyone know how to pass an array of values as the ref argument of the
>>>>Rank
>>>>function without hardcoding or using a range reference?
>>>>
>>>>Assume I have this array of values generated by another formula:
>>>>{71;66;83;71;84}
>>>>
>>>>How do I pass that array to Rank?
>>>>
>>>>I can get the array passed but then the formula #VALUE! errors:
>>>>
>>>>=RANK(71,{71;66;83;71;84})
>>>>
>>>>I've tried using a name for the array, Indexing, Indirect ???
>>>>
>>>>I've never seen this done but I'm not certain that it can't be done.
>>>>
>>>>Biff
>>
>>

[Biff]

Hi Vasant!

That's pretty much the conclusion I've come to myself.

From help:

Ref is an array of, or a reference to, a list of numbers. Nonnumeric
values in ref are ignored.

What I've been trying to do is build the "is an array of", but it ain't
workin'.

Biff

"Vasant Nanavati" <vasantn AT aol DOT com> wrote in message
news:[email protected]...
> Hi Biff:
>
> If I understand your problem correctly, RANK (for some reason) doesn't
> work with literal arrays, only with range references that translate to
> arrays, despite what the Help files say.
>
> Regards,
>
> Vasant
>
>
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Folks!
>>
>> Anyone know how to pass an array of values as the ref argument of the
>> Rank function without hardcoding or using a range reference?
>>
>> Assume I have this array of values generated by another formula:
>> {71;66;83;71;84}
>>
>> How do I pass that array to Rank?
>>
>> I can get the array passed but then the formula #VALUE! errors:
>>
>> =RANK(71,{71;66;83;71;84})
>>
>> I've tried using a name for the array, Indexing, Indirect ???
>>
>> I've never seen this done but I'm not certain that it can't be done.
>>
>> Biff
>>
>>
>>
>
>

[Vasant Nanavati]

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Ref is an array of, or a reference to, a list of numbers. Nonnumeric
> values in ref are ignored.

Sadly, the first part is incorrect.

> That's pretty much the conclusion I've come to myself.

Been there, done that!

http://groups-beta.google.com/group/...09f686a6e4447/

Regards,

Vasant

[Domenic]

Biff,

Assuming that A1:A5 contains...

green
black
grass
blue
green

B1, copied down:

=INDEX($A$1:$A$5,MATCH(SMALL((COUNTIF($A$1:$A$5,"<"&$A$1:$A$5)+1)-ROW($A$
1:$A$5)/10^10,ROWS($B$1:B1)),(COUNTIF($A$1:$A$5,"<"&$A$1:$A$5)+1)-ROW($A$
1:$A$5)/10^10,0))

....confirmed with CONTROL+SHIFT+ENTER, which will give you the
following...

black
blue
grass
green
green

Hope this helps!

P.S. As I said before, I do like a challenge. Actually, I surprised
myself with this one.

In article <[email protected]>,
"Biff" <[email protected]> wrote:

> Hi Domenic!
>
> I'm try to extract TEXT values sorted in ascending order. I can do it easily
> but it takes 2 helper columns. So what I'm trying to do is put it all
> together in a single formula and eliminate the need for the helpers. The
> list has dupes and that's what's proving to be a real bear.
>
> green
> black
> grass
> blue
> green
>
> I want to extract sorted based on the first letter. It doesn't matter if
> "black" or "blue" is listed first. So the extracted list would look like
> this:
>
> black
> blue
> green
> green
> grass
>
> Right now I use 2 helpers, 1 returns the code for the first letter:
>
> =CODE(UPPER(A1)
>
> The other helper is the Rank that break ties:
>
> =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1
>
> Then a simple INDEX/MATCH.
>
> Trying to put that all together in one formula!
>
> Good challenge for someone!
>
> Biff
>
> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Biff!
> >
> > Unfortunately, I have no idea. Does the solution have to involve the
> > RANK function or are you willing to use another alternative to get
> > ranking?
> >
> > In article <[email protected]>,
> > "Biff" <[email protected]> wrote:
> >
> >> Hi Folks!
> >>
> >> Anyone know how to pass an array of values as the ref argument of the
> >> Rank
> >> function without hardcoding or using a range reference?
> >>
> >> Assume I have this array of values generated by another formula:
> >> {71;66;83;71;84}
> >>
> >> How do I pass that array to Rank?
> >>
> >> I can get the array passed but then the formula #VALUE! errors:
> >>
> >> =RANK(71,{71;66;83;71;84})
> >>
> >> I've tried using a name for the array, Indexing, Indirect ???
> >>
> >> I've never seen this done but I'm not certain that it can't be done.
> >>
> >> Biff

[RagDyer]

Would this "auto sort" *array* formula of Harlan's be of any help?

=INDEX($D$1:$D$10,MATCH(SMALL(COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),ROW()-ROW($
E$1)+1),COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),0))

The "E1" is not a typo!
It's the first cell that you enter the formula in.

This works for *all* text, OR *all* numbers.
--
Regards,

RD

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


"Biff" <[email protected]> wrote in message
news:[email protected]...
> That could be done but it would have to done every time the data changes.
A
> macro could also be used but the macro would have to run every time the
data
> changes. I'm looking for a completely automated operation and the formula
> route satisfies that requirement. It's just a matter of trying to
eliminate
> the need for the helper columns. I can live with what I have!
>
> Biff
>
> "Alan Beban" <[email protected]> wrote in message
> news:%[email protected]...
> > If you simply copy the list to another range and then perform Data/Sort,
> > Ascending, it will return
> >
> > black
> > blue
> > grass
> > green
> > green
> >
> > Is that satisfactory?
> >
> > Alan Beban
> >
> > Biff wrote:
> >> Hi Domenic!
> >>
> >> I'm try to extract TEXT values sorted in ascending order. I can do it
> >> easily but it takes 2 helper columns. So what I'm trying to do is put
it
> >> all together in a single formula and eliminate the need for the
helpers.
> >> The list has dupes and that's what's proving to be a real bear.
> >>
> >> green
> >> black
> >> grass
> >> blue
> >> green
> >>
> >> I want to extract sorted based on the first letter. It doesn't matter
if
> >> "black" or "blue" is listed first. So the extracted list would look
like
> >> this:
> >>
> >> black
> >> blue
> >> green
> >> green
> >> grass
> >>
> >> Right now I use 2 helpers, 1 returns the code for the first letter:
> >>
> >> =CODE(UPPER(A1)
> >>
> >> The other helper is the Rank that break ties:
> >>
> >> =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1
> >>
> >> Then a simple INDEX/MATCH.
> >>
> >> Trying to put that all together in one formula!
> >>
> >> Good challenge for someone!
> >>
> >> Biff
> >>
> >> "Domenic" <[email protected]> wrote in message
> >> news:[email protected]...
> >>
> >>>Hi Biff!
> >>>
> >>>Unfortunately, I have no idea. Does the solution have to involve the
> >>>RANK function or are you willing to use another alternative to get
> >>>ranking?
> >>>
> >>>In article <[email protected]>,
> >>>"Biff" <[email protected]> wrote:
> >>>
> >>>
> >>>>Hi Folks!
> >>>>
> >>>>Anyone know how to pass an array of values as the ref argument of the
> >>>>Rank
> >>>>function without hardcoding or using a range reference?
> >>>>
> >>>>Assume I have this array of values generated by another formula:
> >>>>{71;66;83;71;84}
> >>>>
> >>>>How do I pass that array to Rank?
> >>>>
> >>>>I can get the array passed but then the formula #VALUE! errors:
> >>>>
> >>>>=RANK(71,{71;66;83;71;84})
> >>>>
> >>>>I've tried using a name for the array, Indexing, Indirect ???
> >>>>
> >>>>I've never seen this done but I'm not certain that it can't be done.
> >>>>
> >>>>Biff
> >>
> >>
>

[Biff]

Very nice, Domenic!

I wan't too far off.

Instead of subtracting:

ROW($A$1:$A$5)/10^10

I was trying to add:

ROW()/10^5

I'll put this one in my "stash".

Thanks

Biff

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> Biff,
>
> Assuming that A1:A5 contains...
>
> green
> black
> grass
> blue
> green
>
> B1, copied down:
>
> =INDEX($A$1:$A$5,MATCH(SMALL((COUNTIF($A$1:$A$5,"<"&$A$1:$A$5)+1)-ROW($A$
> 1:$A$5)/10^10,ROWS($B$1:B1)),(COUNTIF($A$1:$A$5,"<"&$A$1:$A$5)+1)-ROW($A$
> 1:$A$5)/10^10,0))
>
> ...confirmed with CONTROL+SHIFT+ENTER, which will give you the
> following...
>
> black
> blue
> grass
> green
> green
>
> Hope this helps!
>
> P.S. As I said before, I do like a challenge. Actually, I surprised
> myself with this one.
>
> In article <[email protected]>,
> "Biff" <[email protected]> wrote:
>
>> Hi Domenic!
>>
>> I'm try to extract TEXT values sorted in ascending order. I can do it
>> easily
>> but it takes 2 helper columns. So what I'm trying to do is put it all
>> together in a single formula and eliminate the need for the helpers. The
>> list has dupes and that's what's proving to be a real bear.
>>
>> green
>> black
>> grass
>> blue
>> green
>>
>> I want to extract sorted based on the first letter. It doesn't matter if
>> "black" or "blue" is listed first. So the extracted list would look like
>> this:
>>
>> black
>> blue
>> green
>> green
>> grass
>>
>> Right now I use 2 helpers, 1 returns the code for the first letter:
>>
>> =CODE(UPPER(A1)
>>
>> The other helper is the Rank that break ties:
>>
>> =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1
>>
>> Then a simple INDEX/MATCH.
>>
>> Trying to put that all together in one formula!
>>
>> Good challenge for someone!
>>
>> Biff
>>
>> "Domenic" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi Biff!
>> >
>> > Unfortunately, I have no idea. Does the solution have to involve the
>> > RANK function or are you willing to use another alternative to get
>> > ranking?
>> >
>> > In article <[email protected]>,
>> > "Biff" <[email protected]> wrote:
>> >
>> >> Hi Folks!
>> >>
>> >> Anyone know how to pass an array of values as the ref argument of the
>> >> Rank
>> >> function without hardcoding or using a range reference?
>> >>
>> >> Assume I have this array of values generated by another formula:
>> >> {71;66;83;71;84}
>> >>
>> >> How do I pass that array to Rank?
>> >>
>> >> I can get the array passed but then the formula #VALUE! errors:
>> >>
>> >> =RANK(71,{71;66;83;71;84})
>> >>
>> >> I've tried using a name for the array, Indexing, Indirect ???
>> >>
>> >> I've never seen this done but I'm not certain that it can't be done.
>> >>
>> >> Biff

[Biff]

Hi RD!

That works. I'll put this one in my "stash", also.

Thanks

Biff

"RagDyer" <[email protected]> wrote in message
news:[email protected]...
> Would this "auto sort" *array* formula of Harlan's be of any help?
>
> =INDEX($D$1:$D$10,MATCH(SMALL(COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),ROW()-ROW($
> E$1)+1),COUNTIF($D$1:$D$10,"<"&$D$1:$D$10),0))
>
> The "E1" is not a typo!
> It's the first cell that you enter the formula in.
>
> This works for *all* text, OR *all* numbers.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> That could be done but it would have to done every time the data changes.
> A
>> macro could also be used but the macro would have to run every time the
> data
>> changes. I'm looking for a completely automated operation and the formula
>> route satisfies that requirement. It's just a matter of trying to
> eliminate
>> the need for the helper columns. I can live with what I have!
>>
>> Biff
>>
>> "Alan Beban" <[email protected]> wrote in message
>> news:%[email protected]...
>> > If you simply copy the list to another range and then perform
>> > Data/Sort,
>> > Ascending, it will return
>> >
>> > black
>> > blue
>> > grass
>> > green
>> > green
>> >
>> > Is that satisfactory?
>> >
>> > Alan Beban
>> >
>> > Biff wrote:
>> >> Hi Domenic!
>> >>
>> >> I'm try to extract TEXT values sorted in ascending order. I can do it
>> >> easily but it takes 2 helper columns. So what I'm trying to do is put
> it
>> >> all together in a single formula and eliminate the need for the
> helpers.
>> >> The list has dupes and that's what's proving to be a real bear.
>> >>
>> >> green
>> >> black
>> >> grass
>> >> blue
>> >> green
>> >>
>> >> I want to extract sorted based on the first letter. It doesn't matter
> if
>> >> "black" or "blue" is listed first. So the extracted list would look
> like
>> >> this:
>> >>
>> >> black
>> >> blue
>> >> green
>> >> green
>> >> grass
>> >>
>> >> Right now I use 2 helpers, 1 returns the code for the first letter:
>> >>
>> >> =CODE(UPPER(A1)
>> >>
>> >> The other helper is the Rank that break ties:
>> >>
>> >> =RANK(B1,B$1:B$5)+COUNTIF(B$1:B1,B1)-1
>> >>
>> >> Then a simple INDEX/MATCH.
>> >>
>> >> Trying to put that all together in one formula!
>> >>
>> >> Good challenge for someone!
>> >>
>> >> Biff
>> >>
>> >> "Domenic" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>
>> >>>Hi Biff!
>> >>>
>> >>>Unfortunately, I have no idea. Does the solution have to involve the
>> >>>RANK function or are you willing to use another alternative to get
>> >>>ranking?
>> >>>
>> >>>In article <[email protected]>,
>> >>>"Biff" <[email protected]> wrote:
>> >>>
>> >>>
>> >>>>Hi Folks!
>> >>>>
>> >>>>Anyone know how to pass an array of values as the ref argument of the
>> >>>>Rank
>> >>>>function without hardcoding or using a range reference?
>> >>>>
>> >>>>Assume I have this array of values generated by another formula:
>> >>>>{71;66;83;71;84}
>> >>>>
>> >>>>How do I pass that array to Rank?
>> >>>>
>> >>>>I can get the array passed but then the formula #VALUE! errors:
>> >>>>
>> >>>>=RANK(71,{71;66;83;71;84})
>> >>>>
>> >>>>I've tried using a name for the array, Indexing, Indirect ???
>> >>>>
>> >>>>I've never seen this done but I'm not certain that it can't be done.
>> >>>>
>> >>>>Biff
>> >>
>> >>
>>
>

[Domenic]

Just to clarify, as RagDyer has already shown, this part...

ROW($A$1:$A$5)/10^10

....isn't necessary, unless you also want to return a corresponding
value. So, for example, if A1:B5 contains...

green.....75
black.....25
grass.....80
blue.....55
green.....60

D1, copied down and over to the next column...

=INDEX(A$1:A$5,MATCH(SMALL((COUNTIF($A$1:$A$5,"<"&$A$1:$A$5)+1)+ROW($A$1:
$A$5)/10^10,ROWS(D$1:D1)),(COUNTIF($A$1:$A$5,"<"&$A$1:$A$5)+1)+ROW($A$1:$
A$5)/10^10,0))

....confirmed with CONTROL+SHIFT+ENTER, would return the following...

black.....25
blue.....55
grass.....80
green.....75
green.....60

Notice that in this case I used +ROW(...)/10^10 instead of
-ROW(...)/10^10 so that the first occurrence is returned first and the
second occurrence second.

Now I'll have to put this one in my 'stash' too.

In article <#[email protected]>,
"Biff" <[email protected]> wrote:

> I wan't too far off.
>
> Instead of subtracting:
>
> ROW($A$1:$A$5)/10^10
>
> I was trying to add:
>
> ROW()/10^5
>
> I'll put this one in my "stash".
>
> Thanks
>
> Biff