Average of a part of the data

[breve93]

Hi,

I have an amount of data (1000 measured values). I want to check if I need 20/30/50/... values to have a good result.
I need to take the average of the first 20, then the average of the next 20, etc. Is there an easy way to do this instead of writing everything down (AVERAGE(B1:B20), AVERAGE(B21:B40), ... )?

Thanks for the help!

[canapone]

Hi,

waiting for better solutions

Please Login or Register  to view this content.

regards

[ben_hensel]

to me the most obvious would be with INDIRECT to name the range.

B1:B1000 is the range

C1 = "B1" / the location of the start cell
C2 = "B20" / the location of the end cell

C3 = average(indirect(C1):indirect(C2))
/this will take the average from the cell location named in cell C1 to the cell location named in C2.

Or you could just put the number of the rows in the "feed" cells and bury the letter naming the column inside the reference.

C1 = 1
C2 = 20
C3 = average( indirect("B"&C1):indirect("B"&C2) )

The advantage of that is that you could use math to get the number directions of the row.

[breve93]

Allright, that shall do it. Thanks a lot for the quick response guys!

[ChemistB]

To get all the averages (1-20, 21-40, 41...)
Copy this formula down

Please Login or Register  to view this content.

[Evolta]

Works if data is in cell A1:A1000 (if it is in B you have to play with the columns, so (Column()-1) when your averages are in C and beyond)

Paste code in B1 and drag

Please Login or Register  to view this content.