Basically I need the average of 4 cells on separate worksheets with 1 or more
cells having a DIV/O message. A little background: these 4 worksheets are the
quarterly results (averages from the monthly workbooks) then I need a formula
that averages the quarters to make a year-to-date running total. I can't get
this total without values entered in all 4 workbooks in which case we won't
have until all data for the year has been entered but we need to see the
running total now. In addition to the standard AVERAGE formula, here are the
other formulas already attempted:

=SUM('1st Quarter Jan-Mar'!G40,'2nd Quarter Apr-Jun'!G40,'3rd Quarter
Jul-Sep'!G40,'4th Quarter Oct-Dec'!G40)/COUNTA('1st Quarter Jan-Mar'!G40,'2nd
Quarter Apr-Jun'!G40,'3rd Quarter Jul-Sep'!G40,'4th Quarter Oct-Dec'!G40)

=SUM('1st Quarter Jan-Mar'!G40,'2nd Quarter Apr-Jun'!G40,'3rd Quarter
Jul-Sep'!G40,'4th Quarter Oct-Dec'!G40)/SUM(--(ISNUMBER('1st Quarter
Jan-Mar'!G40)),--(ISNUMBER('2nd Quarter Apr-Jun'!G40)),--(ISNUMBER('3rd
Quarter Jul-Sep'!G40)),--(ISNUMBER('4th Quarter Oct-Dec'!G40)))

Note: both of the above formulas only worked if all 4 quarters had their
data filled in

=IF(ISNUMBER('1st Quarter Jan-Mar'!G40),'1st Quarter
Jan-Mar'!G40,0)+IF(ISNUMBER('2nd Quarter Apr-Jun'!G40),'2nd Quarter
Apr-Jun'!G40,0)+IF(ISNUMBER('3rd Quarter Jul-Sep'!G40),'3rd Quarter
Jul-Sep'!G40,0)+IF(ISNUMBER('4th Quarter Oct-Dec'!G40),'4th Quarter
Oct-Dec'!G40,0)/SUM(--(ISNUMBER('1st Quarter Jan-Mar'!G40)),--(ISNUMBER('2nd
Quarter Apr-Jun'!G40)),--(ISNUMBER('3rd Quarter
Jul-Sep'!G40)),--(ISNUMBER('4th Quarter Oct-Dec'!G40)))

Note:that above formula works only if 1 quarter is filled, but 2 or
more quarters and it adds rather than give the average.

Does anyone have any suggestions?