Calculate only numbers in a cell that has text as well

[bizpioneer]

Hi,

Is there away to calculate the average values from cell E to K and put it in column L
the trick is that the values contain text as well so I need to calculate the average using the numbers only

Also is there some kind of formula that does this and also works on Google documents?

 

[mole999]

are you prepared to fake it ?

 

[Scott Huish]

I can't speak for Google docs, but in Excel perhaps:

=SUMPRODUCT(--LEFT("0"&E2:J2,FIND(" ",E2:J2&" ")))/COUNTA(E2:J2)

 

[bizpioneer]

I can't speak for Google docs, but in Excel perhaps:

=SUMPRODUCT(--LEFT("0"&E2:J2,FIND(" ",E2:J2&" ")))/COUNTA(E2:J2)

It's not working for me I am getting this "#VALUE!" did I miss anything?

 

[mole999]

=SUMPRODUCT(--LEFT("0"&E2:K2,FIND(" ",E2:K2&" ")))/COUNTA(E2:K2)

quick test suggests that if the number is touching the letter you get a fail

you did say E to K

also if text is first you will have the error