Re: Retrieve the Nth element in a dataset

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

Interesting timing. As usual, I learned something from you.

=SMALL(IF(ISNUMBER(rng),IF(MATCH(rng,rng,0)=ROW(rng)-MIN(ROW(rng))+1,rng)),N)

I don't really need "" in the formula. FALSE is fine.

Checking for ISNUMBER is good as it takes care of blanks.

However, both formulae give #NUM! error if N is larger than the number of unique values. I won't worry about it though.

Your example makes sense. I was thinking of integers and I scratched my head.

I won't worry about Nth unique element in a data set of *text* only.

Epinn

"T. Valko" <biffinpitt@xxxxxxxxxxx> wrote in message news:O5v6mahWHHA.4796@xxxxxxxxxxxxxxxxxxxxxxx
I can't think of an example why one would need to retrieve the Nth element.

http://tinyurl.com/2cvlpd

Biff

"Epinn" <someone@xxxxxxxxxxxxxxxxxxx> wrote in message
news:OT2l6MhWHHA.3500@xxxxxxxxxxxxxxxxxxxxxxx
Previously I learned the following

=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),N),COUNTIF(rng,"<"&rng),0))
array formula à ascending order



=INDEX(rng,MATCH(LARGE(COUNTIF(rng,"<"&rng),N),COUNTIF(rng,"<"&rng),0))
array formula à descending order



I use them to retrieve the Nth element in a data set of numbers or text.



They work fine if there are no duplicates.



Tonight I have discovered a formula created by David Hager which takes care
of duplicates. But it is only good for numbers.



=SMALL(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula --> ascending order



=LARGE(IF(MATCH(rng2,rng2,0)=ROW(rng2)-MIN(ROW(rng2))+1,rng2,""),N) array
formula --> descending order



Don't know if I have missed anything. Biff, I will put them away in my
stash although I can't think of an example why one would need to retrieve
the Nth element.



Comments welcome from all.




.

Relevant Pages

  • Re: Retrieve the Nth element in a dataset
    ... I use them to retrieve the Nth element in a data set of numbers or text. ... They work fine if there are no duplicates. ... formula --> ascending order ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Retrieve the Nth element in a dataset
    ... I use them to retrieve the Nth element in a data set of numbers or text. ... formula --> ascending order ... formula --> descending order ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Retrieve the Nth element in a dataset
    ... They work fine if there are no duplicates. ... I will put them away in my stash although I can't think of an example why one would need to retrieve the Nth element. ...
    (microsoft.public.excel.worksheet.functions)
  • RE: Date Comparison in the same column
    ... Sort your data in Ascending order of Col B ... duplicates. ... For instance the required delivery date changed from February 2 ...
    (microsoft.public.excel.worksheet.functions)
  • How do I check search for duplicate entries in an Excel spreadshe.
    ... You can sort the serial numbers in ascending order. ... will ensure that duplicates appear in consecutive rows. ...
    (microsoft.public.excel.misc)