Re: Pass an array to Rank

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" <biffinpitt@xxxxxxxxxxx> wrote in message
news:u$FVBtEfFHA.3584@xxxxxxxxxxxxxxxxxxxxxxx
> 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: calculating in a changing range
    ... > Biff, thank you very much for the help. ... > 3, but, this data is actually data on a circle(example: first cell is a ... > recording at 0 degrees, and the last cell is a recording 359.9 ... > degrees), so, is there a way to find the max between zero 3 and zero ...
    (microsoft.public.excel.misc)
  • Re: Formula Question
    ... John ... Biff wrote: ... > An alternative: CSE ... > first cell it ...
    (microsoft.public.excel.worksheet.functions)