Also, I have to go and study first and last value now.
I have a bunch of formulae (e.g. LOOKUP, COUNTIF)
to determine last value and I have to go and check if
yours is already there.
I didn't mean the FIRST TEXT ENTRY or the LAST TEXT ENTRY in the range. I
meant the first or last ordered alphabetically:
Brown
Smith
Williams
Adams
Luce
Adams would be the first and Williams would be the last.
Of course, one could use the original formula I posted and just change the k
parameter but the LOOKUP formula is much simpler.
Biff
Sorry, KJS, I didn't expect I had so much to say. I should have started my
own thread. If I want to discuss on first and last value I'll definitely
start my own thread. Allow me to finish off with sorting on this thread.
Biff, I am glad I said "If I understand correctly......" and you clarified.
I almost miss the beauty of the formulae - *sort* by *dragging* and not just
the first value. You know, I was so focused on nth element because it was
how it all started.
I think finally I understand every single word in your posts i.e. error
trap, blanks at the end of the list, text, sort ...... Without dragging
down, I missed so much.
We use COUNTIF, < etc. because it is TEXT. Of course, we can use the same
formula for numbers. But why bother, when I can just do
=SMALL(C$1:C$10,ROWS($1:1)) for all numbers and no blanks.
I don't really want to understand the reasoning/difference between the two.
I think I have taken in a lot within the last 24 hours and I don't want to
confuse myself any more. Also, I have to go and study first and last value
now. I have a bunch of formulae (e.g. LOOKUP, COUNTIF) to determine last
value and I have to go and check if yours is already there. Thanks for
alerting me that I can flip "<" to ">" to get a different order. When I
first discovered this by myself (plus LARGE/SMALL), I was so delighted.
Thank you for your wisdom and patience, my good teacher.
Epinn
T. Valko said:
I won't worry about the nth element in a range of text and numbers.
Yeah, I can't think of a situation where you'd need to know the 10th
element, but ya never know! Basically, the formula is meant to sort the
entire range by drag copying.
If you needed to know the first or last TEXT value (alphabetically) in a
TEXT ONLY list:
First:
=LOOKUP(2,1/((COUNTIF(rng,"<"&rng)=0)*(rng<>"")),rng)
Or, an array formula:
=INDEX(rng,MATCH(0,IF(rng<>"",COUNTIF(rng,"<"&rng)),0))
For the last, just flip the "<" to ">".
Biff
Don't get me wrong. You gave a perfect solution for the OP's request.
Yes, I got carried away. I was "greedy" and I thought I could use that
formula on numbers as well. It did seem to work when I had all numbers and
no blanks.
Okay, I won't drive myself crazy with Large/Small, "<" and ">" etc.
Forgive me and I shall give everyone and myself a break hopefully soon.
Thanks for the wonderful gifts. If I understand correctly, both formulae
return the smallest item. I won't worry about the nth element in a range of
text and numbers.
Epinn
Don't get carried away with </> Large/Small! It can be confusing! <bg>
That particular formula only works with TEXT and I assumed the rng only
contained TEXT. I didn't see any numbers and/or blank/empty cells
represented in the sample.
This formula works for TEXT only and accounts for blank/empty cells (no
error trap) (array entered):
=INDEX(rng,MATCH(SMALL(IF(rng<>"",COUNTIF(rng,"<"&rng)),ROWS($1:1)),IF(rng<>"",COUNTIF(rng,"<"&rng)),0))
This formula works for both TEXT and NUMBERS and accounts for blank/empty
cells (array entered). Note: without the error trap any blank/empty cells
will be included as 0's at the end of the list:
=IF(ROWS($1:1)<=COUNTA(rng),INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),ROWS($1:1)),COUNTIF(rng,"<"&rng&"")+COUNT(rng)*ISTEXT(rng)+100000*ISBLANK(rng),0)),"")
It will sort numbers first, then the text.
Biff
One more thing I just discovered.
LARGE and ">" is exactly the same as SMALL and "<" i.e. ascending order.
Epinn
Wow! This formula is so versatile. Thanks Biff for sharing.
I had so much fun playing with it and came up with the following findings.
Please correct me if I am wrong.
Keep SMALL and change "<" to ">", I get the kth element in descending order.
Keep "<" and change SMALL to LARGE, I also get the kth element in descending
order.
With SMALL, "<" and k = 1, I get the smallest element.
With LARGE, "<" and k = 1, I get the largest element.
With SMALL, ">" and k = 1, I also get the largest element.
All this is good only when I have only numbers (right aligned) or only text
(left aligned) in the range.
If I have text and numbers in the range, I don't trust any of the above
formulae.
e.g. A1:A10 I only have zzz (text) or 999 (number).
I get the same result regardless of whether I use SMALL or LARGE in the
formula. I am keeping the "<" in the formula. I don't understand why.
I also don't trust the formula when I have blanks. I only focus on all text
or all numbers plus blanks in the range.
e.g. =INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),1),COUNTIF(rng,"<"&rng),0))
rng is A1:A10. A1: aaa A10: zzz A2:A9 have other 3-letter combination
in between. No blanks.
The formula returns aaa as the smallest element. No problem. Now delete
aaa in A1 and the result is 0. No problem. Put back aaa into A1 and I get
aaa. No problem. Now delete zzz in A10. I still get aaa and not 0 for the
blank in A10. ???
I am missing something.
Appreciate input.
Epinn
Try this:
Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):
=INDEX(rng,MATCH(SMALL(COUNTIF(rng,"<"&rng),k),COUNTIF(rng,"<"&rng),0))
Where k = the kth element
Biff
