Average Multiple columns and drag formula

[TwinButte]

Hello,

I need to average some data 7 columns at a time. so if you look at the screen shot i would need to average row 14 from AI to AO the from AP to AV etc etc. then do the same for the other rows, the problem is that I cannot use Average(AI14:AO14) and drag the formula as it only shifts over 1 column. any advice?

I removed the screen shot as it came up as text. im not sure how to upload from a URL

 

[socool111]

I believe this is where OFFSET can be used...but I don't have the experience to give you a direct answer...Not sure how your data is setup, but you could copy and paste the formula every 7 columns, and hide the ones in between...or if you have a lot of data, highlight the cell with the average in it...and the following 6 cells, then drag it across...it would still end up with an average every 7 cells

...not much of a helpful answer...but it is a solution if you are desperate

 

[TwinButte]

It will work thanks, just not as fast as a solution, but easier then manually copying the formula.

Thank you

 

[bbott]

Without seeing your data, I don't know if this will work. But try this formula in a cell and drag it over.

=AVERAGE((INDIRECT(ADDRESS(ROW(AI14),COLUMN(A1)*7+28,4)&":"&ADDRESS(ROW(AI14),COLUMN(A1)*7+34,4))))

 

[TwinButte]

I'm not sure how to edit this to my need, is there a way I can easily upload the screen shot? as the data is on another sheet then where I want the output

 

[bbott]

I'm not sure how to upload a screenshot, sorry.

The formula I provided should work for your data. It averages AI14:AO14. If you drag it over 1 cell, it will average AP14:AV22. Likewise, if you drag it down one cell, it will average AI15:AO15, and so on....

Just copy the formula and paste it into any cell in your worksheet. I believe it should work as is.

 

[TwinButte]

It worked, thank you high fives all around

 

[socool111]

" =AVERAGE((INDIRECT(ADDRESS(ROW(AI14),COLUMN(A1)*7+28,4)&":"&ADDRESS(ROW(AI14),COLUMN(A1)*7+34,4)))) "

idk what kind of voodoo that is..but well done!

 

[TwinButte]

I may have spoke to soon, the data does not always start in cell AI14,15,16 etc it starts randomly and I am using this formula to normalized production by weekly average (1st week of production, second week etc) when I adjust the =AVERAGE((INDIRECT(ADDRESS(ROW(AI14),COLUMN(A1)*7+28,4)&":"&ADDRESS(ROW(AI14),COLUMN(A1)*7+34,4))))

for the next row down, which starts on AF15 it doesn't seem to average correctly and when the 3rd row starts at AR16 the first cell comes back as #DIV/0! what am I not changing correctly?

 

[socool111]

div means you divided by a 0, which means you are averaging nothing...you probably need to add a $ somewhere

so $A$1 will aways return A1, no matter where you move the formula

if you have $A1...if you move over to A3 and use the formula, you return A3..but if you go down to B3, you will return A3.

A$1: If you move to B3, you will return B1

not quite understanding how your data is setup i can't tell you the exact reference...you just gotta work with it :D