Average cells

[Seth.Schwarzkopf]

i have a sheet that collects data by the date (1/1/09,1/2/09....) and then
comes up with the differences in usage from day to day. in column "F" i have
the data i want to average for the month but i cant just do =Average(F1:F31)
because in this column i have it totaling for the week. how can i average
the month but skip the cells that hold the weekly total and not average any
cell that has a zero in it?
it kind of looks like this..
A F
1/1/09 .001
1/2/09 0
1/3/09 .0058
Total .0068
1/4/09 .012
The weekly totals fall every saturday not every 7th day of the month please
help been racking my brain for a few days thanks in advance

 

[T. Valko]

Try this array formula** :

=AVERAGE(IF((ISNUMBER(A1:A31))*(F1:F31<>0),F1:F31))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Seth.Schwarzkopf]

Thanks that worked for 1 column. i have the same thing in column "K" so i
just changed all the "F's" to "K's" and now i get div by zero. is that
because i dont have any info entered to get numbers in the rest of column "K"
or have i just entered something wrong?

 

[T. Valko]

i get div by zero. is that because i dont have any

info entered to get numbers in the rest of column "K"

Yes

To prevent the error until data is entered:

=IF(ISERROR(AVERAGE(IF((ISNUMBER(A1:A31))*(K1:K31<>0),K1:K31))),"",AVERAGE(IF((ISNUMBER(A1:A31))*(K1:K31<>0),K1:K31)))

Still array entered.