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.
A three month rolling average is what I seek.
I self-populate the cells with a complex if statement, but I'm not
sure if I've got it set right.
The data represents machine fault data. Zero values are not recorded
in the source document. If not present for the month in question, no
data = 0 time associated with the given fault. If no data is
available for the month I would like to fill that month with a 0. If
the source data workbook is not present (as it is for upcoming months)
I would like to just leave the cell blank. Here's the code:
=IF(ISERROR(VLOOKUP($A20,'[source_file.xls]sheet_reference'!$A$16:$E
$44,5,FALSE)),"",(VLOOKUP($A20,'[source_file.xls]sheet_reference'!$A
$16:$E$44,5,FALSE)))
Note that the value_if_true is set to "blank."
I don't want the users to have to go in and edit the average formula.
Any help would be appreciated.
kg
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.
A three month rolling average is what I seek.
I self-populate the cells with a complex if statement, but I'm not
sure if I've got it set right.
The data represents machine fault data. Zero values are not recorded
in the source document. If not present for the month in question, no
data = 0 time associated with the given fault. If no data is
available for the month I would like to fill that month with a 0. If
the source data workbook is not present (as it is for upcoming months)
I would like to just leave the cell blank. Here's the code:
=IF(ISERROR(VLOOKUP($A20,'[source_file.xls]sheet_reference'!$A$16:$E
$44,5,FALSE)),"",(VLOOKUP($A20,'[source_file.xls]sheet_reference'!$A
$16:$E$44,5,FALSE)))
Note that the value_if_true is set to "blank."
I don't want the users to have to go in and edit the average formula.
Any help would be appreciated.
kg