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

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.

--
Biff
Microsoft Excel MVP


"Seth.Schwarzkopf" wrote in
message ...
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

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Seth.Schwarzkopf" wrote in
message ...
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

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.


--
Biff
Microsoft Excel MVP


"Seth.Schwarzkopf" wrote in
message ...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"Seth.Schwarzkopf" wrote in
message ...
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