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

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

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