Google Sheets Average – How to Autofill the ‘Average’ Function in Google Sheets

google sheets

I have a column of data. Each cell in the column represents a new day. So the 1st cell is for the 1st day and the Nth cell is for the Nth day. I want to add a 2nd column where the Nth cell in that column displays the average of the first N cells in the 1st column. I want to enter this formula once and then drag it down to the bottom of the column so that the entire column is 'filled' with the proper formula.

Currently, when I specify the average function like AVERAGE(A1:A5), dragging that formula increases both the starting and the ending cell indices in the new filled formulas. For example, two cells down from the cell above I might have AVERAGE(A3:A7) when what I want is AVERAGE(A1:A7).

Example: 

Col1  __ Col2  
  7 __ __  7  
  8 __ __  7.5  
  7 __ __  7.333  
  9 __ __  7.75

Best Answer

Use the following notation:

=AVERAGE(A$1:A5)

Copying this formula, will yield:

=AVERAGE(A$1:A7)

Absolute references explained:

  • $A$1; Both the column and row reference are fixed.
  • $A1; Only the column reference is fixed.
  • A$1; Only the row reference is fixed.

Reference: Absolute reference

Related Topic