How to find min value in Excel text cells?

[Jeff Kantner]

Suppose I've got 5 cells, containing values 'b', 'd', 'a', 'e', 'c' ... if
asked to pick the minimum value, you'd say 'a' (setting aside collating
sequence issues). How do I create an Excel formula to figure out the answer
is 'a'? All the min/max functions I can find expect numbers, not text. And
I'm not in a situation where I can sort and pick top/bottom. Ideas?

 

[Max]

Assuming source range is fully populated with only single letters of the same
case (ie either all are in lower case, or in upper case), here's one possible
play to get the "minimum" letter

Put in say B1, array-enter the formula, ie press CTRL+SHIFT+ENTER to confirm
the formula (instead of just pressing ENTER):
=INDEX(A1:A5,MATCH(MIN(CODE(A1:A5)),CODE(A1:A5),0))

If source range may contain a mix of upper and lower case single letters,
and case sensitivity is to be ignored, then you could try this, array-entered:
=INDEX(A1:A5,MATCH(MIN(CODE(UPPER(A1:A5))),CODE(UPPER(A1:A5)),0))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[RagDyer]

Try this *array* formula:

=INDEX(A1:A5,MATCH(MIN(CODE(UPPER(A1:A5))),CODE(UPPER(A1:A5)),))

 

[Shane Devenshire]

Hi,

Here is an array function to do this:

=INDEX(A1:A5,MATCH(0,COUNTIF(A1:A5,"<"&A1:A5),))

To make it an array press Shift+Ctrl+Enter to enter it.

Since the same range is used 3 times a range name like R would simplify this
to

=INDEX(R,MATCH(0,COUNTIF(R,"<"&R),))