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

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.

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

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

On Nov 16, 4:52 pm, akoskelo
wrote:
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.

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

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



On Nov 16, 5:11 pm, JP wrote:
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

On Nov 16, 4:52 pm, akoskelo
wrote:



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.- Hide quoted text -

- Show quoted text -

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

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


"akoskelo" wrote:

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.

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

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

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


:-)


HTH,
JP

On Nov 16, 5:17 pm, JP wrote:
Forgot to mention that you should FILL DOWN the formula in columns G &
H before pasting in values.

On Nov 16, 5:11 pm, JP wrote:



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

On Nov 16, 4:52 pm, akoskelo
wrote:

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

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

"akoskelo" wrote:

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.

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

Hi,

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

=AVERAGE(IF((A1:B200000)*(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

"akoskelo" wrote:

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.

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

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 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"akoskelo" wrote:
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.