Results 1 to 8 of 8

Thread: Solved: Finding avg of ranges

  1. #1

    Solved: Finding avg of ranges

    I have a subroutine that takes any size of data and finds Xbar. Xbar is the average of all the means based upon the rows. So say I have 4 rows of data input it will take the average of each of these then take the average of those averages. It starts with rows from A2 on for my header.
    [VBA]Sub CalcXbar()

    Dim r as Range
    set r = Activesheet.Range("A1").CurrentRegion
    set r = r.offset(1,0).Resize(r.rows.count-1)
    msgbox application.Average(r)

    End Sub[/VBA]

    My question is I want to take this same format and make it find the average of the ranges for my data. So I want it to take the range for each set and then take the average of all these ranges and still be able to do this for any size of data input.
    Thanks in advance.

  2. #2
    Any advice? What about a new subroutine then? I am basically just looking for a subroutine/macro that will takes whatever number of sets and observations (rows, columns) of data and would calculate the range for each row and then calculate the average of all those ranges.

  3. #3
    VBAX Tutor david000's Avatar
    Mar 2007
    I'm not sure what your looking for but this will allow you to grab mulitipule ranges and average them.


    Sub InputBox_MultiRangeAverage()
    Dim varRange As Range, subArea, subRange As Range, AreasStr As String
    On Error Resume Next

    Set varRange = Application.InputBox("Select single or multiple ranges:", _
    "MultiRange test", Selection.Address(0, 0), Type:=8)
    On Error GoTo 0

    If varRange Is Nothing Then Exit Sub

    For Each subRange In varRange.Areas
    AreasStr = AreasStr & vbNewLine & subRange.Address(0, 0)
    Next subRange
    MsgBox "Average of: " & AreasStr & vbNewLine & "Equals = " & Application.WorksheetFunction.Average(varRange)

    End Sub


  4. #4
    Sorry it's hard to express exactly what I mean, I'm new to code in general. I am trying to make a subroutine that will find the range for each set of data that a person can enter. I terms of set I mean rows, and observations are the columns. so a set would be like 2 3 4 5, where the set is (2,3,4,5) and the observation count is 4.
    I want it to find the range of each of these rows meaning the Max value - Min value. Excel doesnt seem to have a range function. Once it takes the ranges for each of the rows I want it to then take the average of that column of ranges. In statistics this is called an Rbar which is sorta like a median or mean range to use for charting data points. The code I have above was able to give me the average of my means for any number of rows using the count. I was hoping to get or adjust the code to do this for averaging the ranges of the data rows. I've been stuck on this one part and its a vital part to the rest of my program. I'd appreciate any help you can give.

  5. #5
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    May 2004
    Sub CalcXbar()
    Dim r As Range, rw As Range
    Dim tot As Single
    Set r = ActiveSheet.Range("A1").CurrentRegion
    Set r = r.Offset(1, 0).Resize(r.Rows.Count - 1)
    For Each rw In r.Rows
    tot = tot + Application.Average(rw)
    MsgBox tot / (r.Rows.Count)
    End Sub

    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  6. #6
    Gave wrong values each time.
    Say I have these sets of data and input them in the rows.
    1 3 6
    6 2 1
    3 3 1
    I'm trying to make a subroutine that will take the range for each. So Max value - Min value. So the ranges it would yield would be 6-1=5 6-1=5 3-1=2
    so 5 5 2. Then I need it to take the average of these ranges. (5+5+2)/3= 4
    I'm trying to set it up so it goes row by row taking the range of each row then will average them all at the end. Problem is I wont know specifically how many rows of data could be input.

  7. #7
    Ah nvm I got it adjusted a tad and got it working. Thanks man. All I needed to do was adjust the tot expression to tot = tot + Application.Max(rw) - Application.Min(rw)

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Dec 2005
    And your code is?
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

Posting Permissions

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