Solved: Finding avg of ranges

[bluetide76]

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.
Code:
[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.

[bluetide76]

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.

[david000]

I'm not sure what your looking for but this will allow you to grab mulitipule ranges and average them.

[VBA]

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


[/VBA]

[bluetide76]

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.

[mdmackillop]

[VBA]
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)
Next
MsgBox tot / (r.Rows.Count)
End Sub

[/VBA]

[bluetide76]

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.

[bluetide76]

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)

[Aussiebear]

And your code is?