Average of time each month

[Kelway07]

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.

Example : April 120 min, May - 135 min, June 105 min
(Time is in minutes)

=IF(SUM($M$88,$P$88,$S$88,$V$88,$Y$88,$AB$88,$AE$88,$AH$88,$AK$88,$AN$88,$AQ$88,$AT$88)<=0,"",AVERAGE($M$88,$P$88,$S$88,$V$88,$Y$88,$AB$88,$AE$88,$AH$88,$AK$88,$AN$88,$AQ$88:$AT$88))

The above gives me the average of 30 min rather than 120 min. Please advise

 

[Teethless mama]

Try this elegant formula:

=AVERAGE(IF(($M$88:$AT$88>0)*(MOD(COLUMN($M$88:$AT$88),3)=1),$M$88:$AT$88))

ctrl+shift+enter, not just enter

 

[JoeU2004]

Example : April 120 min, May - 135 min, June 105 min
[....]
=IF(SUM($M$88,$P$88,$S$88,$V$88,$Y$88,$AB$88,$AE$88,$AH$88,$AK$88,$AN$88,$AQ$88,$AT$88)<=0,
"",
AVERAGE($M$88,$P$88,$S$88,$V$88,$Y$88,$AB$88,$AE$88,$AH$88,$AK$88,$AN$88,$AQ$88:$AT$88))

The above gives me the average of 30 min rather than 120 min.

If you copy-and-pasted your formula exactly as it is written in the
worksheet, the problem might be the colon (":") near the end. I suspect it
should be a comma (',"); that is, you would write $AQ$88,$AT$88 instead of
$AQ$88:$AT$88.

If that does not explain the unexpected result, I suggest that you use Tools

Formula Auditing > Evaluate Formula (in Excel 2003) to see if that shows

you what is wrong.

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.

No, not if by not "populated", you mean it appears blank. See the help page
for the AVERAGE function. It states: "If an array or reference argument
contains text, logical values, or empty cells, those values are ignored;
however, cells with the value zero are included."


PS: This is just an academic exercise because I think the array formula
suggested by "Teethless Mama" is the better way to go; and by coincidence,
it will probably avoid whatever error you have in your formula.


----- original message -----

 

[Kelway07]

Teethless Mama, your formula worked for most part but I am getting DIV/0
error on worksheets with no data. Please advise