Average of selected cells, array required?

**oscar munero** · 03-12-2013, 04:18 PM

Hi,

thanks for reading, it has been a few years since I have had to do anything like this in Excel, so I'm very rusty.

My problem is this: I have been measuring crystals length and breadth, I now have over 4000 rows of data and I would like to extract the average size for each individual crystal (not overall average of the 4000+ measurement).

E.g. In column A the data in cells A2 and A3 relates to one crystal, A4 and A5 the next, A6 and A7 the next and so on. Now I know how to get the average using formulas =AVERAGE(A2,A3), =AVERAGE(A4,A5), =AVERAGE(A6,A7) but when I copy this down it doesn't automatically go to =AVERAGE(A8,A9), =AVERAGE(A10,A11) and so on, how do I make it do this? I remember using an array to select every other cell a few years back, is it something like this I need to do?

Thanks.

**FDibbins** · 03-12-2013, 04:25 PM

Hi and welcome to the forum

If your data starts on an uneven number, then use this, copied down...
=IF(MOD(ROW(),2)=0,AVERAGE(E2:E3),"")

If it starts on an even number, use this...
=IF(MOD(ROW(),2)<>0,AVERAGE(E2:E3),"")

**oscar munero** · 03-12-2013, 04:40 PM

Originally Posted by FDibbins

Hi and welcome to the forum

If your data starts on an uneven number, then use this, copied down...
=IF(MOD(ROW(),2)=0,AVERAGE(E2:E3),"")

If it starts on an even number, use this...
=IF(MOD(ROW(),2)<>0,AVERAGE(E2:E3),"")

Hi thanks for the reply and the welcome!

Sorry I think I have confused matters by referencing the old array I used, please ignore that, as the solution you have given me isn't relevant as there isn't odd or even numbers per se as my data has e.g. 6.6, 7.1, 1.03 etc.

All I want to do is not have to be able to have to manually enter in

=AVERAGE(A2,A3)
=AVERAGE(A4,A5)
=AVERAGE(A6,A7)
=AVERAGE(A8,A9)
=AVERAGE(A10,A11) etc

when I try to drag this down it doesn't automatically put in the next in the sequence I desire i.e. =AVERAGE(A12,A13)

Is there any way to do this? I don't want to have to manually enter the formula several thousand times!

Thanks again and apologies for any confusion.

**FDibbins** · 03-12-2013, 04:56 PM

when I said odd or even numbers, i meant row numbers

**oscar munero** · 03-12-2013, 05:11 PM

Originally Posted by FDibbins

when I said odd or even numbers, i meant row numbers

Ah right. I've adapted it as my data starts in A2 thus:

=IF(MOD(ROW(),2)<>0,AVERAGE(A2:A3),"")

this returns a blank cell :/

then when I copy it down the next entry is

=IF(MOD(ROW(),2)<>0,AVERAGE(A3:A4),"")

but I want it to be

=IF(MOD(ROW(),2)<>0,AVERAGE(A4:A5),"")

even when I manually enter the cells I want (which is what I want to avoid) every other cell is returning blank