Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of time spent monthly getting DIV/0 error
Teethless Mama, your formula worked for most part but I am getting DIV/0
error on worksheets with no data. Please advise I am trying to get an average of time spent each month on an activity. The spreadsheet has 12 months and only 3 of the months are populated.I think it is still counting months 4-12 in my average. As each months time is entered I want it to recalculate the average. "Teethless mama" wrote: Try this elegant formula: =AVERAGE(IF(($M$88:$AT$880)*(MOD(COLUMN($M$88:$AT $88),3)=1),$M$88:$AT$88)) ctrl+shift+enter, not just enter |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of time spent monthly getting DIV/0 error
Hi,
Try this =IF(SUM(IF(($M88:$AT880)*(MOD(COLUMN($M88:$AT88), 3)=1),$M88:$AT88))=0,"",AVERAGE(IF(($M88:$AT880)* (MOD(COLUMN($M88:$AT88),3)=1),$M88:$AT88))) or in 2007: =IFERROR(AVERAGE(IF(($M88:$AT880)*(MOD(COLUMN($M8 8:$AT88),3)=1),$M88:$AT88)),"") both array entered. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Kelway07" wrote: Teethless Mama, your formula worked for most part but I am getting DIV/0 error on worksheets with no data. Please advise I am trying to get an average of time spent each month on an activity. The spreadsheet has 12 months and only 3 of the months are populated.I think it is still counting months 4-12 in my average. As each months time is entered I want it to recalculate the average. "Teethless mama" wrote: Try this elegant formula: =AVERAGE(IF(($M$88:$AT$880)*(MOD(COLUMN($M$88:$AT $88),3)=1),$M$88:$AT$88)) ctrl+shift+enter, not just enter |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Average of time spent monthly getting DIV/0 error
Shane thank you! One last snag - this is calculating minutes. I want to
present in Hours, where would I "/60" in the formula? "Shane Devenshire" wrote: Hi, Try this =IF(SUM(IF(($M88:$AT880)*(MOD(COLUMN($M88:$AT88), 3)=1),$M88:$AT88))=0,"",AVERAGE(IF(($M88:$AT880)* (MOD(COLUMN($M88:$AT88),3)=1),$M88:$AT88))) or in 2007: =IFERROR(AVERAGE(IF(($M88:$AT880)*(MOD(COLUMN($M8 8:$AT88),3)=1),$M88:$AT88)),"") both array entered. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Kelway07" wrote: Teethless Mama, your formula worked for most part but I am getting DIV/0 error on worksheets with no data. Please advise I am trying to get an average of time spent each month on an activity. The spreadsheet has 12 months and only 3 of the months are populated.I think it is still counting months 4-12 in my average. As each months time is entered I want it to recalculate the average. "Teethless mama" wrote: Try this elegant formula: =AVERAGE(IF(($M$88:$AT$880)*(MOD(COLUMN($M$88:$AT $88),3)=1),$M$88:$AT$88)) ctrl+shift+enter, not just enter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
something to help me track money and time spent on one specific j. | Excel Discussion (Misc queries) | |||
Average time spent to # of events | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
How can I show time spent? | Excel Worksheet Functions | |||
Funcation to average the Time spent | Excel Worksheet Functions |