| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Averaging cells in separate workbooks to make year-to-date average
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging cells in separate workbooks to make year-to-date average
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" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging cells in separate workbooks to make year-to-date ave
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" wrote: 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" wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging cells in separate workbooks to make year-to-date ave
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" wrote: 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" wrote: 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" wrote: 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging cells in separate workbooks to make year-to-date ave
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging cells in separate workbooks to make year-to-date ave
=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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging cells in separate workbooks to make year-to-date ave
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. "Joel" wrote: =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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Averaging cells in separate workbooks to make year-to-date ave
Do you have any other suggestions on how I could get that formula to work?
"Joel" wrote: =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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Average If - averaging cells based on value of another cell | Excel Worksheet Functions | |||
| averaging separate cells in excel excluding 0 values | Excel Discussion (Misc queries) | |||
| Elapsed time when separate cells contain time and separate date | New Users to Excel | |||
| linking 12 monthly workbooks to one year to date workbook | Excel Worksheet Functions | |||
| Due Date with year using IF for several cells | Excel Worksheet Functions | |||
Linear Mode
