MINA – MAXA (Excel 2003)

Topic

New Reply

The definition I found for MAXA is “Returns the largest value in a list of arguments. Text and logical values such as TRUE and FALSE are compared as well as numbers.” So I would expect “A” to be greater than “0” (zero). IF works in a comparison of two numbers or text or Booleans. I’ve attached a small test example. MAXA and MINA appear not to work properly. In summary, IF seems OK, MINA and MAXA do not.

Am I misunderstanding something or is there a basic problem? I’d appreciate any assistance that may be possible.

Reply | Quote

Replies

The MIN and MAX functions simply ignore text values and TRUE/FALSE values.

The MINA and MAXA functions include those, but they still return a numeric result. According to MAXA – Excel – Microsoft Office Online:

Arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0 (zero).

There is no built-in function that returns the highest alphanumeric value in a list.

Reply | Quote

Thank you Hans. I’d not understood this subtlety. Thank you also for showing me there is no built-in function to calculate such a result.

Reply | Quote

You could use these custom functions:

Code:

Function MaxAlpha(rng As Range) As String
  Dim n As Long
  MaxAlpha = CStr(rng.Cells(1))
  For n = 2 To rng.Count
	If CStr(rng.Cells(n)) > MaxAlpha Then
	  MaxAlpha = CStr(rng.Cells(n))
	End If
  Next n
End Function

Function MinAlpha(rng As Range) As String
  Dim n As Long
  MinAlpha = CStr(rng.Cells(1))
  For n = 2 To rng.Count
	If CStr(rng.Cells(n)) < MinAlpha Then
	  MinAlpha = CStr(rng.Cells(n))
	End If
  Next n
End Function

Example of use:

=MaxAlpha(A2:A10)

If you store the functions in your Personal.xls workbook, you'd use

=Personal.xls!MaxAlpha(A2:A10)

Reply | Quote