Average ignoring non-numeric character and blanks?

[bhodgman]

A
1 ^4
2 (blank)
3 2
4 ^3
6 3
? (formula for average above = should return '3')


Need a formula for the average of values column A (ignoring any blanks, and reading ^4 as '4', ^3 as '3', etc)? Should return the average value of '3' for the example above.

I'm having trouble with the syntax, given some of the values are preceded by the '^' (which I unfortunately need to keep), as well as how to ignore that blank cell.

Think I need some combination of sumproduct, substitute, and isnumber, but just can't figure it out... Thanks!!

 

[bhodgman]

Figured it out, so thought I'd post formula in case others run into same issue: array entered formula: =SUM(VALUE(SUBSTITUTE(A1:A6,"^",""))))/COUNTA(A1:A6)

 

[DRSteele]

My post didn't work. I see you've solved it. Good.

 

[FDibbins]

Will that be the only non-numeric character you will find?