Re: Pass an array to Rank

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <domenic22@xxxxxxxxxxxx> wrote in message
news:domenic22-4E7C38.23110528062005@xxxxxxxxxxxxxxxxxxxxxxx
> 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 <u9xfKZEfFHA.3304@xxxxxxxxxxxxxxxxxxxx>,
> "Biff" <biffinpitt@xxxxxxxxxxx> 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" <domenic22@xxxxxxxxxxxx> wrote in message
>> news:domenic22-B8C33E.20312228062005@xxxxxxxxxxxxxxxxxxxxxxx
>> > 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 <Oj312pDfFHA.1412@xxxxxxxxxxxxxxxxxxxx>,
>> > "Biff" <biffinpitt@xxxxxxxxxxx> 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


.

Quantcast