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?

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(UP PER(A1:A5)),0))

--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik
---
"Jeff Kantner" wrote:
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?

Try this *array* formula:

=INDEX(A1:A5,MATCH(MIN(CODE(UPPER(A1:A5))),CODE(UP PER(A1:A5)),))
--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Jeff Kantner" wrote in message
...
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?

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),))

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Jeff Kantner" wrote:

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?