Pass an array to Rank

  • Thread starter Thread starter Biff
  • Start date Start date
B

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
 
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?
 
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
 
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
 
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
 
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
 
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
 
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. :)
 
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.
 
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
 
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. :)
 
Back
Top