Average of every 5 cells in a column

[hit]

hello
i have numbers in the cells of column A and i want to calculate the average of every 5 cells and put it in column B
so the first 5 numbers from column A will give a number to be put in column B and so on

i found several ways through google but i did not manage it to make it work

any help would be much appreciated

thank you

[RusselB]

If your numbers in column A start on row 1, then in column B .. presuming you want the first five consecutive numbers .. you would enter

Code: Select all

=average(A1:A5)

[hit]

yes but after that how is it going to go from cell A6:A10 and then from A11:A15 and so on

[RusselB]

If the formula for my example is in B1, then you can copy that formula and paste it in B6
If you want the updated formula to be in B2, then I suggest just entering the formula with the different range.
If you want the updated formula to be in B2 but don't want to have to enter the range manually (which can get to be redundant), then you can copy & paste the formula through out column B, then delete the rows of column B that have averages for ranges you don't want.

These are the simplest ways of getting what you seem to be asking for. It is possible, using helper columns and much more complicated formulas, to have the row numbers automatically calculated.

[hit]

well i have thousands of data in column A
=SUM(OFFSET($D$2,(ROW()-ROW($D$2))*5,0,5,1)) / 5

this is something i found through google that it is supposed to calculate the average every 5 cells and then i added a division by 5 to that so it would give the average
but it did not work on openoffice although other people testing it confirm that it works on office excel


i think i need something similar to the one above

thank you

[Villeroy]

Do not copy any formulas you do not understand.

Add a helper column, say X. If row #1 has column labels and the numeric data reside in D2:D1000, put
=MOD(ROW();5) in column X
and
=SUMIF($X$1:$X$1000;2;$D$1:$D$1000) / COUNTIF($X$1:$X$1000;2)
divides the sum of all D values with row number 2 by the count of rows with row number 2.

[hit]

just tried it and it does not work the way i described
it does not show the average for every 5 cells
it only shows the same number for all
(i did not understand why you divide by 2 in your formula though)

[Villeroy]

blah.ods

average every 5th row

(12.33 KiB) Downloaded 271 times

[RusselB]

I used my formula at viewtopic.php?f=9&t=76889&hilit=+subtotal#p350567 as a basis for the formula in column D on the attached spreadsheet