How to find min value in Excel text cells?

J

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?
 
M

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
 
R

RagDyer

Try this *array* formula:

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

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

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

Top