# give text a numerical value

#### excel007

##### New Member
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?

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

##### MrExcel MVP
excel007 said:
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

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

2+2=4

4/3=1.333

#### smi123

##### Board Regular
Wonderful little function is counta

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

##### MrExcel MVP
excel007 said:
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

##### New Member
Thanks that worked perfect :wink: smi123

Replies
4
Views
222
Replies
8
Views
502
Replies
3
Views
86
Replies
28
Views
911
Replies
2
Views
251

1,195,849
Messages
6,011,955
Members
441,657
Latest member
Diupsy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back