Macros/Formulas

[wrongful death]

I'm trying to create a macro that will average (5) numbers, ie..A1:A5 and have that number shown in B5. Then it will go down the next (5) numbers, ie..A6:A10 and have that number shown in B10. So on and so on and so on for thousands of rows.

Thank for any assistance.

 

[Al Chara]

Try the following:

Dim counter As Long
For i = 1 To Int(ActiveSheet.UsedRange.Columns(1).Rows.Count / 5)
counter = counter + 6
Range("a" & counter) = Application.WorksheetFunction.Average(Range("A" & counter - 5), Range("A" & counter - 1))
Next

Should work, but I didn't test it, so post any problems.

 

[Jay Petrulis]

On 2002-04-09 12:13, wrongful death wrote:
I'm trying to create a macro that will average (5) numbers, ie..A1:A5 and have that number shown in B5. Then it will go down the next (5) numbers, ie..A6:A10 and have that number shown in B10. So on and so on and so on for thousands of rows.

Thank for any assistance.

Hi,

Quick and easy way without a macro:
In B5, please the following formula

=IF(MOD(ROW(),5)<>0,"",AVERAGE(A1:A5))

and copy down the column

With a macro:
----------------------
Sub average_five()
Dim lastrow As Long, x As Long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
For x = 5 To lastrow Step 5
Cells(x, 2) = "=AVERAGE(R[-4]C[-1]:RC[-1])"
Next x

End Sub
----------------------------

HTH,
Jay

 

[wrongman]

I learn that solution now,it seems complicated to me,but I believe it could help me finished my job with excel quickly

Carte De Creditsales tracking softwarewrongful death lawyersInterest Only Mortgage​

 

[todd78]

Hey this worked well. I tried doing it and got it . You too try.