How can I calculate averages of blocks of 200 cells? so the first output row shows average of first 200 cells, next output row from cell numbers 201-400, the next 401-600 and so on.
= Average(A1:A200)
= Average(A201:A400)
= Average(A401:A600)
etc.
Sample file attached.
In B2, = AVERAGE(INDEX(A:A, 200*ROWS($A$1:A1) - 198):INDEX(A:A, 200*ROWS($A$1:A1) + 1))
Entia non sunt multiplicanda sine necessitate
shg used Row() as did I,
I had to build my formula to make sure it was correct. See the attached for my slower answer.
=AVERAGE(OFFSET(INDEX(A:A,(ROW(A1)*200)-198),,,200)) only cos your example was rubbish! and i couldn't understand the results
e.g.should be= AVERAGE(A202,401)
= AVERAGE(A202:A401)
Last edited by martindwilson; 08-30-2010 at 06:14 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks