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

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

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))

A better way would likely be to trap your calculated values so that they
didn't return an error - it's bad design to make errors "normal".

So, for an example, instead of

=A1 + B1

which gives a #VALUE! error if either A1 or B1 are text (including space
characters, which some users use to "blank" a cell), you could use

=IF(COUNT(A1:B1)=2,A1+B1,"")

which will return "" instead of #VALUE! if A1 or B1 is text. Since
AVERAGE() ignores text, you can then just use

=AVERAGE(A1:A5)




In article ,
Robin wrote:

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

Try something like this:

For values in A1:A10

B1: =SUMIF(A1:A10,"<#VALUE!",A1:A10)/COUNT(A1:A10)

or...perhaps....

B1: =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))
Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter].


Does that help?
Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Robin" wrote:

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

Thanx Ron, yes both of those worked and I can work with each.

"Ron Coderre" wrote:

Try something like this:

For values in A1:A10

B1: =SUMIF(A1:A10,"<#VALUE!",A1:A10)/COUNT(A1:A10)

or...perhaps....

B1: =AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))
Note: For that array formula, hold down [Ctrl] and [Shift] when you press
[Enter].


Does that help?
Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Robin" wrote:

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

Thanx for the lesson; I will read up on this concept as I certainly
appreciate wanting to avoid bad design!

"JE McGimpsey" wrote:

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(ISNUMBER(A1:A5),A1:A5))

A better way would likely be to trap your calculated values so that they
didn't return an error - it's bad design to make errors "normal".

So, for an example, instead of

=A1 + B1

which gives a #VALUE! error if either A1 or B1 are text (including space
characters, which some users use to "blank" a cell), you could use

=IF(COUNT(A1:B1)=2,A1+B1,"")

which will return "" instead of #VALUE! if A1 or B1 is text. Since
AVERAGE() ignores text, you can then just use

=AVERAGE(A1:A5)




In article ,
Robin wrote:

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