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

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

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

On Aug 29, 4:50 pm, wrote:
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?

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

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.