Automation of Averages

[bachya1208]

Hi everyone,

I have the following situation: I have 10,000 numbers all in the first
column (A) in an Excel spreadsheet. I need to computer the averages of
every 10 numbers (that is, the average of A1:A10, A11:A20, A21:A30, and
so on). I know how to do this for every 1 cell (just by dragging
down), but no idea how to do it for every 10 cells. Doing this by hand
means that I'm going to have to deal with 1,000 separate values,
manually...and that's not fun.

Any ideas? Thanks.

 

[Dave Peterson]

I put this in B1 and dragged it down:

=SUM(OFFSET($A$1,(ROW(A1)-1)*10,0,10,1))

 

[Peo Sjoblom]

Just one caution using row as opposed to rows, if a row is inserted above
the formula it will return an erroneous result, secondly you might want to
divide it by 10 if you want the average <g>
Thirdly the OP seems to have multiposted

Peo

 

[Dave Peterson]

Points #2 and #3 are well taken.

I never insert any rows <vbg>.

 

[Bob Phillips]

Better to use AVERAGE than divide by 10.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Just one caution using row as opposed to rows, if a row is inserted above
the formula it will return an erroneous result, secondly you might want to
divide it by 10 if you want the average <g>
Thirdly the OP seems to have multiposted

Peo

 

[Peo Sjoblom]

Since I already had answered the same question in another newsgroup using a
non volatile formula and average I tried to have some fun on Dave's behalf

Peo

Better to use AVERAGE than divide by 10.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Dave Peterson]

And I'm sure he appreciates it!

You big Swede, you!