Results 1 to 10 of 10

How to get the maximum, minimum and average of a range?

This is a discussion on How to get the maximum, minimum and average of a range? within the Excel Questions forums, part of the Question Forums category; I use this code to discover, but it's not running: Code: For lin = foundcell.Row To foundcell.MergeArea(foundcell.MergeArea.count).Row For col = ...

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Posts
    68

    Unhappy How to get the maximum, minimum and average of a range?

    I use this code to discover, but it's not running:
    Code:
    For lin = foundcell.Row To foundcell.MergeArea(foundcell.MergeArea.count).Row
            For col = 4 To numcol - 1
                Cells(lin, col).Interior.Color = RGB(212, 200, 200)
                If Not myrange Is Nothing Then
                    Set myrange = Union(myrange, Cells(lin, col))
                End If
            Next col
        Next lin
    
     LabelMax.Caption = WorksheetFunction.max(myrange)
     LabelMin.Caption = WorksheetFunction.min(myrange)
     LabelAverage.Caption = WorksheetFunction.Average(myrange)
    col: column
    lin: line / row
    numcol: number of columns

    So, how could I find max, min and avarage value?
    If I save cells(i,j) in a matrix(i,j), could I use something like LabelMax.Caption = worksheetFunction.max(matrix)? And, to use worksheetFunction, need I declare some library?

    Thanks.

  2. #2
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,553

    Default Re: How to get the maximum, minimum and average of a range?

    So is there a "Set myrange = " somewhere before this code?
    if not, then it is nothing and will not get into the Union statement.
    Did you intend to test the value of Cells(lin, col) maybe?

    So, how could I find max, min and avarage value?
    You're doing fine once myRange gets fixed.

    If I save cells(i,j) in a matrix(i,j), could I use something like LabelMax.Caption = worksheetFunction.max(matrix)?
    fix myRange and you should be fine how you have it.

    And, to use worksheetFunction, need I declare some library?
    Nope, it's readily available with the Excel.Application.
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  3. #3
    Board Regular
    Join Date
    Jan 2011
    Posts
    68

    Default Re: How to get the maximum, minimum and average of a range?

    Tweedle, u'r right. myrange begins with nothing. It's ok now.

    There's a last trouble:
    when I finished the search and myrange is nothing yet, ie, when there was no number wrote in the range, occurs a problem with .average (I think it's just with average).
    I tried:
    Code:
    If  WorksheetFunction.Average(interval) Then
    LabelMed.Caption = "Méd: " & WorksheetFunction.Average(interval)
    End If
    but that's not enough, the problem continues:
    Err 1004, it's not possible get the property "Average" in worksheetfunction.

  4. #4
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,553

    Default Re: How to get the maximum, minimum and average of a range?

    What is 'interval' ?
    Needs to be an array of values i.e. myRange, which it would still be a good idea to test for is not nothing....
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  5. #5
    Board Regular
    Join Date
    Jan 2011
    Posts
    68

    Default Re: How to get the maximum, minimum and average of a range?

    Sorry, I had changed the name, "interval" is the same as "myrange".
    Using "On Error GoTo...", there's no error msg - although don't solve.

    It's a little odd; when myrange is blank, an error occurs. So I use "On Error GoTo".
    Other cases:
    1. When myrange is just one merged cell, max = min = average = value, ok.
    2. When myrange is more then a row, max = maxvalue; min = minvalue, but average is nothing.

  6. #6
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,553

    Default Re: How to get the maximum, minimum and average of a range?

    Can you post your current code please?
    somewhere I'm expecting
    Code:
     
    Dim interval as range 
    < snip >
    set interval = myRange
    to make interval a range of cells being passed into the worksheet functions, yes?
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  7. #7
    Board Regular
    Join Date
    Jan 2011
    Posts
    68

    Default Re: How to get the maximum, minimum and average of a range?

    The form has 1 combobox, 2 buttons and 3 labels.

    Code:
        Option Explicit
        Dim numlin As Long
        Dim numcol As Long
    Code:
    Private Sub UserForm_Initialize()
        
        On Error GoTo Errorsee
        
        Dim maxlin As Integer 'number of rows; property
        Dim maxcol As Integer 'number of columns; data related that property
        Dim base As String 'keep the word "basis", because that's the first word in the table; and, this row has filled cells to every column
        Dim i As Integer
    
    '   CALCULATE HOW MANY ROWS AND COLUMNS THE TABLE HAS
        Dim foundcell As Range
        Dim wordsearched As String
        
         'rows:
        numlin = Range("A" & Rows.count).End(xlUp).Row
        numlin = Range("A" & numlin).MergeArea.count + numlin
        
        'find the word "basis", go to the end of table and takes the number of col
        wordsearched = "Basis"
        Set foundcell = Cells.Find(what:=wordsearched, After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not foundcell Is Nothing Then
            numcol = Cells(foundcell.Row, Columns.count).End(xlToLeft).Column
        End If
    
    '   Put items in combobox
        For i = 10 To numlin '10 first rows are titles, others have names of properties. However, there're some merged cell, so we need remove blanks.
                ComboBox1.AddItem Cells(i, 2).FormulaR1C1
        Next
        
    '   Should Remove blanks
        For i = 1 To ComboBox1.ListCount - 1
            If Len(ComboBox1.List(i)) = 0 Then
               ComboBox1.RemoveItem i
            End If
        Next
        
        Exit Sub
        
    Errorsee:
        
    End Sub
    Code:
    Private Sub CommandButton1_Click() 'button close
       userform1.Hide 'closes form
    '   Remove colors
        With Cells.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End Sub
    
    Private Sub CommandButton2_Click()
    
        On Error GoTo Errorsee
        
    '   DECLARE
        Dim busca As Range
        Dim col As Integer
        Dim lin As Integer
        Dim wordseached As String
        Dim interval As Range
    
    '   Remove colors
        With Cells.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
    '   Hide labels
        LabelMax.Caption = ""
        LabelMin.Caption = ""
        LabelMed.Caption = ""
        LabelMax.Visible = False
        LabelMin.Visible = False
        LabelMed.Visible = False
    
    '   Find the combobox1.text in the sheet.
        wordsearched = ComboBox1.value
        Set busca = Cells.Find(what:=wordsearched, After:=Range("B3"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
       
        
    '   Paint cells
        Range(busca.Address).Interior.Color = RGB(212, 200, 200)
        For lin = busca.Row To busca.MergeArea(busca.MergeArea.count).Row  'from first to last merged cell
           For col = 3 To numcol '3 first columns has titles.
             Cells(lin, col).Interior.Color = RGB(212, 200, 200)
             Next
        Next
    
        
    '   Get max, min and average inside range
        For lin = busca.Row To busca.MergeArea(busca.MergeArea.count).Row
            For col = 4 To numcol
                If Not interval Is Nothing Then
                    Set interval = Union(interval, Cells(lin, col))
                 Else
                    Set interval = Cells(lin, col)
                End If
            Next col
        Next lin
    
        LabelMax.Visible = True
        LabelMin.Visible = True
        LabelMed.Visible = True
        
        LabelMax.Caption = "Máx: " & WorksheetFunction.max(interval)
        LabelMin.Caption = "Mín: " & WorksheetFunction.min(interval)
        LabelMed.Caption = "Méd: " & WorksheetFunction.Average(interval)
        Exit Sub
        
    Errorsee:
    
    End Sub

  8. #8
    Board Regular tweedle's Avatar
    Join Date
    Aug 2010
    Location
    35.883348,-78.510216
    Posts
    1,553

    Default Re: How to get the maximum, minimum and average of a range?

    So you're running calcs against a pivot table, is that right?
    Based on that assumption, I believe how you cycle through the rows can vary on your pivot design.

    the closest I got, not knowing the design of your pivot, looks like this:
    Code:
      'Get max, min and average inside range
        lastrow = busca.MergeArea.End(xlDown).Row - 1
        For lin = busca.Row To lastrow
    Win7::MSO2007
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    I have no special talent.
    I am only passionately curious. - Albert Einstein
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    "Simple" and "Easy" are a matter of perspective
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Solutions here may be 'representative' and you may
    need to actually put some thought to your own needs.

  9. #9
    Board Regular
    Join Date
    Jan 2011
    Posts
    68

    Default Re: How to get the maximum, minimum and average of a range?

    I don't know why, but I couldn't send the file by here. This is the code:
    Actually, in a first sheet, I have a form that finds the sheet by name (in this sample, NY012010), so, this sheet is opened and this second forms is showed:

    Code:
      Option Explicit
        Dim numrows As Long
        Dim numcol As Long
    Private Sub CommandButton1_Click() 'botão fechar
        specific_prop.Hide
    '   Remove colors
        With Cells.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    End Sub
    Private Sub CommandButton2_Click()
        On Error GoTo Trataerro
        
    '   Declare
        Dim foundcell As Range
        Dim col As Integer
        Dim lin As Integer
        Dim wordsearched As String
        Dim interval As Range
    '   Remove colors
        With Cells.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        
    '   Hide labels
        LabelMax.Caption = ""
        LabelMin.Caption = ""
        LabelMed.Caption = ""
        LabelMax.Visible = False
        LabelMin.Visible = False
        LabelMed.Visible = False
    '   Find combobox.text in the sheet
        wordsearched = ComboBox1.value
        Set foundcell = Cells.Find(what:=wordsearched, After:=Range("B3"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
       
        
    '   Paint cells
        Range(foundcell.Address).Interior.Color = RGB(212, 200, 200)
        For lin = foundcell.Row To foundcell.MergeArea(foundcell.MergeArea.count).Row
           For col = 3 To numcol 'unless 2 first columns
             Cells(lin, col).Interior.Color = RGB(212, 200, 200)
             Next
        Next
        
    '   Get max, min and average
        For lin = foundcell.Row To foundcell.MergeArea(foundcell.MergeArea.count).Row
            For col = 4 To numcol
                If Not interval Is Nothing Then
                    Set interval = Union(interval, Cells(lin, col))
                 Else
                    Set interval = Cells(lin, col)
                End If
            Next col
        Next lin
        LabelMax.Visible = True
        LabelMin.Visible = True
        LabelMed.Visible = True
        
        LabelMax.Caption = "Máx: " & WorksheetFunction.max(interval)
        LabelMin.Caption = "Mín: " & WorksheetFunction.min(interval)
        LabelMed.Caption = "Average: " & WorksheetFunction.Average(interval)
        Exit Sub
        
    Trataerro:
    End Sub
    
    Private Sub UserForm_Initialize()
        
        On Error GoTo ErrorSee
        
        Dim maxlin As Integer
        Dim maxcol As Integer
        Dim price As String
        Dim i As Integer
    '   Calc how many rows and columns the table has
        Dim foundcell As Range
        Dim wordsearched As String
        
        'linhas:
        numrows = Range("A" & Rows.count).End(xlUp).Row
        numrows = Range("A" & numrows).MergeArea.count + numrows
        
        'find the word "price", go to the end of the table to know the number of columns
        wordsearched = "price"
        Set foundcell = Cells.Find(what:=wordsearched, After:=Range("A1"), LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not foundcell Is Nothing Then
            numcol = Cells(foundcell.Row, Columns.count).End(xlToLeft).Column
        End If
    '   Put items to the combobox
        For i = 10 To numrows 'descontamos as primeiras linhas, com os títulos
                ComboBox1.AddItem Cells(i, 2).FormulaR1C1
        Next
        
    '   Remove empty items
        For i = 1 To ComboBox1.ListCount - 1
            If Len(ComboBox1.List(i)) = 0 Then
               ComboBox1.RemoveItem i
            End If
        Next
        
        Exit Sub
        
    ErrorSee:
        
    End Sub

  10. #10
    Board Regular
    Join Date
    Jan 2011
    Posts
    68

    Default Re: How to get the maximum, minimum and average of a range?

    Hi again,
    I tried in another forum, and there I put the link:
    http://www.vbforums.com/showpost.php...6&postcount=12

    Thanks.

    I found a book about VBA Office2003 whose writter is "MrExcel", good.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com