averaging non-contiguous data using arrays
 

All,

I have data that look like the following:

abc Top | bcd Top | Average | ST Dev | cde Top | def Top| Average|
St Dev
data | data | AV | STDEV | data | data |
AV | STDEV
data | data | AV | STDEV | data | data |
AV | STDEV
data | data | AV | STDEV | data | data |
AV | STDEV

What I'd like to do is make an overall average of abc, bcd, cde, and def
data. I am trying to make an array to first collect all the data together,
then make an array that is the average data to paste. Below is what I have
so far. I am running into trouble populating the overall array (arrTotAv)
and then also trying to get the average of that array (arrTotAvTop).

Any help would be appreciated!
Thanks!
Matt


'Average All files together
ActiveCell.Offset(0, 5).Value = "Total Average"
ActiveCell.Offset(0, 6).Value = "Total St Dev"
ActiveCell.Offset(0, 1).Select
ReDim arrTotAv(1 To 11, 1 To lngFileCount)
ReDim arrTotAvTop(1 To lngFileCount)

StartColumn = ActiveCell.Column
Count = 1

'Populate the array to average data
For j = 1 To StartColumn
If Not ActiveCell.Value = "" Then
If Mid(ActiveCell.Value, Len(ActiveCell.Value) - 2, 3) =
"Top" Then
arrTotAv(Count, 0) = ActiveCell.Range("A2:A12").Value
Count = Count + 1
Else: End If
Else: End If
ActiveCell.Offset(0, -1).Select
Next j

'Do average and st dev calculations on array
For j = 1 To 11
With Application.WorksheetFunction
arrTotAvTop(j) = .Average(.Index(arrTotAv, ?))
End With
Next j

'Paste Data into Sheet
ActiveCell.Offset(1, StartColumn + 3).Range("A1:A11").Value =
Application.Transpose(arrAvTop)

averaging non-contiguous data using arrays
 

the table I tried to create didn't come out that great. Let me try again:

abcTop|bcd Top|Average|STDEV|cde Top|def Top|Average|STDEV
data | data | AV | SD | data | data | AV | SD
data | data | AV | SD | data | data | AV | SD
data | data | AV | SD | data | data | AV | SD


"Matt S" wrote:

All,

I have data that look like the following:

abc Top | bcd Top | Average | ST Dev | cde Top | def Top| Average|
St Dev
data | data | AV | STDEV | data | data |
AV | STDEV
data | data | AV | STDEV | data | data |
AV | STDEV
data | data | AV | STDEV | data | data |
AV | STDEV

What I'd like to do is make an overall average of abc, bcd, cde, and def
data. I am trying to make an array to first collect all the data together,
then make an array that is the average data to paste. Below is what I have
so far. I am running into trouble populating the overall array (arrTotAv)
and then also trying to get the average of that array (arrTotAvTop).

Any help would be appreciated!
Thanks!
Matt


'Average All files together
ActiveCell.Offset(0, 5).Value = "Total Average"
ActiveCell.Offset(0, 6).Value = "Total St Dev"
ActiveCell.Offset(0, 1).Select
ReDim arrTotAv(1 To 11, 1 To lngFileCount)
ReDim arrTotAvTop(1 To lngFileCount)

StartColumn = ActiveCell.Column
Count = 1

'Populate the array to average data
For j = 1 To StartColumn
If Not ActiveCell.Value = "" Then
If Mid(ActiveCell.Value, Len(ActiveCell.Value) - 2, 3) =
"Top" Then
arrTotAv(Count, 0) = ActiveCell.Range("A2:A12").Value
Count = Count + 1
Else: End If
Else: End If
ActiveCell.Offset(0, -1).Select
Next j

'Do average and st dev calculations on array
For j = 1 To 11
With Application.WorksheetFunction
arrTotAvTop(j) = .Average(.Index(arrTotAv, ?))
End With
Next j

'Paste Data into Sheet
ActiveCell.Offset(1, StartColumn + 3).Range("A1:A11").Value =
Application.Transpose(arrAvTop)