Re: Pass an array to Rank

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" <unavailable@xxxxxx> wrote in message
news:%23UTvWhEfFHA.3448@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <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
>>
>>

.

Relevant Pages

  • Re: Pass an array to Rank
    ... Biff ... >> That could be done but it would have to done every time the data changes. ... >> macro could also be used but the macro would have to run every time the ... >> the need for the helper columns. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Pass an array to Rank
    ... Biff ... >> but it takes 2 helper columns. ... >>> RANK function or are you willing to use another alternative to get ...
    (microsoft.public.excel.worksheet.functions)
  • Compilation order when compiling and loading a file
    ... defmacro a macro which uses the helper function ...
    (comp.lang.lisp)
  • Re: Compilation order when compiling and loading a file
    ... defmacro a macro which uses the helper function - use the macro ... (defmacro my-macro (a) ...
    (comp.lang.lisp)
  • Re: Can I get a wchar_t from a std::string?
    ... If you convert it using the MultiByteToWideChar function or a helper ... macro such as CA2W. ... A std:string is a single byte character string only. ...
    (microsoft.public.vc.stl)