# 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