# Excel macro to average data points every 60 rows

#### Mattykinsx

##### New Member
I have an excel file that has over 33,000 data points
The data is in column F

I would like the macro to take every 60 rows of the data start at row 80 and average them into column d

Thank you in advance

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Is there a reason you want a macro instead of a formula?

I understand that a macro would be easier to have it go through and do it for me across the entire database.

I know that formula =AVERAGE(F26:F80) (cells as an example) will yield me the result, but across 33,000 data points... I would be here forever.

Something like this:

=AVERAGE(INDEX(F:F,ROW(A1)*60-59):INDEX(F:F,ROW(A1)*60))

Will give you the average for F1:F60. When copied down one cell, it will give you the average for F61:F120, and so on. So basically, you enter this formula above and copy down. Just as easy as a macro. But if you don't want a formula, we can get a macro solution as well.

I appreciate this but unfortunately I would need a macro.
Also, I don't need an average of everything in between, simply from each point to each point.

I appreciate this but unfortunately I would need a macro.
Also, I don't need an average of everything in between, simply from each point to each point.

So do you want the average of F60 and F120, then F120 and F180, and so on? Or do you want the average of every 60th point (F60 and F120 and F180...)?

I appreciate your help. I was able to find a macro that worked.

Range("D199").Select
Selection.Copy
For a = 199 To Range("B1").End(xlDown).Row Step 60
If a > Range("B1").End(xlDown).Row Then Exit For
Range("D" & a).PasteSpecial Paste:=xlPasteFormulas
Next a

End Sub

So, if anyone needs it, there ya go.

Replies
1
Views
363
Replies
9
Views
224
Replies
4
Views
144
Replies
4
Views
661
Replies
2
Views
494

1,217,346
Messages
6,136,044
Members
449,981
Latest member
kjd513

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back