return maximum text "value"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marcel - 21 Mar 2008 16:08 GMT

Is there a function to get in a cell the maximum text "value" of a list
(e.g. T1, T2a, T2b).
Like MAX for numbers, part of this functionality is embedded in the
sort/filter commando's.

Reply to this Message

Ron Coderre - 21 Mar 2008 16:24 GMT

Depending on the list structure:

With
A1:A30 containing text values (no duplicates, some blanks are OK)

This formula returns the "max" text value:
B1:
=INDEX(A1:A30,MATCH(COUNTA(A1:A30)-1,INDEX(COUNTIF(A1:A30,"<"&A1:A30),0),0))

Eample:
A1: Bravo
A2: (blank)
A3: Zulu
A4: Charlie

The formula returns: Zulu

BUT...if there may be duplicates

Either this ARRAY FORMULA
(committed with Ctrl+Shift+Enter, instead of Enter)
B1: =INDEX(A1:A30,MATCH(MAX(COUNTIF(A1:A30,"<"&A1:A30)),
COUNTIF(A1:A30,"<"&A1:A30),0))

or...this longer...but, non-array formula:
B1:
=INDEX(A1:A30,MATCH(MAX(INDEX(COUNTIF(A1:A30,"<"&A1:A30),0)),
INDEX(COUNTIF(A1:A30,"<"&A1:A30),0),0))

OR...if there may be ALL blanks:
Wrap any of those formulas within this structure:
B1: =IF(COUNTA(A1:A30),the_formula,"No items listed")
...Committing with Enter or Ctrl+Shift+Enter, depending on the formula you
use.

Is that something you can work with?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

> Is there a function to get in a cell the maximum text "value" of a list
> (e.g. T1, T2a, T2b).
> Like MAX for numbers, part of this functionality is embedded in the
> sort/filter commando's.

Reply to this Message