Calculate average but not include zero value cell in EXCEL 1

[AndyLord]

Can anyone help?

I want to calculate an average, in Excel, which will take a range of cells but not include any cells with the value of 0.00. See below:

Length of Time
A1 0:32
A2 0:39
A3 0:39
A4 0:50
A5 0:36
A6 0:00
A7 0:00
A8 0:00
A9 0:00
A10 0:00
Average 0:39

Using the standard Excel formula for Average:
=AVERAGE(A1:A10)
I get the Average = 0.16 when it should be = 0.39
Also it has to take into account that the cells could range from A1 to A??, not necessarily 10 cells. It could be 6 or 38 up to a maximum of 40. If that makes sense....
Many thanks in advance.

 

[SkipVought]

[tt]
=SUM(Length_of_Time)/(COUNT(Length_of_Time)-COUNTIF(Length_of_Time,0))
[/tt]


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[KenWright]

Another option:-

As an array formula, entered with CTRL+ENTER

=AVERAGE(IF(A1:A10,A1:A10))

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

How does he do it?! ==> * ;-)

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[AndyLord]

Skip and KenWright
Many thanks for your help guys. I did find that Skip solution very helpfull as this coped perfectly with the possibility of have extra columns inserted etc without any problems.

Thanks again.

 

[KenWright]

That is very true, as the array formula requires you to break it and then re array it.

Andy, please note the link to the bottom left of Skip's post, as his was the post that actually did exactly what you asked wrt new columns etc. The one that starts 'Thanks Skip.....'.

(Not with mine though please, as Skip had already kindly done so)

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------