seeking help on how to automatically shift an average range as new data is added

[the_cagey_one]

Cells b20 through m20 contain numerical values by month. These cells
get their data from an external source; using code I gleaned from here
and elsewhere on the 'net this data will be automatically populated.
That is, today the cells for August 07 through March 08 (f20 - m20)
are blank. When the properly-formatted data source documents for
August, etc. are created data will "appear."

Currently n20 contains the formula:
=IF(SUM(C20:E20)<>0,AVERAGE(C20:E20),0)
that calculates the 3-month average for the the months of May, June
and July. If there is no data no average is calculated.

I don't want the users to have to go in and edit this simple formula.

Any help would be appreciated.

kg

 

[the_cagey_one]

Cells b20 through m20 contain numerical values by month. These cells
get their data from an external source; using code I gleaned from here
and elsewhere on the 'net this data will be automatically populated.
That is, today the cells for August 07 through March 08 (f20 - m20)
are blank. When the properly-formatted data source documents for
August, etc. are created data will "appear."

Currently n20 contains the formula:
=IF(SUM(C20:E20)<>0,AVERAGE(C20:E20),0)
that calculates the 3-month average for the the months of May, June
and July. If there is no data no average is calculated.

I don't want the users to have to go in and edit this simple formula.

Any help would be appreciated.

kg

Anyone have any suggestions?

 

[MyVeryOwnSelf]

Cells b20 through m20 contain numerical values by month. These cells

get their data from an external source; using code I gleaned from here
and elsewhere on the 'net this data will be automatically populated.
That is, today the cells for August 07 through March 08 (f20 - m20)
are blank. When the properly-formatted data source documents for
August, etc. are created data will "appear."

Currently n20 contains the formula:
=IF(SUM(C20:E20)<>0,AVERAGE(C20:E20),0)
that calculates the 3-month average for the the months of May, June
and July. If there is no data no average is calculated.

I don't want the users to have to go in and edit this simple formula.

Any help would be appreciated.

There's got to be a more elegant way, but the following seems to work. It
uses row 21 as a "helper row."

In B21 put
=IF(ISNUMBER(B20),MAX(0,COLUMN()-4),-1)
and extend it to M21.

Then in N20 put
=IF(MAX(21:21)<0,"no data",AVERAGE(OFFSET(B20,0,MAX(21:21),1,3)))

Modify to suit.