Average every 5 rows

=AVERAGE(OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,))

• AVERAGE calculates an average
• Its argument is a range or a set of numbers
• So if we make AVERAGE include the first batch of five cells (C2:C6), then when dragged down it will include the second batch (C7:C11), and so on, producing the desired results

• OFFSET returns a reference to a cell or range located a given number of rows and columns from a starting reference
• The syntax of OFFSET is:
  • OFFSET(reference, rows, cols, [height], [width])
  • Reference Required. This is the starting reference used to calculate the offset. Reference must refer to a cell or a range of adjacent cells; otherwise OFFSET returns a #VALUE! error.
  • Rows Required. This is the number of rows to offset the reference's top-left cell up or down. Using 5 as the rows argument specifies that the top-left cell of the reference is five rows below the reference. Rows can be positive (down from the starting reference) or negative (up from it).
  • Cols Required. This is the number of columns to offset the result's top-left cell left or right. Using 5 as the cols argument indicates the top-left cell of the reference is five columns to the right of the reference. Cols can be positive (right of the starting reference) or negative (left of it).
  • [Height] Optional. This is the number of rows to return in the reference. Height must be a positive number.

  • [Width] Optional. This is the number of columns to return in the reference. Width must be a positive number.

• Can be interpreted as:
  • Reference Starting point
  • Rows Move down y rows
  • Cols Required. Move down y rows
  • [Height] The range that starts n rows down from the point reached by moving y rows down and x columns right from the starting point

  • [Width] The range that starts m columns to the right from the point reached by moving y rows down and x columns right from the starting point

• OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,) means:
  • Use C2 as the starting point
  • Move down (ROW()-ROW($F$2))*5 rows
    • ROW() refers to the row number of the cell containing the formula; since the formula is in F2, ROW() = 2.
    • ROW($F$2) is the row number of F2, i.e., row 2.
    • So (2-2)*5 = 0.
  • The third argument is omitted (treated as 0), so it stays in the same column.
  • Now the new starting point is the position of C2 moved down 0 rows and right 0 columns, i.e., the original F2.
  • The 4th and 5th arguments are 5 and omitted (0), so starting at F2 it returns the next 5 rows (F2 counted as the first) and 0 columns to the right — i.e., A2:A6 — then AVERAGE computes the mean.
• Now look at F3's formula: =AVERAGE(OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,))

• • OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,)
  • Use C2 as the starting point
  • Move down (ROW()-ROW($F$2))*5 rows, i.e., (3-2)*5
  • Move right 0 columns
  • C7 is the new starting point
  • Take 4 rows down (including C7 itself)
  • C7:C11 is the range for the average calculation.

=AVERAGE(OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,))

• C2 is the first cell of the data.
• F2 is the location of the formula.
• 5 is the 'n' in 'every n rows'.