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


A

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.
 
Ad

Advertisements

J

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

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?
 
J

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

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?
 
J

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

Advertisements

A

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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top