I have a have multiple worksheets that contain data i would like to calculate
some averages for. Down one column is BF, LN, DN, SCK, TOT repeated, and the
adjecent cell contains a value I currently have the average for Breakfast as
=(SUMIF(August!$J$2:$J$91,"BF",August!$K$2:$K$91))/COUNTIF(August!$J$2:$J$91,"BF"),
howver the count function still counts cells that have zero values, I would
like the average of only the non-zero cells. Any ideas????

=AVERAGE(IF((August!$J$2:$J$91="BF")*(August!$K$2: $K$91<0),August!$K$2:$K$9
1))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom




"REW2705" wrote in message
...
I have a have multiple worksheets that contain data i would like to
calculate
some averages for. Down one column is BF, LN, DN, SCK, TOT repeated, and
the
adjecent cell contains a value I currently have the average for Breakfast
as

=(SUMIF(August!$J$2:$J$91,"BF",August!$K$2:$K$91))/COUNTIF(August!$J$2:$J$91
,"BF"),
howver the count function still counts cells that have zero values, I
would
like the average of only the non-zero cells. Any ideas????

That still doesnt work, i played around with it and im still not getting the
average of only the non-zero matching values. It either counts the zero
cells as a one to to change the average or it doesnt add them to the sum but
it divides by too large of a number.

Never mind, it worked

"REW2705" wrote:

I have a have multiple worksheets that contain data i would like to calculate
some averages for. Down one column is BF, LN, DN, SCK, TOT repeated, and the
adjecent cell contains a value I currently have the average for Breakfast as
=(SUMIF(August!$J$2:$J$91,"BF",August!$K$2:$K$91))/COUNTIF(August!$J$2:$J$91,"BF"),
howver the count function still counts cells that have zero values, I would
like the average of only the non-zero cells. Any ideas????