How to use AVERAGE on every five cells with only once keying the command

[avrumit]

Hi I've a list numbers for example in cells I10, 11, 12, 13, 14

122
132
163
172
149

to work out the average I do function =AVERAGE(I10:I14) and I get 148 placed in K10

How do I use this function on every five cells with only once keying in this command?
Coloumn I goes down to I125 and I really dont have the time to key in the command at every five cells.

[sweep]

Hi,

Put this is K10, and copy it down as far as your data goes

=IF((ROW())/5=ROUND((ROW())/5,0),AVERAGE(I10:I14),"")

[NBVC]

Try in K10:

=IF(MOD(ROW(),5)=0,AVERAGE(I10:OFFSET(I10,4,0)),"")

copied down.

or

=IF(MOD(ROW(),5)=0,AVERAGE(OFFSET(I10,0,0,5,1)),"")

[sweep]

Yup, MOD would've been neater wouldn't it?!!

[avrumit]

Thanks I used =IF(MOD(ROW(),5)=0,AVERAGE(I10:OFFSET(I10,4,0)),"" ) it works great in K10 and gives me the average of every 5 cells in I. BUT... sometimes I need the average of every 5 cells starting from K13 and some from K12, I've tried using this formula and the others given to me on forum to no avail. So I need some help on how to use the formula on every 5 cells but not always starting from K10 how do go about it? Thanks a lot on helping me, very kind of you.

[NBVC]

You can adjust the Row() function by subtracting number of rows from 10th...

If you want to start at I12, then

=IF(MOD(ROW()-2,5)=0,AVERAGE(I12:OFFSET(I12,4,0)),"" )

for I13,

=IF(MOD(ROW()-3,5)=0,AVERAGE(I12:OFFSET(I12,4,0)),"" )

[avrumit]

Thank you very much for all your help you're Wiz No1 With Excel

[avrumit]

Hi thanks for your help you're really good at this. Would know how to copy the results i get from using this formula and paste them in a fresh page without the gaps?

[NBVC]

So if your results in the original sheet (say, Sheet1) are in K12:K29, then try this anywhere in your other sheet.:

Please Login or Register  to view this content.

copied down.

Adjust this range: Sheet1!$K$12:$K$29 to envelope full results range.

[avrumit]

Thank You Very Much You're Number One