Average of time each month

K

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
 
T

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
 
J

JoeU2004

Kelway07 said:
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 -----
 
K

Kelway07

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top