average in increments that aren't integers

[jacob128]

Hi,

I need to average every 7.11111 cells in column A (from row 1 to row 192), then column b seperately and so on... (to column QB) (ie rounding to the nearest whole number of cells, so most will average the contents of 7 cells, but some will average the values of 8 cells)

I also need to average every 16.4444 cells in row 1, (which has the same range as above) but i guess if i transpose my data i can use the same formula, just changing the number by which it averages.

i am useless when it comes to excel formulae, so could someone please show me how to write a formula that averages a number of cells incrementally without the increments being whole numbers?

Thanks in advance

Jacob

 

[jacob128]

Thanks, ben. that does work, but you're right, it is rather messy, especially if i have to do that 444 times. I guess i can record a macro which will do it more quickly, right?

 

[BenMiller]

Sure, you should be able to do that. Just remember to use relative references when recording, and do it on a copy of your data because macros can't be undone. Btw make sure to use the formulas in the post after I updated it; I entered the wrong formula the first time (I was originally counting from A1, I forgot it had no data. You need to start from A2).

 

[jacob128]

I entered the wrong formula the first time (I was originally counting from A1, I forgot it had no data. You need to start from A2).

Yeah, thanks, i did notice, and unfortunately i did forget to use relative references just now, but i had saved it! thanks for all your help,

Jacob

 

[BenMiller]

No problem! Sorry no MVP's helped out on this one; I'm really just a beginner but I did what I could

 

[BenMiller]

If it helps, you could always change column C, D, and E into one formula in C =AVERAGE(INDIRECT(ADDRESS((ROUND(B1,0)+2),1)):INDIRECT(ADDRESS((ROUND(B2,0)+1),1)))

 

[jacob128]

thanks, am using that now . i'm having trouble getting the macro to work properly though:

i'm starting it recording, then copying column c and pasting to a new sheet ive created, then copying column D into column A and deleting column D so E moves into its place, then stopping recording. is this the correct procedure? if so, how do i get it to do it multiple times without having to press run like a million times?

im not sure what's going wrong, but what it's pasting is not right.

Thanks

 

[BenMiller]

The problem might be because the column number is 1 in all the formulas (in ADDRESS); you need it to be sequential.

 

[BenMiller]

Try putting a sequential number at the top of each column. In other words, in the (empty) row A, number your data. So A1=1 B1=2 C1=3, etc. Do this all before you enter any formulas.
Then record a macro, entering the same as before in column B, and this: =AVERAGE(INDIRECT(ADDRESS((ROUND(B1,0)+(A$1+1)),1)):INDIRECT(ADDRESS((ROUND(B2,0)+(A$1)),1))) in cell C2, and drag it down.

 

[BenMiller]

My mistake. Try this: =AVERAGE(INDIRECT(ADDRESS((ROUND(B1,0)+(A$1+1)),A$1)):INDIRECT(ADDRESS((ROUND(B2,0)+(A$1)),A$1)))

 

[jacob128]

I feel so stupid, but would you be able to get the macro working for me please and save it as a workbook that can deal with macros), i dont mind pressing run a million times, i just cant get it to work. i've added the file below
http://www.mediafire.com/?he46yzifi4uuhb8

i'm sorry, and thanks for your help once again