copying a "moving average" formula?

**Warstar** · 11-22-2010, 02:07 AM

I have created a formula for calculating an average for amounts in a column. I wish to be able to copy that formula down a column.

The formula I have used is =AVERAGEIF(K3:K4,"<>0")

When I copy this and paste it into the other cells in the column, the first cell reference does not "lock"

Ie. the result I want is =AVERAGEIF(K3:K5,"<>0")
=AVERAGEIF(K3:K6,"<>0")
=AVERAGEIF(K3:K7,"<>0")
etc in the consectutive cells running down the page.

But The result I get is =AVERAGEIF(K4:K5,"<>0")
=AVERAGEIF(K5:K6,"<>0")
=AVERAGEIF(K6:K7,"<>0")
ETC.

Please, How do I copy such a formula??

**Paul** · 11-22-2010, 02:10 AM

Hi Warstar, welcome to the forum.

Use $'s next to the row number, for example:

=AVERAGEIF($K$3:K4,"<>0")

When you fill that down, K4 will update, but K3 will remain constant.

For more information about this subject, search for Relative and Absolute cell references.