give text a numerical value

[excel007]

On my worksheet no values can be reported as "0" instead they must be reported as " QL". But I need to be able to include the fields containing  QL when averaging.

Example:

   A

1  2

2  <QL

3  2

4

5

6   =AVERAGE(a1:a5)   and the answer is 1.333

Any ideas?

 

[Aladin Akyurek]

On my worksheet no values can be reported as "0" instead they must be reported as " QL". But I need to be able to include the fields containing  QL when averaging.

Example:

   A

1  2

2  <QL

3  2

4

5

6   =AVERAGE(a1:a5)   and the answer is 1.333

Any ideas?

Can you explain the result - that is, why 1.333?

 

[excel007]

Can you explain the result - that is, why 1.333?

2+2=4

4/3=1.333

 

[smi123]

Wonderful little function is counta

=SUM(A1:A5)/COUNTA(A1:A5)

 

[Aladin Akyurek]

Can you explain the result - that is, why 1.333?

2+2=4

4/3=1.333

=SUM(A1:A5)/MAX(1,COUNT(A1:A5)+COUNTIF(A1:A5,"?QL"))

 

[excel007]

Thanks that worked perfect :wink: smi123