macro help

[scott]

My task is is pretty simple, but complicated to explain, so bear with me. I
am taking a column of data (column a), and taking the average of every 5 data
points (column b), starting at a1. In column c, I am taking the average of
every 5 data points in column b, starting with b5. I would like to create a
macro that will do this for me, but I am having trouble figuring out how to
do it. See below for my example.

a b c
1. 1
2. 2
3. 3
4. 4
5. 5 3
6. 6 4
7. 7 5
8. 8 6
9. 9 7
10. 10 8 5
11. 11 9 6
12. 12 10 7
13. 13 11 8
14. 14 12 9
15. 15 13 10
16. 16
17. 17
18. 18
19. 19
20. 20

can anyone help? i'll explain better if you need me to.

 

[Glenn]

My task is is pretty simple, but complicated to explain, so bear with me. I
am taking a column of data (column a), and taking the average of every 5 data
points (column b), starting at a1. In column c, I am taking the average of
every 5 data points in column b, starting with b5. I would like to create a
macro that will do this for me, but I am having trouble figuring out how to
do it. See below for my example.

a b c
1. 1
2. 2
3. 3
4. 4
5. 5 3
6. 6 4
7. 7 5
8. 8 6
9. 9 7
10. 10 8 5
11. 11 9 6
12. 12 10 7
13. 13 11 8
14. 14 12 9
15. 15 13 10
16. 16
17. 17
18. 18
19. 19
20. 20

can anyone help? i'll explain better if you need me to.

No macro necessary, as far as I can tell. I think you meant to start column C
with row 9 (the first five values in column B are in rows 5 through 9).

B5=AVERAGE(A1:A5)

C9=AVERAGE(B5:B9)

Copy each formula down as needed.

 

[scott]

Yes, I agree that that formula works. However, for simplicity I only used
a1:a20. I am doing this for every day between January 1, 2001 and July 31,
2008. So by having a macro I would be saving a tremendous amount of time.
You can see that I will be using a large number of cells; do you have any
other suggestions?

 

[Don Guillett]

This should match the example given

Sub averageemup()
Columns("b:c").ClearContents

For i = 1 To Cells(rows.Count, "a").End(xlUp).Row - 9
Cells(i + 4, "b").Value = _
Application.Average(Range(Cells(i, "a"), Cells(i + 4, "a")))
Next i

For i = 5 To Cells(rows.Count, "b").End(xlUp).Row - 5
Cells(i + 5, "c").Value = _
Application.Average(Range(Cells(i, "b"), Cells(i + 4, "b")))
Next i

End Sub

 

[Spiky]

Yes, I agree that that formula works. However, for simplicity I only used
a1:a20. I am doing this for every day between January 1, 2001 and July 31,
2008. So by having a macro I would be saving a tremendous amount of time.
You can see that I will be using a large number of cells; do you have any
other suggestions?

Well, since it is the same formula every time, you just have to copy
it to all the other cells. This can be done for thousands and
thousands of cells if necessary in just a few seconds. Esp if you know
how to move and select ranges with the keyboard. CTRL-SHIFT-UP and so
forth. Makes everything go faster, really.

If it is exactly like the above, starting in B5 (don't use it above
row 5), use this formula instead:
=IF(A1="","",AVERAGE(A1:A5))

That can be copy/pasted to an easy-to-select rectangle block of cells
and be done in an instant. And it will give your staggered look,
regardless of how many columns you need.

No doubt a macro can also do something like this fast, but you also
have to write a new one each time you have a slightly different task.
I find good use of the keyboard (skip the mouse, it slows you down) to
be very fast.

 

[Glenn]

scott wrote:
Yes, I agree that that formula works. However, for simplicity I only used
a1:a20. I am doing this for every day between January 1, 2001 and July 31,
2008. So by having a macro I would be saving a tremendous amount of time.
You can see that I will be using a large number of cells; do you have any
other suggestions?

Can't see how you would save any significant time.

Look at "Fill data within a row or column" in the help file.