Try to use the following arrary function (end with Ctrl+Shift+Enter)

=AVERAGE(IF(NOT(ISERROR(Sheet1!$A$1:$A$4)),Sheet1! $A$1:$A$4))

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Download
=================================

"Robin" wrote in message
...
I have a column of calculated values (time). In that column, there are a
few
#VALUEs due to unavailable data for that particular cell. How do I
average
the column, but not include cells with #VALUES in the average? I also need
zero included in the average. eg, my column, B1:B4 consists of:
0:02
0:04
#VALUE
0:00

I would like the average to return a value of 0:02 ((0:02 + 0:04 +
0:00)/3)

Thanx