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!!
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!!