MIN/MAX on text sort order

Question

In SQL Server, MIN and MAX can act on text that doesn't evaluate to numbers, returning the text item with the lowest or highest text sort order, or as it's known in SQL Server-speak, "collation order".

Is it possible to do that in Excel without going to a UDF that actually sorts?

For example, for MIN("bb","aa","cc") to return "aa", and MAX("bb","cc","aa") to return "cc".

Excel's MIN/MAX ignore text, and although MINA/MAXA can work on text, they break on text that doesn't resolve to a number. LARGE/SMALL don't do it either.

FWIW, a coworker asked me how to do this in a pivot. I don't see a way without going to a custom function. Am I wrong?

Answer 1

This array formula looks promising. since it is an array it needs to be entered with ctrl-shift-enter.

Max:

=INDEX(A2:A6,MATCH(0,COUNTIF(A2:A6,">"&A2:A6), 0))

Min:

=INDEX(A2:A6,MATCH(0,COUNTIF(A2:A6,"<"&A2:A6), 0))

Change the three ranges to what you want.

The max and min versions are the same except for the > versus <.

Answer 2

I believe you are correct, a custom function is best. The good thing to note is the normal comparator operators work similarly as you described.

Public Function MinStr(ByVal strVal As Range) As String
    Dim i As Integer
    Dim cell As Range
    MinStr = ""

    'Check to make sure the range is not empty
    if strVal.Rows.Count > 0 then
        'Initialize MinStr to a known value
        MinStr = strVal.cells(1,1).Value

        'Iterate through the entire range
        For Each cell in strVal.Cells
            if(MinStr > cell.Value) then
                MinStr = cell.Value
            end if
        Next cell
    end if
End Function

Public Function MaxStr(ByVal strVal As Range) As String
    Dim i As Integer
    Dim cell As Range
    MaxStr = ""

    'Check to make sure the range is not empty
    if strVal.Rows.Count > 0 then
        'Initialize MaxStr to a known value
        MaxStr = strVal.cells(1,1).Value

        'Iterate through the entire range
        For Each cell in strVal.Cells
            if(MaxStr < cell.Value) then
                MaxStr = cell.Value
            end if
        Next cell
    end if
End Function