... I need some help with a formula ...

[Guest]

I have a column of data (A2:A53) which represent 2007 week ending dates,
(linked to cells of 52 worksheets).

I have a column of Data (B2:B53) which represents the hours worked for each
week, (also linked to cells of 52 worksheets). For weeks not worked yet, the
value is zero, (right now there are zeros in cells B13:B53).

I would like the average of the hours worked for the entire year, and I
would like the average of the hours worked between Cell B7 and B53.

If there were no zero's I could use the following formulas:

=(SUM(B2:B53))/(COUNT(A2:A53))
=(SUM(B7:B53))/(COUNT(A7:A53))

I think I should use a couple of if statements something like <<< IF a some
cells are =0, subtract the count from the ranges in the formulas >>>, But I
don't know how to state this in Excel terms.

Can someone give me a hand?

Thanks
Darrell

 

[Guest]

In this respect the dates are irrelevant, you simply need to average the
numbers that are greater then 0 so try

=(SUM(B2:B53))/(COUNTIF(B2:B53,">0"))

for the average of B7 to B53 simply change B2 in the above formula to B7.

Mike

 

[Guest]

Mike:

Thank you, this was nice and simple.

Darrell

 

[Guest]

excepting the need to exclude the zero values, is there any reason why the
=average() function isn'tbeing used here?