Can i get a sum function to recognise X (letter) as a number?

N

nomad

I need to get a sum function to recognise an 'x' or 'NA' as a number as not
to mess up an overall percentage function later on in the worksheet.
This is for an inspection & cells would usually have a 1 - 5 value placed in
them but sometimes the field is not applicable.
Is it possible??
Can anyone help??
 
D

David Biddulph

What number do you want your x to be recognised as?

The SUM() and AVERAGE() functions will ignore a cell containing text, so
your sum and average would look at the numbers in your range, and include
that cell when you change it from X to a number.

If you want x to be treated as some specific number, tell us what number you
want it treated as.
 
B

Bob Phillips

I think you want this array formula

=AVERAGE(IF(ISNUMBER(I1:I5),I1:I5))

As an arary formula, commit with Ctrl-Shift-Enter, not just Enter.
 
N

nomad

Yes, i need it to be recognised as 5.

David Biddulph said:
What number do you want your x to be recognised as?

The SUM() and AVERAGE() functions will ignore a cell containing text, so
your sum and average would look at the numbers in your range, and include
that cell when you change it from X to a number.

If you want x to be treated as some specific number, tell us what number you
want it treated as.
Click to expand...
 
N

nomad

I dont think this is what im looking for

Bob Phillips said:
I think you want this array formula

=AVERAGE(IF(ISNUMBER(I1:I5),I1:I5))

As an arary formula, commit with Ctrl-Shift-Enter, not just Enter.

--
__________________________________
HTH

Bob
Click to expand...
 
M

MartinW

Hi Nomad,

Maybe this might work,
=SUM(A1:A30)+(COUNTIF(A1:A30,"x")*5)

You will rpobably need to add some sort of error checking
but that depends on how your sheet is set-up.

HTH
Martin
 
G

Gary''s Student

Say we have a column of mixed values:

13
13
13
13
13
happy
13
13
13
sad
13
13
13
13
13
13
13

We want to sum the column, but want ANY text cell to be treated as a 5.

=SUM(A1:A17)+5*(COUNTA(A1:A17)-COUNT(A1:A17))

will display 205
 
Top