Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Antediluvian' post='764132' date='08-Mar-2009 06:11'][/quote]
    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.

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Antediluvian' post='764156' date='08-Mar-2009 13:50'][/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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •