Averaging Values using a Macro

[monkster62]

Hi,

I am new to the forum. I have a spreadsheet that I need to average the following ranges A7:A11, A17:A21, A27:A31, A37:A41...A21997:A22001. How would be the best way to go about it? My assumption is with a macro? I also want to delete A2:A6, A12:16, A22:A26.... A21992:A21996. Any help would be greatly appreciated!!

I am using Excel 2003

Thanks!!
M

[jasoncw]

I would suggest first deleting the cells, then you could simply average one contiguous range. Do you want to delete just the cells, or the entire rows listed?

[monkster62]

Thanks Jason,

Delete the entire row. The problem is that there are 22,000 rows. Way too many to manually delete. I am not a programmer so I am not sure how to go about either task.

M

[DonkeyOte]

Here this should delete the values you don't want -- then you can simply AVERAGE the remaining data in one contiguous range

Please Login or Register  to view this content.

Prior to running this ensure you are active on the appropriate sheet !
I would advise you save a copy of your file before running the above just in case...

Again per prior poster -- this is deleting just cells in A as opposed to the entire row... change Range(...) to Rows(i-4 & ":" & i) for rows.

[shg]

To average the numbers as they are, you could use this formula:

=AVERAGE(IF( MOD( ROW(A7:A22001) - 7, 10) <= 4, IF(A7:A22001 <> "", A7:A22001) ) )

This is an array formula, meaning it MUST be confirmed with Ctrl+Shift+Enter, not just Enter. (With the cursor in the formula bar, hold down Ctrl and Shift, then press Enter.) You'll know you did it correctly if curly braces appear around the formula in the Formula Bar; you cannot type in the braces directly.

To instead delete all the spurious rows, in an unused collumn starting in row 7, enter and copy down this formula:

=IF(MOD(ROW()-7, 10) <=4, "", 1)

Then select all the cells containing that formula, and do Edit > Go to ..., Special, Formula, and untick everything but Numbers. That will select all the cells that contain 1.

Then do Edit > Delete > Entire row