Still trying to average 4 worksheets and having formula problems

[alisah]

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?

 

[T. Valko]

The resulting formula will be pretty long. I'm gonna use shortened sheet
names:

=AVERAGE(IF(COUNT(Sheet1!G40),Sheet1!G40,{""}),IF(COUNT(Sheet2!G40),Sheet2!G40,{""}),IF(COUNT(Sheet3!G40),Sheet3!G40,{""}),IF(COUNT(Sheet4!G40),Sheet4!G40,{""}))

With your actual sheet names that'll be pretty long.

An alternative is to pull each of those cells into your summary sheet:

A1: =Sheet1!G40
A2: =Sheet2!G40
A3: =Sheet3!G40
A4: =Sheet4!G40

Then just average A1:A4 -

Array entered** :

=AVERAGE(IF(ISNUMBER(A1:A4),A1:A4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Or, normally entered:

=SUMIF(A1:A4,"<1E100")/COUNT(A1:A4)

 

[alisah]

That worked! Thank you!!!!!!!

The resulting formula will be pretty long. I'm gonna use shortened sheet
names:

=AVERAGE(IF(COUNT(Sheet1!G40),Sheet1!G40,{""}),IF(COUNT(Sheet2!G40),Sheet2!G40,{""}),IF(COUNT(Sheet3!G40),Sheet3!G40,{""}),IF(COUNT(Sheet4!G40),Sheet4!G40,{""}))

With your actual sheet names that'll be pretty long.

An alternative is to pull each of those cells into your summary sheet:

A1: =Sheet1!G40
A2: =Sheet2!G40
A3: =Sheet3!G40
A4: =Sheet4!G40

Then just average A1:A4 -

Array entered** :

=AVERAGE(IF(ISNUMBER(A1:A4),A1:A4))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Or, normally entered:

=SUMIF(A1:A4,"<1E100")/COUNT(A1:A4)

 

[T. Valko]

You're welcome. Thanks for the feedback!