Excel macro to average data points every 60 rows

Mattykinsx

New Member
Joined
May 23, 2015
Messages
5
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Is there a reason you want a macro instead of a formula?
 

Mattykinsx

New Member
Joined
May 23, 2015
Messages
5
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.
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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.
 

Mattykinsx

New Member
Joined
May 23, 2015
Messages
5

ADVERTISEMENT

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.
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
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...)?
 

Mattykinsx

New Member
Joined
May 23, 2015
Messages
5
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. :p
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,145
Members
417,010
Latest member
jnuss03

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?

Disable AdBlock

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
Top