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

  • Thread starter Thread starter the_cagey_one
  • Start date Start date
T

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
 
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
Click to expand...

Anyone have any suggestions?
 
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.
Click to expand...

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.
 
Back
Top