Retrieve the Nth element in a dataset

E

Epinn

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.
 
T

T. Valko

I can't think of an example why one would need to retrieve the Nth element.

http://tinyurl.com/2cvlpd

Biff

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.
 
E

Epinn

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

Why do things the hard way?

For numbers I can just use

=SMALL(rng,N)

This is good if there are no duplicates.

Epinn

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.
 
E

Epinn

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

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 said:
I can't think of an example why one would need to retrieve the Nth element.
Click to expand...

http://tinyurl.com/2cvlpd

Biff

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.
 
J

JMB

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

Thanks for posting this, it is just what I was looking for.

To answer your question why it is useful, right now I've got a lookup table
at work which lists physician specialties (which are text and duplicated),
effective dates, and amounts. The last column of the table lists the unique,
nonblank physician specialty descriptions which, in turn, is used by an input
cell (on a different sheet) that has data validation applied (with the list
option) so that the user can choose the specialty from a drop down box. A
lookup formula uses the selected specialty description and date to return the
proper amount from the table. When additional items are added to the table,
the inputs for the drop down box will automatically update to include the new
specialties. One other benefit is it eliminates typos (by the user - not the
person updating the table) that would result in the specialty description
not being found in the table since the drop down is fed from the same table
used to do the lookup.

But, I would like the drop down options in alphabetical order to make it
easier to find the one you are looking for.
 
J

JMB

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

Thanks for posting this, it is just what I was looking for.

To answer your question why it is useful, right now I've got a lookup table
at work which lists physician specialties (which are text and duplicated),
effective dates, and amounts. The last column of the table lists the unique,
nonblank physician specialty descriptions which, in turn, is used by an input
cell (on a different sheet) that has data validation applied (with the list
option) so that the user can choose the specialty from a drop down box. A
lookup formula uses the selected specialty description and date to return the
proper amount from the table. When additional items are added to the table,
the inputs for the drop down box will automatically update to include the new
specialties. One other benefit is it eliminates typos (by the user - not the
person updating the table) that would result in the specialty description
not being found in the table since the drop down is fed from the same table
used to do the lookup.

But, I would like the drop down options in alphabetical order to make it
easier to find the one you are looking for.
 
T

T. Valko

However, both formulae give #NUM! error if N is larger
than the number of unique values.
Click to expand...

That's true. It could be trapped but you'd have to define what to trap. If N
uniques what result would you want?
Click to expand...

It'll also error if there are no numbers in the range.

Biff

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

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 said:
I can't think of an example why one would need to retrieve the Nth element.
Click to expand...

http://tinyurl.com/2cvlpd

Biff

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

finding n-th most frequently occuring word 4
Combining two formulas 0
Cell value using Max 5
Continuation to equivalent for formula - to Mr. Harlan Grove (and notonly...) 1
OLD PROBLEM without SOLUTION , part || 1
First row where a cell is zero 2
copy adjacent cells also and put totals at the end (for copied cel 6
Index and Match 3

Top