Average calculation with a #Value in the selection

[JoshuaX1980]

Hi,
I want to get the average of three cells, one of these cells has no value and is assigned #VALUE (because I calculated with it in other worksheets), now I want the average value of these three cells to be the average value of the two remaining cells. So:

4
#value
6

I want the outcome of Average(A1:A3) to be 5. Now I get #value as an outcome.

I do not want the outcome to become (4+6+0)/3=3.3 ( no zero value for the #value)
I hope somebody can help me.

Cheers,
Lennart

 

[Peter_SSs]

JoshuaX1980

Perhaps something like this? Formula in A8 is:
=AVERAGE(IF(ISNUMBER(A1:A7),A1:A7))
confirmed with CTRL+SHIFT+ENTER

Mr Excel.xls
	A	B	C	D
1	4
2	#VALUE!
3	#VALUE!
4	7
5	4
6	dasdasD
7	7
8	5.5
9
Sheet1

 

[JoshuaX1980]

Ok thank you, it is working!