Averaging cells in separate workbooks to make year-to-date average

[alisah]

I need the average from 4 different workbooks (which are averaged from 3
other workbooks!) 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. This is the formula I'm using =AVERAGE('1st Quarter
Jan-Mar'!G40,'2nd Quarter Apr-Jun'!G40,'3rd Quarter Jul-Sep'!G40,'4th Quarter
Oct-Dec'!G40)
it gives me #DIV/O! unless all 4 workbooks have values.

 

[Joel]

You need to use SUM dikvided by CountA (coounts cells that are not empty

=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)

 

[alisah]

It only worked if all 4 cells had data which they won't have until the end of
the year. I think the problem is that those 4 cells are dependent on data
averaged from 3 different monthly workbooks and contain a #DIV/0! until we
enter in the data from that quarter. Is there a way Excel can average cells
if one or more contain that #DIV/0! message?

 

[Joel]

This will work as long as one quarter is filled in. If you have no quarters
filled in there will still be an error

=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)))

 

[alisah]

I really appreciate your help, but it still is only giving me a result with
all 4 cells filled in. Do you have any more suggestions?

 

[Joel]

=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)))

 

[alisah]

WOW! That is one huge formula! I had to go over it several times to make
sure I entered it correctly and it works only if 1 quarter is filled, 2 or
more and it adds rather than give the average.

 

[alisah]

Do you have any other suggestions on how I could get that formula to work?