averaging non-contiguous data using arrays

M

Matt S

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)
 
M

Matt S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

stacking data 0
Error 2042 problem with vlookup 0
averaging arrays 2
Array range error question 1
Need help-For loop 3
using arrays to complete calcs 4
Losing my array from one sub to another 5
Arrays 13

Top