| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Still trying to average 4 worksheets and having formula problems
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Still trying to average 4 worksheets and having formula problems
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) -- Biff Microsoft Excel MVP "alisah" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Still trying to average 4 worksheets and having formula proble
That worked! Thank you!!!!!!!
"T. Valko" wrote: 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) -- Biff Microsoft Excel MVP "alisah" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problems with worksheets | Excel Discussion (Misc queries) | |||
| Problems with offset/average formula | Excel Worksheet Functions | |||
| =Average(if( formula using different worksheets | Excel Worksheet Functions | |||
| Problems copying a formula between worksheets | Excel Discussion (Misc queries) | |||
| AVERAGE problems | Excel Worksheet Functions | |||
Linear Mode
