locked
Excel VBA - Min and Max Function Limits? RRS feed

  • Question

  • Hello forum,

    Is there is limit on what 'WorksheetFunction.Min' and 'WorksheetFunction.Max' can do or see?  I feel like I'm asking this wrong but I will try to explain what is happening. 

    For example, there is a column that Min is used on, and the min value is 0.00 which is rounded from values that are 0.001 and smaller and say this occurs 100 times out of 500 data entries in that column.  So the min function will see 0.00 100 times when the actual values are I guess 'hidden' because of the rounding.  When the min function is used it produces a '#VALUE!' error. 

    Same situation for the max function and the max values.  Basically, there are multiple entries where the rounded values are the same and the min/max functions produce a '#VALUE!' error. 

    The reason the values are rounded is because one of my superiors does not want to see values like 0.0000345 (ex. min) or 16.34812479 (ex. max).

    Thanks for your help!


    Auburn University Student IT/MIS Intern War Eagle!

    Wednesday, March 7, 2012 3:30 PM

Answers

  • The find method is causing the error.

    The Find Method works on the displayed value.If you change the num format to 0.000 then your function will work

    I will suggest that find method is not suitable for this.Take help of excel function for that.Check the following:

    Pls adjust the Line Continuing characters and it will work.

    Public Function MinReference(ColRange As Range) As String
        
        Dim lMinRow As Long
       
        lMinRow = Application.Evaluate("=MATCH(MIN(" & ColRange.Address & ")," _
            & ColRange.Address & ",0)")
        
       MinReference = ColRange.Resize(1, 1).Offset(lMinRow - 1, 0).Address( _
            rowabsolute:=False, columnabsolute:=False)
           

    End Function

    --------------------

    Public Function MaxReference(ColRange As Range) As String
        
        Dim lMinRow As Long
       
        lMinRow = Application.Evaluate("=MATCH(MAx(" & ColRange.Address & ")," _
            & ColRange.Address & ",0)")
        
       MaxReference = ColRange.Resize(1, 1).Offset(lMinRow - 1, 0).Address( _
            rowabsolute:=False, columnabsolute:=False)
           

    End Function

    Wednesday, March 7, 2012 4:48 PM
    Answerer

All replies

  • Hi Brian,

    I strongly suspect there is a VALUE error in one of the cells the MIN/MAX function(s) are pulling their data from. I just tried a MIN on a million cells without a hitch. Try the error tracing function on the formula tab from your cell with the min function.


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Wednesday, March 7, 2012 3:45 PM
  • Thanks for the reply!

    Well, it's not that.  I did find out what the problem is though.  It is failing at MinReference and MaxReference and that is returing the #VALUE! error. 

    Here is what the forumla in the cell is: =HYPERLINK("[File_Name.xlsm]'Sheet_Name'!"&MinReference('Sheet_Name'!$E:$E),MIN('Sheet_Name'!$E:$E))

    Here is the MinReference/MaxReference code:

    Public Function MinReference(ColRange As Range) As String
         
        Dim MinValue As Long
        
        Dim CellValue As Range
         
        MinValue = WorksheetFunction.Min(ColRange)
         
        Set CellValue = ColRange.Find(what:=MinValue, After:=ColRange.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
         
        MinReference = CellValue.Address(RowAbsolute:=False, ColumnAbsolute:=False)
            
    End Function
     
    Public Function MaxReference(ColRange As Range) As String
          
        Dim MaxValue As Long
        
        Dim CellValue As Range
         
        MaxValue = WorksheetFunction.Max(ColRange)
         
        Set CellValue = ColRange.Find(what:=MaxValue, After:=ColRange.Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
         
        MaxReference = CellValue.Address(RowAbsolute:=False, ColumnAbsolute:=False)
         
    End Function

    I've also tried MinValue and MaxValue as Doubles instead of Longs and still get the same error. Thanks for your help!


    Auburn University Student IT/MIS Intern War Eagle!

    Wednesday, March 7, 2012 4:09 PM
  • The find method is causing the error.

    The Find Method works on the displayed value.If you change the num format to 0.000 then your function will work

    I will suggest that find method is not suitable for this.Take help of excel function for that.Check the following:

    Pls adjust the Line Continuing characters and it will work.

    Public Function MinReference(ColRange As Range) As String
        
        Dim lMinRow As Long
       
        lMinRow = Application.Evaluate("=MATCH(MIN(" & ColRange.Address & ")," _
            & ColRange.Address & ",0)")
        
       MinReference = ColRange.Resize(1, 1).Offset(lMinRow - 1, 0).Address( _
            rowabsolute:=False, columnabsolute:=False)
           

    End Function

    --------------------

    Public Function MaxReference(ColRange As Range) As String
        
        Dim lMinRow As Long
       
        lMinRow = Application.Evaluate("=MATCH(MAx(" & ColRange.Address & ")," _
            & ColRange.Address & ",0)")
        
       MaxReference = ColRange.Resize(1, 1).Offset(lMinRow - 1, 0).Address( _
            rowabsolute:=False, columnabsolute:=False)
           

    End Function

    Wednesday, March 7, 2012 4:48 PM
    Answerer
  • I would first remove the after property in the find method.  It is not necessary and may be the cause of the problem.  If you are trying to find the first item in the range with the find method using after will never located the first item.  Second I would add a return message when the function failed like this

    Public Function MinReference(ColRange As Range) As String
        
       
    Dim MinValue As Long
       
       
    Dim CellValue As Range
        
        MinValue
    = WorksheetFunction.Min(ColRange)
        
       
    Set CellValue = ColRange.Find(what:=MinValue, LookIn:=xlValues, lookat:=xlWhole)

        if CellValue is nothing then

           CellValue = MinValue & " Not found"  
       else  
           MinReference
    = CellValue.Address(RowAbsolute:=False, ColumnAbsolute:=False)
       end if    
    End Function


    jdweng

    Wednesday, March 7, 2012 6:13 PM
  • Thanks for your help guys! I will try each solution get back to you all today sometime.

    Auburn University Student IT/MIS Intern War Eagle!

    Friday, March 9, 2012 12:29 PM