Results 1 to 4 of 4

Thread: [RESOLVED] Excel 365: How to get Min & MAX of values mixed numbes & text

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2014
    Posts
    32

    Resolved [RESOLVED] Excel 365: How to get Min & MAX of values mixed numbes & text

    I have a column list of Fiscal Years formatted as follows:

    FY19
    FY21
    FY18
    FY15
    FY30
    FY28
    FY29


    I need to the Max & Min values which supposed to be (Max=FY30 & Min=FY15) .. when using the function MIN(My range) the result is 0

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel 365: How to get Min & MAX of values mixed numbes & text

    do you have MAXA function available?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2014
    Posts
    32

    Re: Excel 365: How to get Min & MAX of values mixed numbes & text

    Yes I have MAXA and MINA .. both return zero

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Excel 365: How to get Min & MAX of values mixed numbes & text

    you might have to roll your own udf

    this sort of works
    Code:
    Function mymax(r As Range) As Variant
    Dim cel As Range
    For Each cel In r
        If cel > mymax Then mymax = cel
    Next
    End Function
    but as it is a text compare fy3 would be greater the fy22, if your values would always be formatted like fy## so fy3 would be fy03 then there would be no problem
    to fix the text compare problem you would need to just compare the actual value part after the text, using intrinsic (or explicit) type conversion
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width