Incremental Average

[ctolson]

I have a data set from a DAQ device that I need to downsize

I need to take the average of every 50 point increment, my data is in columns, as an example

a1
a2
a3
a4
a5
...
a...


I would need the average(a1, a3, a5 ...) in one cell, then average (a2, a4, a6...) in the next any ideas?

 

[DonkeyOte]

where x = last row (change as required -- ie to final row of data)

Average of A1,A3 etc -- entered as array (CTRL + SHIFT + ENTER)

=AVERAGE(IF(MOD(ROW($A$1:$A$x),2)=1,$A$1:$A$x))

For average of A2,A4 etc...again as array

=AVERAGE(IF(MOD(ROW($A$1:$A$x),2)=0,$A$1:$A$x))

HTH

 

[ctolson]

Forgive my incompetence, now that I look at my question again it isn't real clear. The average is to be taken taken every 50 points, not every other point as it looks above, so it would be

average(a1, a51, a101...)
average(a2, a52, a102...)

I am not sure how that code would work for this case.

 

[PA HS Teacher]

Just change the 2 in Lasw10's post to 51. Check that...see my post below.

 

[ctolson]

Thanks!

 

[PA HS Teacher]

=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=0,$A$1:$A$x))
=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=1,$A$1:$A$x))
=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=2,$A$1:$A$x))
......
......
......
=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=49,$A$1:$A$x))

Then Copy these 50 rows, and paste down. (actually, doing so would be redundant...just don't bother pasting down.)

 

[DonkeyOte]

A1,51 etc...
=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=1,$A$1:$A$x))

A2,52 etc...
=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=2,$A$1:$A$x))

If you need to exlude zeroes/blanks you can add additional clauses such as:

A1,51 etc...
=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=1,IF($A$1:$A$x,$A$1:$A$x)))

A2,52 etc...
=AVERAGE(IF(MOD(ROW($A$1:$A$x),50)=2,IF($A$1:$A$x,$A$1:$A$x)))

All formulae are Arrays and thus require CTRL + SHIFT + ENTER.

HTH

 

[Ashurk77]

Hi Everyone,

In my case I have to find average of cell

Ag4

Ag4,AG13

Ag4,AG13,Ag22

and so on...
(in incremental of 9)
I tried to use above formula but it shows error message for $AG$x

Thanks in Advance