averaging cells to reduce data

[jeff]

Hi, Tom,

If you enter your formula =AVERAGE(A1:A10)in D1, then
select D110 and copy that set down (grab the lower
right corner [see a + sign] and drag down), you'll
get the skipping averages; all you need to do then
is either sort on that (D) column or use a filter
to show them all grouped together.

jeff

-----Original Message-----
Hi,
I have a lot of data that I want to reduce. I have

data recorded at 1 second intervals but want to have data
at 10 second intervals. So I want to average a1:a10 then
a11:a20 and so on. I want the result from this averaging
to be recorded in D1,D2 and so on. I Can anyone help me
out with a formula to achieve this. I have 15,000 rows so
I would like to not have to write the formula every time.

 

[Max]

Maybe try my suggestion (posted in the other branch) ?

Here it is: ..

One way

Try in D1:

=AVERAGE(INDIRECT("A"&ROW(A1)*10-9&":A"&ROW(A1)*10))

This gives the average of data in A1:A10 (first ten cells)

Copy D1 down col D as required

D2 will return the average of data in A11:A20 (next ten cells)

And so on ..

If you have 15,000 cells in col A,
copy down to D1500

One way to fill down quickly?:

Type D11500 in the namebox
(box with drop arrow just to the left of the "=")

Press Enter
(this will select the range D11500)

Put in the *formula bar*:

=AVERAGE(INDIRECT("A"&ROW(A1)*10-9&":A"&ROW(A1)*10))

Press CTRL + ENTER

The above will fill D11500 with the formula
(equivalent to copying down D1 to D1500)
--

... And if you need error trapping
(say to return "" instead of "#DIV/0!")

Put instead in D1:

=IF(ISERROR(AVERAGE(INDIRECT("A"&ROW(A1)*10-9&":A"&ROW(A1)*10))),"",AVERAGE(
INDIRECT("A"&ROW(A1)*10-9&":A"&ROW(A1)*10)))

Fill down to D1500

--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----

Hi Jeff the averageing technique is a lot swifter than cuttting and

pasting! However, I want cell D1 to be an average of A1:A10 cell D2 to be an
average of A10:A20 and so on to reduce the amount of data by 90%. Can this
be done? It's driving me mad!

Many thanks Tom

Hi, Tom,

If you enter your formula =AVERAGE(A1:A10)in D1, then
select D110 and copy that set down (grab the lower
right corner [see a + sign] and drag down), you'll
get the skipping averages; all you need to do then
is either sort on that (D) column or use a filter
to show them all grouped together.

jeff

-----Original Message-----
Hi,
I have a lot of data that I want to reduce. I have

data recorded at 1 second intervals but want to have data
at 10 second intervals. So I want to average a1:a10 then
a11:a20 and so on. I want the result from this averaging
to be recorded in D1,D2 and so on. I Can anyone help me
out with a formula to achieve this. I have 15,000 rows so
I would like to not have to write the formula every time.

Many thanks Tom