Avg numbers but not #VALUE
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
|