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.

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.

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 -

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.

....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 -

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.

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.

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.