0

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  
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.