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?

 

[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

 

[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

 

[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]

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

 

[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]

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...functions/browse_frm/thread/f5f09f686a6e4447/

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.

 

[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.

 

[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

 

[Biff]

Hi RD!

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

Thanks

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$11)),(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.