How to average every 48 rows of data for 20,000 rows?

[Guest]

Hi. I have a huge spreadsheet (~20,000 rows). I need to average every 48 rows
into one value. Is there a quick and easy way to do this without scrolling
through all 20,000 rows and copy-pasting the "average" function every 48th
row?

Thank you.

 

[JP]

Here's one way.

Let's say your data is in A1:F20000. Click in cell G1 and type this
formula

=IF(MOD(ROW(),48)=0,"YES","NO")

Copy column G and paste values. Now column G has "YES" every 48th
row. In column H put

=IF(G1="YES",AVERAGE(A1:G1),"")

Again, copy and paste values if you want the hard-coded data to stick.


HTH,
JP

 

[JP]

Forgot to mention that you should FILL DOWN the formula in columns G &
H before pasting in values.

 

[Guest]

Hi,

Try something like this array formula:

=AVERAGE(IF(MOD(A1:A20000,48)=1,A1:A20000))
adjust to suit.... the 1 represents the row of your first data cell to
average, so if your data starts on row 3 then change the 1 to a 3, also this
is an array formula so commit with Ctrl+Shift+Enter

HTH
Jean-Guy

 

[JP]

....and I just realized you can combine the formulas.

=IF(MOD(ROW(),48)=0,AVERAGE(A1:F1),"")





HTH,
JP

 

[Guest]

Hi,

Just a small adjustment to my previous formula:

=AVERAGE(IF(MOD(ROW(A1:A20000),48)=1,A1:A20000))
commit using Ctrl+Shift+Enter

Hope this helps!
Jean-Guy

 

[Guest]

Hi,

If you have more that one column to average then just adapt it this way:

=AVERAGE(IF((A1:B20000>0)*(MOD(ROW(A1:B20000),48)=1),A1:B20000))
if you need to average the "0" values as well then just remove that part of
the formula....again commit using Ctrl+Shift+Enter

HTH
Jean-Guy

 

[Guest]

Another thought ..

Assume source data to be averaged is within A1:A20000

Put in B1:
=AVERAGE(OFFSET($A$1,ROWS($1:1)*48-48,,48))
Copy B1 down as far as required to say, B417? to cover the extent of source
data.

B1 returns the average of A1:A48, ie =AVERAGE(A1:A48)
B2 returns the average of the next 48 rows, ie: =AVERAGE(A49:A96)
and so on ..