Averaging help

[ag1290]

Hey everyone,

I have a spreadsheet that has data from A2 to I2, and around 2000 rows. I need to be able to insert a new row every 6 cells and take an average of the preceding 6 cells. If these new values could also be put into a new sheet without any spaces between them, it would be ideal. Is there any way to do this?

 

[MickG]

Try this :-
Results sheet (2)

[COLOR="Navy"]Sub[/COLOR] MG08Jun10
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
Lst = Range("A" & rows.Count).End(xlUp).row
ReDim ray(1 To Lst, 1 To 9)
[COLOR="Navy"]For[/COLOR] Rw = 2 To Lst [COLOR="Navy"]Step[/COLOR] 6
    c = c + 1
    [COLOR="Navy"]With[/COLOR] Range("A" & Rw)
        [COLOR="Navy"]For[/COLOR] Ac = 1 To 9
            ray(c, Ac) = Application.Average(.Offset(, Ac - 1).Resize(6).value)
        [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]End[/COLOR] With
 [COLOR="Navy"]Next[/COLOR] Rw
Sheets("Sheet2").Range("A1").Resize(c, 9) = ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ag1290]

Absolutely perfect.

Thanks Mick, if I needed to slightly tweak it later on, probably to just add more columns, would I need to change much?

But for now, this works perfectly. The reason I needed this was to make sure that the computer generated hourly averages were similar enough to the raw output 10 min averages. I had been using a copy-paste work around to hand calculate averages up to this point, but this is much better.

Best,
ag1290

 

[ag1290]

And if I needed to average 10 rows instead of 6, would I be able to do that?

Thanks Mick

ag1290